Einen ENUM-Wert in MySQL und MariaDB umbenennen, ohne Fehler 1265 „Data truncated“

„Jedes Problem wird ganz kindisch, sobald man es einem erklärt hat.“ Sherlock Holmes, in „Die tanzenden Männchen“ (1903).

Dieser Eintrag ist ein kleiner. Die Lösung sind drei Anweisungen, die man beim Lesen versteht, und so erzählt klingt es, als gäbe es nichts zu erzählen. Aber wie Holmes sagt: Alles ist einfach, sobald es einem jemand erklärt. An dem Tag, an dem es mir passierte, hatte ich niemanden, der es mir erklärte: Ich wusste nicht, was der Fehler bedeutete und wie ich da herauskam, und es kostete mich mehrere Arbeitsstunden.

Das ENUM, das korrigiert werden musste

Ich hatte eine Tabelle mit den Codes mehrerer Kataloge, und eine ENUM-Spalte gab an, zu welchem Katalog jede Zeile gehörte. Alle Werte standen im Plural außer einem, provincia, und er sollte provincias heißen:

CREATE TABLE codigos (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    catalogo ENUM('paises', 'provincia', 'cantones') NOT NULL,
    codigo   VARCHAR(10)  NOT NULL,
    nombre   VARCHAR(100) NOT NULL
);

INSERT INTO codigos (catalogo, codigo, nombre) VALUES
    ('paises',    'CR',  'Costa Rica'),
    ('provincia', '1',   'San José'),
    ('provincia', '4',   'Heredia'),
    ('cantones',  '101', 'San José'),
    ('cantones',  '102', 'Escazú');

Ein ENUM speichert nicht in jeder Zeile den Text, sondern die Position des Werts in der Liste (1, 2, 3 …). Das braucht wenig Platz, und die Engine lässt keinen Wert speichern, der nicht in der Liste steht. Genau das Letzte sollte mir Probleme machen.

Der Fehler

Naheliegend ist, die Definition der Spalte mit dem korrigierten Namen zu ändern:

mysql> ALTER TABLE codigos MODIFY catalogo ENUM('paises', 'provincias', 'cantones') NOT NULL;
ERROR 1265 (01000): Data truncated for column 'catalogo' at row 2

„Data truncated“ führt in die Irre, denn hier wird kein langer Text abgeschnitten. Was passiert: Beim Ändern der Definition konvertiert MySQL jede Zeile, indem es ihren Wert als Text in der neuen Liste sucht. Zeile 2 sagt provincia, und in der neuen Liste gibt es kein provincia: Für MySQL ist provincias ein anderer Wert, nicht derselbe mit einem Buchstaben mehr. Da es keinen Wert speichern kann, der nicht in der Liste steht, bricht es ab. MariaDB antwortet genauso, mit demselben Code.

Das Gute ist, dass das ALTER nichts halb fertig zurücklässt: Die Tabelle bleibt, wie sie war, mit ihren zwei Zeilen auf provincia.

Ohne Strict Mode ist es schlimmer

Dieser Fehler erscheint, weil der Server im Strict Mode läuft (STRICT_TRANS_TABLES in sql_mode), so wie die aktuellen Versionen von MySQL und MariaDB standardmäßig eingestellt sind. Auf einem Server ohne Strict Mode endet dasselbe ALTER „gut“:

mysql> SET SESSION sql_mode = '';
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE codigos MODIFY catalogo ENUM('paises', 'provincias', 'cantones') NOT NULL;
Query OK, 5 rows affected, 2 warnings (0.09 sec)
Records: 5  Duplicates: 0  Warnings: 2

mysql> SELECT id, catalogo, codigo, nombre FROM codigos;
+----+----------+--------+------------+
| id | catalogo | codigo | nombre     |
+----+----------+--------+------------+
|  1 | paises   | CR     | Costa Rica |
|  2 |          | 1      | San José   |
|  3 |          | 4      | Heredia    |
|  4 | cantones | 101    | San José   |
|  5 | cantones | 102    | Escazú     |
+----+----------+--------+------------+
5 rows in set (0.00 sec)

Die beiden Provinzen haben keinen Katalog mehr. Der leere Wert ist die Position 0 eines ENUM, die die Engine für alles verwendet, was nicht gültig war, und die Tabelle hält nirgends mehr fest, was dort vorher stand. Übrig bleiben nur zwei Warnungen, die man mit SHOW WARNINGS anfordern muss, um sie zu sehen. Zwischen dem und einem Fehler ist mir der Fehler tausendmal lieber.

Das Rezept in drei Schritten

Der Ausweg ist, MySQL nicht raten zu lassen. Zuerst fügt man den neuen Wert hinzu, dann verschiebt man die Zeilen vom einen zum anderen, und zum Schluss entfernt man den alten:

ALTER TABLE codigos MODIFY catalogo ENUM('paises', 'provincia', 'cantones', 'provincias') NOT NULL;

