Share your love
Fix Unknown collation utf8mb4_0900_ai_ci in MySQL
This tutorial intends to show you how to Fix Unknown collation utf8mb4_0900_ai_ci Error in MySQL. Let’s see why you get such an error and the meaning of that. Then, follow the rest of the article to resolve this issue.
what you read in this post?
Unknown collation Error in MySQL
When this Unknown collation error occurred, this may be because of your MySQL version. If your MySQL version is older than MySQL 8.0.1, then, your MySQL server doesn’t support this collation.
What is utf8mb4_0900_ai_ci in MySQL?
This is a character set collation for MySQL databases. It is based on the Unicode Collation Algorithm (UCA) 9.0.0 and introduced in MySQL version 8.0.1.
The ai parameter means accent insensitive, and ci is for the case insensitive. These parameters are used without considering differences in case or accents.
Now follow the steps below to fix the Unknown collation error in MySQL.
How To Fix Unknown collation utf8mb4_0900_ai_ci Error in MySQL?
As we said at the beginning of the article, this error happens if your MySQL version is older than 8.0.1.
To fix this error, follow the steps below.
First, you must create a backup file from your MySQL server.
Then, edit your backup file with your desired text editor like vi editor.
In the file, search for the following line:
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
And, you must replace the Bold line with the following:
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
When you are done, save and close the file.
Changing the utf8mb4 to the utf8 with fix this Unknown collation error. The utf8 is the older version and works for the MySQL versions older than 8.0.1.
After that, you must replace the text in files directly. To do this, run the following commands:
# sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' backup.sql
# sed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' backup.sql That’s it, you are done.
Note: In this way, you can change and fix all the unknown collations in old MySQL versions.
Conclusion
At this point, you have learned to Fix Unknown collation utf8mb4_0900_ai_ci Error in MySQL. Usually, this error will happen in the Older versions of MySQL 8.0.1. You can use utf8 instead of utf8mb4 to fix your problem.
Hope you enjoy it. You may be interested in these articles on the Orcacore website:
