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)
- Open your SQL dump file (dump.sql) in any text editor.
- Find all instances of utf8mb4_0900_ai_ci.
- Replace them with utf8mb4_unicode_ci.
- 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.