If this helps, or does not help, please leave a comment below to let everyone know if this is helping or not.
Did you run into database errors when using BackupBuddy or All-in-One WP Migration or Duplicator or any other method of migrating or cloning your website between different hosting servers. This article might shed some light on the problem and and solution.
With the release of WordPress version 4.6 came a change which may cause database errors when migrating or cloning a WordPress install from one hosting service to another. Specifically if you are migrating your WordPress website:
- from a hosting server running a MySQL database version 5.6 or above,
- to a hosting server running a MySQL database version below 5.6.
It simply won’t work without taking extra steps beyond a standard migration.
HOWEVER – migrating a site from a WordPress install using a pre 5.6 version of MySQL to a WordPress install with a 5.6 MySQL install or above works fine because newer versions of MySQL can handle older versions.
The reason is described by Dave Jesch in this article where he writes,
“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.”
I ran into this problem when using BackupBuddy to migrate a WordPress install from A2 Hosting to HostGator. My A2 Hosting server was running MySQL version is 5.6.23 and Hostgator was running MySQL version is 5.51.
Sample Error Messages I got with BackupBuddy:
Error #9010: Unable to import SQL query. Error: `Unknown collation: ‘utf8mb4_unicode_520_ci’` Errno: `1273`. Click to view error details in the Knowledge Base
Error #9010: Unable to import SQL query. Error: `Table ‘nbhost_a2astra.as_commentmeta’ doesn’t exist` Errno: `1146`. Click to view error details in the Knowledge Base
Error #9010: Unable to import SQL query. Error: `Table ‘nbhost_a2astra.as_commentmeta’ doesn’t exist` Errno: `1146`. Click to view error details in the Knowledge Base
Error #9010: Unable to import SQL query. Error: `Unknown collation: ‘utf8mb4_unicode_520_ci’` Errno: `1273`. Click to view error details in the Knowledge Base
What version of MySQL are you using?
You can use PHPMyAdmin which is a tool often provided in hosting environments like cPanel hosting control panels. In the right column under Database Server is Server Version.
How to fix it?
Dave who wrote the article above created this plugin which when activated will change your database so that it will be compatible with the older version of MySQL. However, note of caution, make a backup first because if the plugin goes awry, it would be a mess.
And here is another option, which is to do a search and replace in your database, however the original article by Dave above indicates that doing this might be more complicated than it sounds: https://stackoverflow.com/questions/42385099/1273-unknown-collation-utf8mb4-unicode-520-ci/44877331
List of Common Shared Hosting Companies and what MySQL database versions they have (Please leave comments from your host so I can update this)
Before MySQL version 5.6 (you can move a site from this group to the next)
- Hostgator Shared / Reseller Hosting (5.51 as of May 18, 2018 )
MySQL version 5.6 or more
- A2 Hosting Shared hosting ( 5.6.23 as of May 18, 2018 )
If this helped you, please let me know below. Or if you have more to contribute, share your support below.
cheers,
David
