Fix: Unknown Collation 'utf8mb4_0900_ai_ci' in MySQL

If you encounter the error Unknown collation: 'utf8mb4_0900_ai_ci' when importing a MySQL dump, it usually happens because the dump was created in MySQL 8+ and you're importing it into an older version of MySQL or MariaDB. Older versions do not support this collation.

What is Collation?

Collation is a set of rules for sorting and comparing text in a database. For example, utf8mb4_0900_ai_ci is a Unicode collation introduced in MySQL 8.0. It uses the Unicode 9.0 standard and supports 4-byte UTF-8 characters. Older versions (MySQL 5.7 or MariaDB) only support older collations like utf8mb4_unicode_ci.

Quick Fix (Find & Replace)

1. Open your SQL dump file (dump.sql) in any text editor.
2. Find all instances of utf8mb4_0900_ai_ci.
3. Replace them with utf8mb4_unicode_ci.
4. Save the file and re-import into your database.

Command Line Example

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

# Windows PowerShell
(Get-Content dump.sql) -replace 'utf8mb4_0900_ai_ci','utf8mb4_unicode_ci' | Set-Content dump.sql

Better Approach (Export Compatible Dump)

If you still have access to the source database, export it in a way that is compatible with older MySQL/MariaDB servers:

mysqldump --default-character-set=utf8mb4 --set-gtid-purged=OFF your_database > dump.sql

This prevents unsupported features like new collations and GTID settings from being included in the dump.

Bonus: GTID_PURGED Error

Sometimes you may also get:

ERROR 1193 (HY000): Unknown system variable 'GTID_PURGED'

This is because your target database does not support GTID or it is not enabled. The fix is to remove that line from your dump or export with --set-gtid-purged=OFF.

Summary

• Error happens due to MySQL version mismatch.
• Quick fix: Replace collation in dump file.
• Better fix: Export in a compatible way.
• Watch out for GTID_PURGED errors.

Following these steps should make your database import smooth and error-free.