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 about an error faced during database restore on other server.
Let’s see the problem and solution about the issue faces recently:
Problem
During the migration of an web application, I got below error while restoring a database on another server. The collation id may difference based on MySQL version.
Error message:
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.
Solution
After a little investigation, I found that the MySQL server running on destination is older version than source. So we got that the destination server doesn’t contains required database collation.
Then we do a little tweak in 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 below string:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
with:
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
Save your file and restore database.
The Linux system users can use sed command to replace text in file 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 above changes, database successfully restored!
Hope this is solution helped you to resolve “Unknown collation: ‘utf8mb4_0900_ai_ci’” issue.