Fix unter MacOS und Linux für SQL Unknown collation: „utf8mb4_0900_ai_ci“ Error

Du exportierst eine Datenbank aus MySQL 8, liest sie in MariaDB ein — oder in ein älteres MySQL auf einem Shared Host — und der Import bleibt schon bei der ersten Tabelle stehen:

ERROR 1273 (HY000) at line 25: Unknown collation: 'utf8mb4_0900_ai_ci'

Kaputt ist nichts. utf8mb4_0900_ai_ci ist die Standard-Collation von MySQL 8.0, gebaut auf Unicode 9.0.0, und der Zielserver kennt den Namen einfach nicht. Der Zeichensatz utf8mb4 ist auf beiden Seiten derselbe, nur die Collation fehlt. Die Lösung ist darum eine Textersetzung in der Dump-Datei.

Zuerst sehen, was im Dump steht

grep -o 'utf8mb4_0900_[a-z_]*' dump.sql | sort | uniq -c

Meistens ist das utf8mb4_0900_ai_ci. Varianten mit Beachtung der Groß- und Kleinschreibung wie utf8mb4_0900_as_cs erscheinen genauso und brauchen dieselbe Behandlung.

Die Lösung in einer Zeile

Der Befehl unterscheidet sich zwischen macOS und Linux, weil beide ein anderes sed mitbringen. Unter macOS braucht -i ein Argument — eine leere Zeichenkette heißt „direkt in der Datei ändern, ohne Sicherung“.

macOS

sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql

Linux (GNU sed)

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql

Wenn im Dump mehrere 0900-Collations stehen, ersetze sie in einem Durchgang:

# Linux
sed -i -E 's/utf8mb4_0900_[a-z_]+/utf8mb4_unicode_ci/g' dump.sql
# macOS
sed -i '' -E 's/utf8mb4_0900_[a-z_]+/utf8mb4_unicode_ci/g' dump.sql

Behalte eine Kopie der Originaldatei, bevor Du direkt in ihr änderst. sed 's/…/…/g' dump.sql > dump-fix.sql macht dasselbe, ohne die Quelle anzufassen.

Danach lässt sich der Dump normal einlesen:

mysql -u benutzer -p zieldatenbank < dump.sql

Ohne Zwischendatei

Bei großen Dumps in einem Durchgang umschreiben und einlesen. Auf die Platte wird nichts Zusätzliches geschrieben:

sed 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql | mysql -u benutzer -p zieldatenbank
zcat dump.sql.gz | sed 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' | mysql -u benutzer -p zieldatenbank

Vorher den Zielserver fragen

utf8mb4_unicode_ci gibt es in MariaDB und in MySQL 5.7, aber ein Blick auf das übrige Angebot lohnt:

SHOW COLLATION LIKE 'utf8mb4%';

Neuere MariaDB-Versionen führen utf8mb4_uca1400_ai_ci, gebaut auf einer aktuellen Unicode-Fassung. Wenn Dein Server sie hat, ist sie die nähere Entsprechung zum Original aus MySQL 8. utf8mb4_general_ci funktioniert auch und ist schneller, sortiert aber naiv — für deutschen, skandinavischen oder osteuropäischen Text ist das die falsche Wahl.

Eine Sache, auf die Du nach der Migration achten solltest

Die Collation betrifft nicht nur ORDER BY. Sie entscheidet auch, welche Zeichenketten als gleich gelten — und das zählt bei UNIQUE-Indizes und bei Joins über Textspalten. Sorg dafür, dass Datenbank, Tabellen und Anwendung nach dem Umzug dieselbe Collation benutzen, und prüfe auch den Zeichensatz der Verbindung. Eine Mischung davon erzeugt den nächsten Fehler, Wochen später, wenn sich ein Name mit Umlaut plötzlich nicht zweimal speichern lässt.

Häufige Fragen

Woher kommt der Fehler?

utf8mb4_0900_ai_ci ist die Standard-Collation von MySQL 8.0 und beruht auf Unicode 9.0.0. MariaDB und MySQL 5.7 kennen sie nicht, darum scheitert jedes CREATE TABLE im Dump, das sie nennt, mit Fehler 1273.

Ist utf8mb4_unicode_ci ein genauer Ersatz?

Nicht genau. Sie benutzt eine ältere Unicode-Fassung, Sortierung und Vergleich weichen in Randfällen ab — bei Buchstaben mit Akzent, beim scharfen s, bei Emoji. Für die meisten Anwendungen ist das unsichtbar; wenn Du Dich auf Sortierung oder auf UNIQUE-Indizes über Text verlässt, teste es.

Und bei einem gepackten Dump?

Du musst ihn nicht entpacken, sondern kannst durchleiten: zcat dump.sql.gz | sed "s/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g" | mysql -u benutzer -p zieldatenbank.

Welche Collations hat mein Server überhaupt?

SHOW COLLATION LIKE "utf8mb4%"; listet sie auf. Neuere MariaDB-Versionen bieten zusätzlich utf8mb4_uca1400_ai_ci, die dem Verhalten von MySQL 8 näher kommt als utf8mb4_unicode_ci.