UPDATE codigos SET catalogo = 'provincias' WHERE catalogo = 'provincia';

ALTER TABLE codigos MODIFY catalogo ENUM('paises', 'provincias', 'cantones') NOT NULL;

• Das erste ALTER fügt provincias am Ende der Liste hinzu, neben provincia. Am Ende, weil sich so die Positionen der vorhandenen Werte nicht ändern und die Engine nur die Definition der Spalte ändern muss, ohne die Tabelle neu zu schreiben, solange die Liste nicht mehr als 255 Werte hat.
• Das UPDATE verschiebt die Zeilen von einem Wert zum anderen. Jetzt geht das, weil beide existieren.
• Das zweite ALTER entfernt provincia. Dieses schreibt die Tabelle tatsächlich neu, aber keine Zeile hat mehr einen Wert, der in der neuen Liste fehlt, also gibt es nichts abzuschneiden.

Im letzten Schritt habe ich provincias an die zweite Stelle gesetzt, wo provincia stand. Das ist keine Frage der Ästhetik: Ein ENUM wird nach der Position in der Liste sortiert, nicht alphabetisch, also liefert ein ORDER BY catalogo die Kataloge in der Reihenfolge der Liste:

SELECT catalogo, COUNT(*) AS filas
FROM codigos
GROUP BY catalogo
ORDER BY catalogo;

+------------+-------+
| catalogo   | filas |
+------------+-------+
| paises     |     1 |
| provincias |     2 |
| cantones   |     2 |
+------------+-------+

Hätte ich provincias am Ende gelassen, kämen die Provinzen nach den Kantonen.

Bei einer großen Tabelle dauert das zweite ALTER so lange, wie das Kopieren der ganzen Tabelle dauert, also macht man es am besten zu einer Zeit mit wenig Last.

Und wenn sich nur ein Akzent ändert?

Bei mir war es ein s. Hätte ich einen Akzent korrigieren müssen, wäre die Geschichte eine andere gewesen, und das Rezept hätte nicht funktioniert. Ein Katalog von Zahlungsarten, bei dem crédito der Akzent fehlt:

CREATE TABLE pagos (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    metodo ENUM('efectivo', 'tarjeta', 'credito') NOT NULL
);

INSERT INTO pagos (metodo) VALUES ('efectivo'), ('credito'), ('credito');

ALTER TABLE pagos MODIFY metodo ENUM('efectivo', 'tarjeta', 'crédito') NOT NULL;

Das direkte ALTER funktioniert, ohne Fehler und ohne Verlust: Beide Zeilen heißen jetzt crédito. Der Grund ist die Kollation (collation), die Regel, nach der die Engine Texte vergleicht. Die, die MySQL und MariaDB standardmäßig verwenden, enden auf _ai_ci: Sie unterscheiden weder Akzente (accent-insensitive) noch Groß- und Kleinschreibung (case-insensitive). Für sie sind credito und crédito derselbe Wert, also findet die Konvertierung ihn in der neuen Liste. Dasselbe gälte für Crédito.

Und aus demselben Grund scheitert das Rezept in drei Schritten schon am ersten, beim Versuch, beide Werte gleichzeitig zu haben:

mysql> ALTER TABLE pagos MODIFY metodo ENUM('efectivo', 'tarjeta', 'credito', 'crédito') NOT NULL;
ERROR 1291 (HY000): Column 'metodo' has duplicated value 'credito' in ENUM

All das hängt von der Kollation der Spalte ab. Mit einer binären, wie utf8mb4_bin, zählt der Akzent wie jeder andere Buchstabe: Das direkte ALTER ergibt Fehler 1265, genau wie beim s, und das Rezept in drei Schritten funktioniert. Bevor ich ein ENUM korrigiere, schaue ich mir die Kollation der Spalte an, oder die der Tabelle, wenn die Spalte keine angibt, in dem, was SHOW CREATE TABLE zurückgibt.

In PostgreSQL ist es eine Zeile

In PostgreSQL ist ein ENUM ein Typ, den man separat anlegt, und seit Version 10 ist das Umbenennen eines Werts eine einzige Anweisung, ohne UPDATE und ohne die Zeilen anzufassen:

CREATE TYPE catalogo AS ENUM ('paises', 'provincia', 'cantones');

ALTER TYPE catalogo RENAME VALUE 'provincia' TO 'provincias';

Was PostgreSQL nicht hat, ist das Gegenteil: Es gibt keine Möglichkeit, einen Wert aus einem ENUM zu entfernen, ohne den Typ neu anzulegen.

Drei Anweisungen

Heute schreibe ich sie, ohne nachzudenken. An jenem Tag haben sie mich mehrere Stunden gekostet, und deshalb lasse ich sie hier, damit es jedem, dem ein 1265 begegnet, genügt, sie zu lesen.