Select Page

Unknown collation: utf8mb4_0900_ai_ci

by | Nov 24, 2023 | Uncategorized

Experienced “Unknown collation: utf8mb4_0900_ai_ci” when i was trying to import a database on a new server. Didn’t realize that the dump was created on a server with MySQL8 and i was trying to import it in MariaDB-10.5.

FYI – The “ai” in the collation name stands for “accent insensitive” and the “ci” stands for “case insensitive.

One of the solutions i stumbled upon, recommended i replace “utf8mb4_0900_ai_ci” with “utf8mb4_general_ci” and “CHARSET=utf8mb4” with “CHARSET=utf8“ and then re-run the import, but this didn’t work for me. After some more searching,

Upon some more searching, i found out this very useful answer:

In MariaDB use utf8mb4_unicode_520_ci instead, or you may even use uca1400_as_ci if you have a newer version of MariaDB.

Don’t use “_general_ci”, as it does not correctly sort or compare according to any version of Unicode and have been superseded decades ago.

When MySQL introduced utf8mb4_0900_ai_ci based on comparison and sorting rules in Unicode 9.0, MariaDB chose not to follow at the time. The new rules did not change much as Unicode’s sorting and comparison rules have been pretty stable for several generations of Unicode now. Choosing the “unicode_520_ci” rules will in almost all situations have the same result and this collation can be used in both MySQL and MariaDB.

MariaDB is currently introducing collation rules from Unicode 14.0 – called simply uca1400_as_ci (you don’t need the “utf8mb4_” part anymore). You can upgrade to these if you have a new version of MariaDB.

Make sure you use “utf8mb4” as the character encoding (and not “utf8mb3” or “utf8”, which represent the older flawed/deprecated implementation of UTF8).

So i ended up doing just one sed replace:

sed -i ‘s/utf8mb4_0900_ai_ci/utf8mb4_unicode_520_ci/g’ backup.sql 

and it all worked just fine.

Enjoy!

Pin It on Pinterest