[Solved] Unknown collation: utf8mb4_0900_ai_ci

A collation means a predefined rules to compare and sort character strings in a database server. Generally, this error occurs during the database restoration on another server. In this guide, we will show you how to fix Unknown collation: utf8mb4_0900_ai_ci error.

Let us discuss the cause and solution about the issue:

Problem – Unknown collation: utf8mb4_0900_ai_ci#

When you migration any web application to another server, you might get the following error at the time of database restore. The collation id may difference based on MySQL version.

Error message:

Error 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

Solution#

This type of error occurs when a MySQL destination server has older version than the source server. We found the solution and the the destination server doesn’t required database collation.

Edit the database backup file using your favorite 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 and close the file.

In Linux systems, you can use the 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

All done. Now your database should restore successfully.

Conclusion#

I hope this guide helped you to fix Unknown collation: utf8mb4_0900_ai_ci issue.