If you use MySQL or MariaDB, you might have encountered the following error:
Unknown collation: 'utf8mb4_0900_ai_ci'
Why This Happens
This error usually occurs when you try to import a database from a newer MySQL/MariaDB version into an older version. For example:
- MySQL 8.0 → MariaDB 10.3
🛠 Step-by-Step Solution
1. Upload the SQL File
First, upload your database .sql file via cPanel → File Manager.
2. Open Terminal
Go to Terminal in cPanel.
3. Navigate to the Folder
Change directory to the folder where the .sql file was uploaded:
cd path/to/your/folder
Replace path/to/your/folder with the actual folder path.
4. Run the Replacement Command
Execute the following command to fix the collation:
sed -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' -i myfilename.sql
Replace
myfilename.sqlwith your actual SQL file name.
This command replaces all instances of utf8mb4_0900_ai_ci with utf8mb4_unicode_ci in the SQL file.
5. Download and Import
Download the modified SQL file from File Manager and import it again via phpMyAdmin.
Result
Your database will now import successfully without the “Unknown collation: ‘utf8mb4_0900_ai_ci’” error.
