How to Fix “Unknown Collation: utf8mb4_0900_ai_ci” Error When Restoring a MySQL Database in cPanel

If you’re trying to restore a MySQL database backup in cPanel and encounter the error:

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

You’re not alone. This is a common issue when moving a database from a newer MySQL version (like 8.0+) to a server running an older version of MySQL or MariaDB (e.g., 5.7 or 10.3).

But don’t worry — this can be fixed easily with a few quick steps.

🧠 What Does This Error Mean?

The collation utf8mb4_0900_ai_ci was introduced in MySQL 8.0, and it’s not recognized by older MySQL or MariaDB versions, which is what most shared hosting and cPanel environments still use.

When the SQL dump file references this collation, the target server doesn’t know what to do with it — and throws this error.

✅ How to Fix the Error (Step-by-Step)

🔹 Step 1: Locate the Backup File

You likely have a .sql file you’re trying to import. If it’s zipped, unzip it first.

🔹 Step 2: Replace the Unsupported Collation

Use a text editor (or command line) to find and replace utf8mb4_0900_ai_ci with a supported collation like utf8mb4_unicode_ci.

Option A: Use Command Line (Recommended for large files)

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

Option B: Use a Text Editor

• Open the .sql file in a code editor (e.g., Notepad++, VS Code)
• Find: utf8mb4_0900_ai_ci
• Replace with: utf8mb4_unicode_ci
• Save the file

🔹 Step 3: Re-Import the Fixed SQL File

Now that the unsupported collation is removed, you can import the updated .sql file via:

✅ phpMyAdmin (cPanel)

1. Login to cPanel
2. Go to phpMyAdmin
3. Select the target database
4. Click Import
5. Upload the updated .sql file

✅ Terminal/SSH (Advanced)

mysql -u cpaneluser -p database_name < your_backup.sql

💡 Optional: Modify Future Backups to Avoid This Error

When creating backups from a MySQL 8.0+ server, use this mysqldump command to avoid version-specific collations:

mysqldump --default-character-set=utf8mb4 --set-gtid-purged=OFF --column-statistics=0 --skip-tz-utc --no-tablespaces -u root -p your_db > your_db.sql

This forces the dump to use older-compatible character sets and avoids collation conflicts.

🛑 Don’t Want to Downgrade? Upgrade Instead (Advanced Users)

If you have WHM or full root access, you could consider upgrading your server to MySQL 8.0+ — but beware:

• Not all cPanel environments support it smoothly yet
• Backup your databases and config before upgrading

For shared hosting users, the file-edit method is the safest route.

🧩 Summary

⚡ Need Help with MySQL or cPanel Migration?

Want your databases restored without errors or downtime?

👉 Open a support ticket with Servers9
Our team can help you with:

• Database import/export
• Character set conversions
• WordPress migrations
• cPanel to cPanel transfers