MySQL error 1273: Unknown collation utf8mb4_0900_ai_ci
ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'Short answer
The target server has no collation named utf8mb4_0900_ai_ci. MySQL 8 uses it as the default for utf8mb4, so dumps from MySQL 8 carry it everywhere. Check which collations the target has, then replace the name in the dump with one both servers know, such as utf8mb4_unicode_ci.
Fix it fast
1Find out which cause you have
Ask the server that gives the error which utf8mb4 collations it knows. If utf8mb4_0900_ai_ci is not in the list, the cause is below.
SELECT VERSION(); SHOW COLLATION WHERE Charset = 'utf8mb4';2Match what you see, then jump to its fix
- 1
The version printed is not MySQL 8, or SHOW COLLATION does not list utf8mb4_0900_ai_ci. The failing line is a CREATE TABLE with COLLATE=utf8mb4_0900_ai_ci.The dump names a collation the target server does not haveFix - 2
The error appears on connect or on the first query, not during an import.A connection or app sets the collation explicitlyFix - 3
The same schema is deployed to MySQL 8 and to an older server.You will keep moving data between old and new serversFix
- 1
What does the error look like?
ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'
Cause 1 of 3
The dump names a collation the target server does not have
1Check
The 8.x manual states the default server collation is utf8mb4_0900_ai_ci, so every table in a plain dump from such a server carries it. Count the places in the dump.
grep -c "utf8mb4_0900_ai_ci" dump.sql2Fix
Replace the collation in the dump with an older utf8mb4 collation that the target lists, then import. The manual calls utf8mb4_unicode_ci more accurate than the legacy utf8mb4_general_ci. Both pad-space collations compare 'a' and 'a ' as equal, unlike the 0900 ones, which do not.
sed 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql > dump-fixed.sql
mysql -u root -p mydb < dump-fixed.sql3Verify
The import finishes with no 1273 error and the tables exist.
SHOW TABLE STATUS\GConfirmed by dev.mysql.com
Cause 2 of 3
A connection or app sets the collation explicitly
1Check
SET NAMES with a COLLATE clause, or a framework setting that names the collation, makes the client ask for utf8mb4_0900_ai_ci on a server that does not know it.
grep -rn "0900_ai_ci" config/ src/2Fix
Name a collation both servers have, or give only the character set so the server picks its own default.
SET NAMES 'utf8mb4';3Verify
The connection opens and queries run.
SELECT @@collation_connection;Confirmed by dev.mysql.com
Cause 3 of 3
You will keep moving data between old and new servers
1Check
The server default comes from collation_server, and it applies to new databases and tables that do not name a collation.
SHOW VARIABLES LIKE 'collation_server';2Fix
Set a collation both servers know when creating databases and tables, so dumps stay portable.
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;3Verify
A new dump contains your chosen collation, not the 0900 one.
mysqldump --no-data mydb | grep -c "utf8mb4_unicode_ci"Confirmed by dev.mysql.com
How do I stop it coming back?
- Check the collation list of the target server before restoring a dump from another version.
- Pin an explicit collation in schema files that run on more than one server version.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL refuses to compare two strings: illegal mix of collations
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_bin,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
Error 1267 means two string values in one comparison have different collations and MySQL cannot pick a winner.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix