Let’s explore more about mysql unknown collation ‘utf8mb4_0900_ai_ci’. As part of our MySQL Support service, Bobcares provides answers to each and every MySQL query that reach us.

MySQL unknown collation ‘utf8mb4_0900_ai_ci’

While trying to import the database backup from the server to the localhost, do you see the error, “1273 – Unknown collation: ‘utf8mb4_0900_ai_ci'”. Then this article is for you. Let’s look closely into the error and see the method to fix it.

A collation is a collection of rules that specify how character string comparison and sorting should be done in a database server. For the user data, SQL Server collations include sorting criteria, case and accent sensitivity characteristics. The code page and related characters that can be represented for character data types like char and varchar are determined by the collations that are used with those data types.

The error is mainly caused due to the version difference and supports the inability of the database engine to render data encoding type.

How to fix MySQL unknown collation ‘utf8mb4_0900_ai_ci’ error?

As the error occurs due to the version difference, we have to edit the database backup file in text editor and replace “utf8mb4_0900_ai_ci” with “utf8mb4_general_ci” and “CHARSET=utf8mb4” with “CHARSET=utf8“. Also, we have to change the below code:

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The new code should be:

ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Now we have to save the file and restore the database. Once the above changes are made, the error is fixed now.

For the Linux users, 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

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our Support team went over the details to fix the MySQL error, unknown collation ‘utf8mb4_0900_ai_ci’.