Database restore fails with with ERROR 1273: Unknown collation: 'utf8mb4_xxxx_ai_ci'
Overview
When restoring a MySQL database backup, you may encounter the following error:
This error typically occurs when the backup is being restored to a server running MariaDB or an older version of MySQL that does not support the collation `utf8mb4_0900_ai_ci`.
MySQL 8.0 introduced new collations, such as `utf8mb4_0900_ai_ci`. These collations are not supported in certain MariaDB or MySQL versions. As a result, restoring a backup that references these collations will fail on incompatible systems.
How to Resolve
There are a few ways to resolve this issue, depending on your environment:
Option 1: Modify the Backup File
If you do not require the exact collation , you can update the backup file to use a compatible collation, such as utf8mb4_general_ci.
Steps:
- Download and extract the database/database_user .sql files.
-
Open the
.sqlfile in a text editor, or run a similar command from the command line: -
Use the modified file (
fixed_backup.sql) for the restore.
Option 2: Restore to a Compatible MySQL Version
You can also try restoring your database to a version of MariaDB/MySQL that supports the collation that was used when the backup was generated.
Need Help?
While we’re happy to point you in the right direction, resolving collation and compatibility issues is typically outside the scope of our support. As the resolution involves selecting appropriate character set settings and potentially modifying the backup file, we recommend working with your database administrator (DBA) or system administrator to determine the best course of action based on your environment and data requirements.
If you have additional questions, feel free to contact us and we’ll do our best to assist.
