SwapSQL

Migration errors

Fix: Unknown collation 'utf8mb4_0900_ai_ci'

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

When it happens

Importing a dump created by MySQL 8.x into MariaDB or into MySQL 5.7.

Why it happens

utf8mb4_0900_ai_ci is the default collation introduced in MySQL 8.0. Older MySQL versions and most MariaDB versions don't know it, so every CREATE TABLE carrying it fails.

How to fix it

1. Quick fix — rewrite the collation in the dump

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' dump.sql
sed -i 's/CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci/CHARSET=utf8mb4/g' dump.sql

2. Better — re-export with a portable collation

mysqldump --single-transaction \
  --default-character-set=utf8mb4 mydb > dump.sql

and create target tables with utf8mb4_unicode_ci.

3. Migrating to PostgreSQL instead?

PostgreSQL is UTF-8 native and this whole class of problems disappears — our converter handles the charset mapping automatically.

Migrating between MySQL and PostgreSQL?

Our converter handles this and dozens of other gotchas automatically — upload a dump, download working SQL.

Try the converter free

More: all migration guides & error fixes