If you are having trouble with the error “Error 1273 (Hy000) At Line 25 Unknown Collation: ‘Utf8mb4_0900_Ai_Ci'”, keep reading our article. We will give you some methods to handle the problem.
Reason for “Error 1273 (Hy000) At Line 25 Unknown Collation: ‘Utf8mb4_0900_Ai_Ci'”
This popular error occurs when you move your databases to a new server and import them. The root of the problem is that the version of your MySQL or MariaDB is different from each other on the two servers.
Look at the following example that causes the error when we import a database to phpMyAdmin.
Let’s move on. We will discover a few methods to solve the problem.
Solution to the problems
Change the content of the coding table
Because the error happens with the CHARSET and COLLATE of the database, we will make some changes to them to eliminate the error.
First, we will open the SQL file in any text editor, even with a notepad. Then, press Ctrl and H to find all “utf8mb4_0900_ai_ci” and replace them with “utf8mb4_general_ci “. After that, save the file and import it again to phpMyAdmin.
This is the SQL file after making the change:
CREATE TABLE IF NOT EXISTS Student (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
age INT(11) NOT NULL,
grade VARCHAR(25) NOT NULL,
subject VARCHAR(25) NOT NULL,
PRIMARY KEY(id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 COLLATE = utf8_general_ci;We will import it again, and the result:
Great! We are successful.
In case you work with the server by command line and do not take a look at the SQL file, run the two below commands to change the CHARSET and COLLATE:
For example our SQL file named Student.sql:
sed -i 's/utf8mb4_0900_ai_ci/utf8_general_ci/g' Student.sqlsed -i 's/CHARSET=utf8mb4/CHARSET=utf8/g' Student.sql Change the version of MySQL/MariaDB
The root of the problem is the difference in version between the two servers. We can change the version of MySQL or MariaDB in the new server to the same as the one in the old server to fix the error. However, this change can cause a severe problem – losing all data. So, we do not recommend changing the version of MySQL or MariaDB in the server, and we also do not mention it anymore.
Note: If you still change the version of MySQL or MariaDB on the server, do not forget to back up your data.
Summary
In summary, the error “Error 1273 (Hy000) At Line 25 Unknown Collation: ‘Utf8mb4_0900_Ai_Ci’” happens when you import your databases to a new server with a different version of MySQL or MariaDB. To solve this error, you can find and replace all the CHARSET and COLLATE. Let’s try these methods.
Maybe you are interested:
- MySQL Command Not Found
- Fix MySQL ERROR 1452 a foreign key constraint fails
- How To MySQL Split String By Delimiter
My name is Robert Collier. I graduated in IT at HUST university. My interest is learning programming languages; my strengths are Python, C, C++, and Machine Learning/Deep Learning/NLP. I will share all the knowledge I have through my articles. Hope you like them.
Name of the university: HUST
Major: IT
Programming Languages: Python, C, C++, Machine Learning/Deep Learning/NLP
