From time to time when migrating a WordPress website, you may come across collation issues when migrating the database. Specifically, a common error message when importing via PHPMyAdmin is the #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ error.
When WordPress updated to version 4.2 they introduced support for “utf8mb4” character encoding to enhance security, but with that comes a potential issue – only MySQL 5.5.3 and greater support it. This was remedied by a simple install check. If your hosting supported utfmb4, support for it was added.
However, this can cause problems if the hosting you develop the website on supports utfmb4 and the live site you migrate it to doesn’t. That’s the reason you will see the #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ error when importing via PHPMyAdmin.
Fortunately there’s an easy fix.
Export the database again from the hosting that has utfmb4 support via PHPMyAdmin as follows:
1) Click the ‘Export’ tab for the database at the top
2) Click the ‘Custom’ radio button
3) Scroll to the ‘Format-specific options’ section and change the dropdown for ‘Database system or older MySQL server to maximize output compatibility with:’ from NONE to MYSQL40, as per the image below.
4) Now scroll to the bottom and click ‘GO’.
If all went well, you should now be able to import this database to the new hosting without any issues.