WordPress Database Collation Troubles

If you ever come across this kind of error message in your WordPress debug.log (assuming that you have debugging enabled) …

WordPress database error Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) ...

… then you have a query that involves tables which have clashing collations. In my case, one table used utf8mb4_unicode_520_ci for a row while a joined table used utf8mb4_unicode_ci for the row on which the join relied.

There is an easy solution as long as you can convert between both collations. In this approach, we are going to convert everything to a single character set and collation. Another approach would be to adjust the queries involved according to the different collations, but I just want to get rid of the differences once and for all without having to worry about any further queries.

First, create a backup of your database. In this example, our database is wordpress.

$ mysqldump --force --single-transation --quick --databases wordpress -u spongebob --password=squarepants --result-file=wordpress-database-backup.sql

Now that we have our safety belt on, we can go into $ mysql with peace of mind, look around and convert as needed.

To check what character set and collation is used in all table columns:

mysql> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'wordpress';

The character set we will use is utf8mb4 and the collation utf8mb4_unicode_ci.

The following query will produce the ALTER TABLE statements needed to convert every single table in the database.

mysql> SELECT CONCAT( 'ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;' ) AS TMP FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'wordpress';

The result will include quite a few tables, so I’m not going to show you all but an excerpt:

+--------------------------------------------------------...---+
| TMP                                                          |
+--------------------------------------------------------...---+
...
| ALTER TABLE `wp_postmeta` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
| ALTER TABLE `wp_posts` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
...

From the resulting statements, you can pick only those for the tables necessary, or put the whole thing in a script and source it.

After the conversion is done, we should have gotten rid of those nasty collation errors.