Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL error 1273: Unknown collation utf8mb4_0900_ai_ci

ErrorERROR 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.

  • Applies to MySQL 8.4 LTS as source, any older server as target
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2The error appears on connect or on the first query, not during an import.A connection or app sets the collation explicitlyFix
    3. 3The same schema is deployed to MySQL 8 and to an older server.You will keep moving data between old and new serversFix

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.sql

2Fix

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.sql

3Verify

The import finishes with no 1273 error and the tables exist.

SHOW TABLE STATUS\G

Confirmed by dev.mysql.com

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

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 fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.