1273 database encoding_error ai_generated true

ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

ID: database/mysql-unknown-collation

Also available as: JSON · Markdown · 中文
83%Fix Rate
86%Confidence
1Evidence
2024-03-18First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
MySQL 5.7.44 active — — —
MySQL 5.6.51 active — — —
MariaDB 10.11.8 active — — —

Root Cause

A MySQL database or table uses a collation (utf8mb4_0900_ai_ci) that is not supported by the current server version, typically because the collation was introduced in MySQL 8.0 but the server is on an older version (e.g., 5.7 or 5.6).

generic

中文

MySQL 数据库或表使用了当前服务器版本不支持的排序规则（utf8mb4_0900_ai_ci），通常是因为该排序规则是在 MySQL 8.0 中引入的，但服务器运行在较旧版本（例如 5.7 或 5.6）上。

Official Documentation

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb4.html

Workarounds

  1. 85% success Replace the collation in the SQL dump file using sed: sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' dump.sql, then import the modified dump: mysql -u root -p mydb < dump.sql. 
    Replace the collation in the SQL dump file using sed: sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' dump.sql, then import the modified dump: mysql -u root -p mydb < dump.sql.
  2. 80% success If the dump is from a MySQL 8.0 database, use mysqldump with the --compatible=mysql56 flag to generate a dump compatible with older versions: mysqldump --compatible=mysql56 -u root -p mydb > dump.sql. 
    If the dump is from a MySQL 8.0 database, use mysqldump with the --compatible=mysql56 flag to generate a dump compatible with older versions: mysqldump --compatible=mysql56 -u root -p mydb > dump.sql.
  3. 78% success Manually alter the collation of the affected tables after import (if the import partially succeeds): ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; 
    Manually alter the collation of the affected tables after import (if the import partially succeeds): ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

中文步骤

  1. Replace the collation in the SQL dump file using sed: sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' dump.sql, then import the modified dump: mysql -u root -p mydb < dump.sql.
  2. If the dump is from a MySQL 8.0 database, use mysqldump with the --compatible=mysql56 flag to generate a dump compatible with older versions: mysqldump --compatible=mysql56 -u root -p mydb > dump.sql.
  3. Manually alter the collation of the affected tables after import (if the import partially succeeds): ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Dead Ends

Common approaches that don't work:

  1. Changing the collation to a random one like 'utf8mb4_general_ci' without checking table definitions 80% fail

    This may work for some tables but can cause data inconsistency if the original collation had specific requirements (e.g., case sensitivity); also, it requires altering each table individually.

  2. Upgrading MySQL to 8.0 without testing compatibility 75% fail

    While upgrading fixes the collation issue, it may introduce other incompatibilities (e.g., SQL syntax changes, deprecated features) that break the application.

  3. Ignoring the error and continuing with the import 100% fail

    The error stops the import process; ignoring it will result in an incomplete or failed database restore.