A collation is a set of rules that defines how to compare and sort character strings in a database server. In this tutorial, we are discussing an error faced during database restoration on another server.
Let’s see the problem and solution to the issue faced recently:
During the migration of a web application, I got the below error while restoring a database on another server. The collation id may differ based on the MySQL version.
Error 1273 (HY000) at line 25 Unknown collation: 'utf8mb4_0900_ai_ci'
See the error screenshot during database restoration.
Here you go with a solution.
After a little investigation, I found that the MySQL server running on the destination is an older version than the source. So we got that the destination server doesn’t contain the required database collation.
Then we do a little tweak in the backup file to resolve this. Edit the database backup file in text editor and replace “utf8mb4_0900_ai_ci” with “utf8mb4_general_ci” and “CHARSET=utf8mb4” with “CHARSET=utf8“.
Replace the below string:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
Save your file and restore the database.
The Linux system users can use the sed command to replace text in files directly.
sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' backup.sql
sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql
That it. after the above changes, the database was successfully restored!
Hope this is solution helped you to resolve “Unknown collation: ‘utf8mb4_0900_ai_ci’” issue.
Thanks mate. It solved my issue. I tried solutions from StackOverflow but none of them helped. Only this one helped.
I installed `brew reinstall gnu-sed` and used `gsed` on mac with the same commands. Worked.
its really very helpful thank you
This helped me greatly, thank you very much!
It worked. Thank you.
yes it really help me alot
Thanx! It helped me..
This post should be removed, this has a high risk of causing data loss. Moving from utf8 to utf8mb4 doesn’t cause data loss, but moving from utf8mb4 to utf8 removes a byte of data, which is VERY dangerous. Please take this down.
Thanks a lot. It worked for me
thanks, it is work for me
And you will loose everything requiring the fourth byte. Emojis for example. But who cares? Ugly hacks made by people that don’t what they are talking about obviously make the world go round. Because using a current version of mySQL would be too much of burden, I guess…
This is great! It works for me!
Thank you this was helpful. Just a minor correction on the intro paragraph utf8mb4_general_ci should be utf8_general_ci
In my case I also had to add this command:
sed -i ‘s/utf8mb4/utf8/g’ backup.sql
On Mac OSX this is correct:
sed -e ‘s/utf8mb4_0900_ai_ci/utf8_general_ci/g’ oldFile.sql > newFile.sql
Thanks for this, I had to leave the CHARSET=utf8mb4 to get my import to work
Otherwise you would be loosing data, like Emojis.
You’re a life-saver!