Resolved Unknown Collation Utf8Mb4 0900 Ai Ci Tecadmin

Let’s see the problem and solution to the issue faced recently:

Problem

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 message: 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 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: with: Save your file and restore the database. The Linux system users can use the sed command to replace text in files directly. 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.

title: " Resolved Unknown Collation Utf8Mb4 0900 Ai Ci Tecadmin" ShowToc: true date: “2022-12-28” author: “Theron Masterson”

Let’s see the problem and solution to the issue faced recently:

Problem

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 message: 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 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: with: Save your file and restore the database. The Linux system users can use the sed command to replace text in files directly. 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.