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

- **ID:** `database/mysql-unknown-collation`
- **Domain:** database
- **Category:** encoding_error
- **Error Code:** `1273`
- **Verification:** ai_generated
- **Fix Rate:** 83%

## 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).

## Version Compatibility

| Version | Status | Introduced | Deprecated |
|---------|--------|------------|------------|
| MySQL 5.7.44 | active | — | — |
| MySQL 5.6.51 | active | — | — |
| MariaDB 10.11.8 | active | — | — |

## Workarounds

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.** (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.
   ```
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.** (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.
   ```
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;** (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;
   ```

## Dead Ends

- **Changing the collation to a random one like 'utf8mb4_general_ci' without checking table definitions** — 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. (80% fail)
- **Upgrading MySQL to 8.0 without testing compatibility** — While upgrading fixes the collation issue, it may introduce other incompatibilities (e.g., SQL syntax changes, deprecated features) that break the application. (75% fail)
- **Ignoring the error and continuing with the import** — The error stops the import process; ignoring it will result in an incomplete or failed database restore. (100% fail)