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!