MySQL 8 Error Unknown collation: utf8mb4_0900_ai_ci – Safe Migration Guide

This error usually appears when you export a database from MySQL 8 and try to import it into:

• MySQL 5.7 or older
• MariaDB (10.x, hosting panels, cPanel)
• WAMP/XAMPP local environments
• Shared hosting providers

You’ll see something like: ERROR 1273 (HY000): Unknown collation: ‘utf8mb4_0900_ai_ci’ The reason is simple: MySQL 8 introduced new collations, and older servers don’t recognize them.

Why This Happens

MySQL 8 uses newer utf8mb4 collations such as:

• utf8mb4_0900_ai_ci
• utf8mb4_0900_as_ci
• utf8mb4_0900_as_cs

Older environments only understand older equivalents like:

• utf8mb4_unicode_ci
• utf8mb4_general_ci

So when you export a dump from MySQL 8, the dump file includes these new collations and your destination server throws an error.

Quick Safe Fix (Dump File Replace)

If this is a one-time import, open your .sql dump file with a good text editor and replace:

• utf8mb4_0900_ai_ci → utf8mb4_unicode_ci
• utf8mb4_0900_as_ci → utf8mb4_unicode_ci

You can also convert to utf8mb4_general_ci, but unicode_ci is more consistent.

Example search/replace:

utf8mb4_0900_ai_ci → utf8mb4_unicode_ci
utf8mb4_0900_as_ci → utf8mb4_unicode_ci

Save the file and import again.
This works in 99% of cases.

Better Fix: Export Database With Compatible Collation

Instead of editing dumps every time, export from MySQL 8 using older compatible collations.

phpMyAdmin Export

Select your database → Export → Custom options:

• Change Collation to utf8mb4_unicode_ci
• Uncheck “Add CREATE DATABASE” if you don’t want global settings

Export again.

CLI Export

mysqldump --default-character-set=utf8mb4 --skip-set-charset db_name > backup.sql

Then use a global find/replace only if needed.

Best Fix: Convert Database Collation Before Export

If you control the MySQL 8 database, convert tables and columns to a compatible collation before exporting.

Change database default

ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Change all tables automatically

SELECT CONCAT(
    'ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
)
FROM information_schema.tables
WHERE table_schema = 'db_name';

Copy the generated ALTER statements and run them. This ensures your export is future-proof.

Important Note for MariaDB Users

MariaDB != MySQL 8.
MariaDB 10.3–10.6 still does not support the new MySQL 8 collations.

MariaDB only supports:

• utf8mb4_unicode_ci
• utf8mb4_general_ci
• utf8_unicode_ci

If your production hosting uses MariaDB, always stick to these.

Convert Tables With phpMyAdmin

If you prefer GUI:

1. Select database
2. Select each table
3. Operations tab
4. Change collation → utf8mb4_unicode_ci
5. Check “Convert table” if available

Repeat for all tables.

Fix Laravel / Application Level

If your migrations specify a new collation, downgrade them:

Schema::defaultStringLength(191);

or set:

'mysql' => [
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
]

Avoid specifying utf8mb4_0900_ai_ci unless you know the server supports it.

Quick Checklist

• Dump from MySQL 8 → import into MySQL 5.7 / MariaDB = ❌
• Replace new collations → utf8mb4_unicode_ci = ✔
• Set database / tables to compatible collation before export = ✔
• Match application-level collation with server = ✔

Once you standardize your database to utf8mb4_unicode_ci, migrations, imports, and hosting transfers go smooth with no collation errors.