(Resolved) Unknown collation: utf8mb4_0900_ai_ci

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.