When updating projects I also check the log files. In a project I maintained, I recently found the following error in the log files I have never seen before:

WordPress database error Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_unicode_520_ci,COERCIBLE) for operation 'like' ...

As WordPress is not very strict with collations, your own WordPress website might also use different collations on different tables. In this project, there were six collations!

Fixing the collations

I order to eliminate this error, we have to convert all tables to use compatible collactions. It’s usually best to use only one collation for all of them. With WordPress 4.2.0 the collation utf8mb4 became the new standard. All database tables created after this update will be created with one of these collation variants. So for our single new collation, we use one of them.

Backup your database

STOP! Before we do anything with our database, we should create a backup. Changing the collation should not be an issue, but in any case it’s always best to create a backup. Use your favorite tool to create the backup. I usually use the WP-CLI for that:

wp db export

Now we can safely do the changes and roll back if things go wrong.

Changing the collation of one table

When changing anything for a database or table, we usually use the ALTER statement which we can also use here as followed:

ALTER DATABASE wp_project_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

This first statement will set the collation of the whole database, so when you create a new table using a database management tool, this collation will be used. But it will not change it for any of the tables. To do that, we have to alter the tables as well like this:

ALTER TABLE wp_project_name.wp_posts CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

This one statement would alter the wp_posts. Repeat the same for all other tables. Alternatively you can use the options provided by your database tool to change the collations.

Updating databases with lots of tables

The project I got this error was a multisite installation with 12 sites and 344 tables! That’s not fun updating all of these manually. Fortunately I found a nice little trick in a which will generate all the ALTER TABLE statements for all your tables:

SELECT CONCAT("ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;") 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA="wp_project_name";

Just set your database name in the last line and run it. Then copy all the “generated” queries form the result and run them. Your might run into errors like this:

Invalid default value for 'comment_date'

Then just set the sql_mode temporarily by adding this statement before all the ALTER TABLE statements:

SET sql_mode = '';

Once all statements have finished successfully all tables should now have the same collation. In this example I’ve used utf8mb4_unicode_520_ci but on your server it might be a different variant.

Updating collations using a plugin

While writing this blog post I stumbled upon the Database Collation Fix plugin which can also do this task for you, in case you cannot use a database management tool or you don’t feel like running SQL statements. The only “downside” is, that the plugin will only convert tables to utf8mb4_unicode_ci (or three alternative collations) and not to the newer utf8mb4_unicode_520_ci collation. But this should generally not be an issue.

Conclusion

Even after working with WordPress for so many years there are still some errors I have never seen. In this case it was not an error specific for WordPress, but the way WordPress is handling collations can lead to this issue. Especially if you work with WordPress installations that have been around for a while (probably installed before version 4.2.0). So if you find the same error in your log files, now you should have an idea on how to solve it. And if you haven’t looked into the log file for a while, this blog post might be a reminder to check them.