Import dabase bị lỗi 1273 unknown collation utf8_0900_ai_ci

This as part of the general sed solution here seems to also address the import hanging / freezing / getting stuck.

My database dump is very small so I only had expected it to take a few seconds to import. Before your fix it seems to take a long time, in fact I cancelled it with CTRL-C / CTRL-Z after a while.

Now, with your sed solution - it imports OK - that utf error no longer seen and the import completes after a short while.

The collation utf8mb4_0900_ai_ci is a character set collation for MySQL databases, introduced in MySQL 8.0.1. It is based on the Unicode Collation Algorithm (UCA) 9.0.0, and the character set is utf8mb4, which supports a wide range of Unicode characters.

The “ai” in the collation name stands for “accent insensitive” and the “ci” stands for “case insensitive.” This means that comparisons between characters are done without considering differences in case or accents.

If you are receiving an “Unknown collation” error, it may be because your MySQL server version is older than 8.0.1 and doesn’t support this collation. To fix this issue, you can:

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:

Error 1273 (HY000) at line 25 Unknown collation: ‘utf8mb4_0900_ai_ci’

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: ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
• With: ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Here we are changing the CHARSET to utf8, that is the older version and have limitation, Read the implications at the end of this article before making the changes in database.

Save your file and restore the database.

The Linux system users can 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

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.

Limitations of UTF8 Character Set:

Changing the character set from utf8mb4 to utf8 in MySQL is not inherently bad, but it may have some implications that you should consider before making the change:

Nguyên nhân gây ra lỗi Unknown collation: ‘utf8mb4_unicode_ci’ khi import cơ sở dữ liệu là do không tương thích giữa các phiên bản MySQL, trên các phiên bản MySQL cũ chưa có hỗ trợ utf8mb4_unicode_ci. Phiên bản mới nhất của WordPress hoạt động tốt nhất trên hosting có hỗ trợ PHP phiên bản 5.4 trở lên, và MySQL phiên bản 5.5 trở lên.

Did you just stumble upon the Unknown Collation: utf8mb4_0900_ai_ci error in MySQL? Well, that’s because of the character set mismatch between the databases. Read this blog post till the end to find the solution.

Recently, I was manually importing a database to a new server using MySQL. Things were going well… until my screen flashed – Unknown collation: ‘utf8mb4_0900_ai_ci’ ⚠

I dug up further to find the actual cause and found that the collation of the new database was different from the old one. In this post, I am going to share the solution I used to fix this issue. But let’s first understand what ‘Collation’ mean in MySQL.

What is Collation?

“A collation is a set of rules that defines how to compare and sort character strings. Each collation in MySQL belongs to a single character set. Every character set has at least one collation, and most have two or more collations.” according to MySQL.

SQL servers use collations that define the character sets, along with their sorting rules and case & accent sensitivity properties. Each part of the collation, separated by an underscore _, defines certain properties.

Take the collation — ‘utf8mb4_0900_ai_ci’ for example. Here the first part (utf8mb4) refers to a 4-Byte UTF-8 Unicode Encoding character set. The number 0900 refers to the Unicode Collation Algorithm version. And the last parts define the accent & case insensitivities. Learn SQL query for case-insensitive data.

Enough of the theory part..! Now, let’s come to a solution to the Unknown collation: ‘utf8mb4_0900_ai_ci’ error in MySQL.

The solution to Unknown Collation utf8mb4_0900_ai_ci Error in MySQL

Simply follow these steps to solve the Unknown Collation utf8mb4_0900_ai_ci Error in MySQL:

• Step 1: Open the dump.sql file in any editor.
• Step 2: Find ‘utf8mb4_0900_ai_ci’ & replace it with ‘utf8mb4_unicode_ci’.
• Step 3: Save the file.

Bingo! The error will be solved.

I hope the above-provided solution will be helpful to you in solving the ‘unknown Collation utf8mb4_0900_ai_ci error in MySQL’

Why is my UTF8 MB4 collation not working?

is happening because you are trying to use a utf8 MB4 collation for a utf8 MB3 charset (MySQL's UTF8 is not actually the full UTF8 charset, it's actually UTF8MB3 which is only a subset). You are using MySQL 5.7 which is an old version and does not support a modern collation such as utf8mb4_0900_ai_ci.

Can utf8mb4 collation be run on a MySQL server?

Go the section titled "Format-specific options" and change the dropdown for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40. Then you are on. Seems like your host does not provide a MySQL-version which is capable to run tables with utf8mb4 collation.

Does 520 collation support UTF

The 520 collation uses a newer version of the Unicode Collation Algorithm (UCA - version 5.2.0) with different weights, but to say it "has much better UTF-8 support" seems more than a stretch. Let's suppose that a Standards body (Unicode) will usually make each newer version (5.20) "better" than an older version (4.0). This is the best answer.

How to set DB collation to utf8_unicode_CI in phpMyAdmin CPAN?

If you have already exported a .sql file, the best thing to do is to Find and Replace the following if you have them in your file: It will replace utf8mb4_unicode_ci to utf8_unicode_ci. Now you go to your phpMyAdmin cPanel and set the DB collation to utf8_unicode_ci through Operations > Collation.