Fix: Unknown collation 'utf8mb4_0900_ai_ci' when importing a dump

You export a database from MySQL 8, import it into MariaDB — or into an older MySQL on a shared host — and the import stops right at the first table:

ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'

Nothing is broken. utf8mb4_0900_ai_ci is the default collation of MySQL 8.0, built on Unicode 9.0.0, and the target server simply does not know the name. The character set utf8mb4 is the same on both sides; only the collation is missing. So the fix is a text replacement in the dump file.

First, see what is in the dump

grep -o 'utf8mb4_0900_[a-z_]*' dump.sql | sort | uniq -c

Usually this is utf8mb4_0900_ai_ci. Case-sensitive variants such as utf8mb4_0900_as_cs show up in the same way and need the same treatment.

The one-line fix

The command differs between macOS and Linux, because they ship different versions of sed. On macOS, -i requires an argument — an empty string means “edit in place without a backup”.

macOS

sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql

Linux (GNU sed)

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql

If the dump contains several 0900 collations, replace them in one pass:

# Linux
sed -i -E 's/utf8mb4_0900_[a-z_]+/utf8mb4_unicode_ci/g' dump.sql
# macOS
sed -i '' -E 's/utf8mb4_0900_[a-z_]+/utf8mb4_unicode_ci/g' dump.sql

Keep a copy of the original file before editing in place. sed 's/…/…/g' dump.sql > dump-fixed.sql does the same without touching the source.

The dump then imports normally:

mysql -u user -p target_db < dump.sql

Without a temporary file

For large dumps, rewrite and import in one pipe. Nothing extra is written to disk:

sed 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql | mysql -u user -p target_db
zcat dump.sql.gz | sed 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' | mysql -u user -p target_db

Check the target server first

utf8mb4_unicode_ci exists in MariaDB and in MySQL 5.7, but it is worth a look at what else is available:

SHOW COLLATION LIKE 'utf8mb4%';

Newer MariaDB versions list utf8mb4_uca1400_ai_ci, built on a recent Unicode version. If your server has it, it is the closer match to the MySQL 8 original. utf8mb4_general_ci also works and is faster, but it sorts naively — for German, Scandinavian or Eastern European text that is the wrong choice.

One thing to watch after the migration

The collation does not only affect ORDER BY. It also decides which strings count as equal — which matters for UNIQUE indexes and for joins between text columns. Make sure the database, the tables and the application all use the same collation after the move, and check the connection charset too. Mixing them is what produces the next bug, weeks later, when a name with an umlaut suddenly cannot be saved twice.

Frequently asked questions

Where does the error come from?

utf8mb4_0900_ai_ci is the default collation of MySQL 8.0 and is based on Unicode 9.0.0. MariaDB and MySQL 5.7 do not implement it, so every CREATE TABLE in the dump that names it fails with error 1273.

Is utf8mb4_unicode_ci an exact replacement?

Not exactly. It uses an older Unicode version, so sort order and comparison differ in corner cases — accented letters, the German sharp s, emoji. For most applications this is invisible, but if you rely on sorting or on unique indexes over text, test it.

What about a gzipped dump?

You do not have to unpack it. Pipe it: zcat dump.sql.gz | sed "s/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g" | mysql -u user -p target_db.

Which collations does my server actually have?

SHOW COLLATION LIKE "utf8mb4%"; lists them. Newer MariaDB versions also offer utf8mb4_uca1400_ai_ci, which is closer to the MySQL 8 behaviour than utf8mb4_unicode_ci.