WordPress database error on multiple sites involving Burst

  • Resolved CarstenTimm

    (@carstentimm)


    2 months, 2 weeks ago

    Please help ??

    On three of my sites that have Burst Statistics installed I get the following error message:

    WordPress database error Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,IMPLICIT) for operation ‘=’ for query UPDATE wp_burst_statistics AS t
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? JOIN (
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? SELECT p.platform, p.ID, COALESCE(m.ID, 0) as platform_id
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? FROM wp_burst_statistics p
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? LEFT JOIN wp_burst_platforms m ON p.platform =?m.name
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? WHERE p.platform_id = 999999
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? LIMIT 100000
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ) AS s ON t.ID = s.ID
    ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? SET t.platform_id = s.platform_id; made by include(‘phar:///usr/bin/wp-cli/php/boot-phar.php’), include(‘phar:///usr/bin/wp-cli/vendor/wp-cli/wp-cli/php/wp-cli.php’), WP_CLI\bootstrap, WP_CLI\Bootstrap\LaunchRunner->process, WP_CLI\Runner->start, WP_CLI\Runner->run_command_and_exit, WP_CLI\Runner->run_command, WP_CLI\Dispatcher\Subcommand->invoke, call_user_func, WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher\{closure}, call_user_func, Cron_Event_Command->run, Cron_Event_Command::run_event, do_action_ref_array(‘burst_upgrade_iteration’), WP_Hook->do_action, WP_Hook->apply_filters, burst_db_upgrade->upgrade, burst_db_upgrade->upgrade_lookup_tables

    I really don’t know if the above is a Burst Statistics problem, or something else, but it would be really helpful if I could be pointed in some direction on how to solve it.

    Thank you ??

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Author Hessel de Jong

    (@hesseldejong)

    Hi @carstentimm,

    This is quite technical but in short there is a mismatch in database collations. You can fix this by installing this plugin: https://www.ads-software.com/plugins/database-collation-fix/

    Make sure you have a full database backup in case anything goes wrong.

    Then go to yourdomain.com/wp-admin/tools.php?page=ds-db-collation and click “Fix Database Collation”

    Then refresh the Burst Dashboard a few times.

    I hope this helps!

    Kind regards,
    Hessel

    Thread Starter CarstenTimm

    (@carstentimm)

    Hi Hessel

    Sorry for taking my time to reply, I testet your suggestion on a staging site, and it worked, I have one follow up question for you, hope that you have time to answer ??

    In the “report” generated by Database Collation Fix, after it had run, I could see that several plugins including Bürst had stored data using ‘utf8mb4_unicode_520_ci’, I’am guessing that is not something that you program the plugin to do.

    Would it be an idea to update wp-config.php changing the following setting to:
    define(‘DB_COLLATE’, ‘utf8mb4_unicode_ci’);

    Would that stop the problem from occurring, or is it just something I need to clean up from time to time?

    Can you think of any potential drawback from defining DB_COLLATE in wp-config?

    Thank You ??

    Plugin Author Hessel de Jong

    (@hesseldejong)

    Hi @carstentimm,

    Sorry for the late reply. I was on a holiday and as this topic was set to resolved my colleagues. Did not see this.

    I think if you run the plugin once the issue should be fixed forever, unless you move to a new database or your hosting provider changes the default collation. So I’m unsure if your suggestion will work.

    Kind regards,
    Hessel

Viewing 3 replies - 1 through 3 (of 3 total)
  • You must be logged in to reply to this topic.