Unknown collation: ‘utf8mb4_0900_ai_ci’

We’ve been running MySQL on our servers for years, this was a first for us.

We ran into this issue recently where migrating a database from one server to another generated the following error on the target box:

ERROR 1273 (HY000) at line 6245: Unknown collation: 'utf8mb4_0900_ai_ci'

At some point, a table was created with the incorrect collation.

To fix this on Linux is fairly straight forward. You need to replace and mention of the erroneous collation with one that is compatible with your data and which is supported by your server.

Please make sure to make a backup of the database you are going to try this on since there is a chance the database could be corrupted.

Launch a terminal window, navigate to where the dump file of the database you were trying to import resides and issue the following command:

sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' DB-Backup.sql
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' DB-Backup.sql

That’s it, try importing the database again and it should work.