We’ve had a lot of questions lately about problems in migrating web sites between servers. A few of our customers are seeing a message about a database collation method of “utf8mb4_unicode_520_ci” not being supported. The message looks like this:
**ERROR** database error write Unknown collation: ‘utf8mb4_unicode_520_ci’
#1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’
The first question is: Why are we seeing this all of a sudden?
That’s pretty straightfoward. The answer is that with the release of WordPress version 4.6 a while back, there was a small change to the database library. The library now checks to see what version of SQL your site is running on. If it uses MySQL version 5.6 or more, it assumes the use of a new and improved Unicode Collation Algorithm (UCA) called “utf8mb4_unicode_520_ci”.
This is great, unless you end up moving your WordPress site from a newer 5.6 version of MySQL to an older, pre 5.6 version of MySQL. When you do this, the SQL export specifies the use of the ‘utf8mb4_unicode_520_ci’ Algorithm and this is unknown to the older versions of MySQL.
So what can you do about it?
Well, one thing you can try to do is to edit your SQL export file and do a search and replace, changing all instances of ‘utf8mb4_unicode_520_ci’ to ‘utf8mb4_unicode_ci’. But if you have any blog posts that are discussing this UCA, then a blind search and replace won’t work. Okay, that’s a slim chance but you get the point. And who wants to have to hand edit their SQL export file every time they migrate a site?
What’s needed is a more programmatic approach, and our Labs Department have been working on just that. We’ve developed a plugin that will modify all of your database tables and change the Collation to the older, more cross compatible ‘utf8mb4_unicode_ci’. It does this on a daily basis, so if you install a new plugin that creates tables, and those tables are created with the new UCA, it will update those when it runs is process each day.
And even better news for DesktopServer users: The plugin is created to work with DesktopServer and be notified of when you create, import, move or export a site within DesktopServer. When these events occur, it will perform the updates again to ensure that the Collation Algorithm used won’t be causing any problems.
But the plugin isn’t dependent on DesktopServer. It will run just fine on any WordPress install on any host. So if you’re using a different migration tool like Updraft Plus, or Backup Buddy, it still changes your Collation Algorithm, allowing these other tools greater reliability when migrating sites between MySQL versions.
With this plugin, it’s a simple install and forget it proposition. It will run in the background and keep things working so you can concentrate on what’s important. Like what’s for lunch.
The “Database Collation Fix” Plugin is currently only available with DesktopServer Premium Version. If you are already a Member, simply login to your “My Account” and upgrade to DesktopServer v.3.8.3 at no additional cost. If you are currently using the Limited version, consider signing up for DesktopServer Premium where you can easily Import, Export and Move a Site in seconds!