Unknown Collation Error Migrating Newer MySQL DB to Older MySQL DB
Today I will explain how I was able to fix the Unknown collation error when importing a MySQL database.
I was moving a completed WordPress site this weekend from my hosting account to a client’s hosting account and was surprised to receive an error upon importing the database into the new empty database.
The error was:
Unknown collation: ‘utf8mb4_unicode_520_ci’
Upon Googling it, I discovered that this is a typical error when importing a database from a newer version of MySQL to an older version of MySQL.
The solution was pretty simple. Export the database in a format that is compatible to both, which turned out to be MYSQL40. I also chose that format when importing the database. Worked fine and I was back on track again.
Update: 2/25/2019
I just did another export/import to move a development site to ‘live’.
This time I got this error: ERROR 1115 (42000): Unknown character set: ‘utf8mb4’
One of the solutions I found while googling was to edit the exported SQL file with a text editor and replace all ‘utf8mb4’ with ‘utf8’.
So I tried that, and then got the same error as before: Unknown Collation: ‘Utf8_unicode_520_ci’
A different solution that the one I used originally was to edit the exported SQL file and replace ‘utf8_unicode_520_ci‘ with ‘utf8_unicode_ci‘.
So I imported the modified SQL file and it worked!
No Comments