Comment renommer une valeur d'un ENUM dans MySQL et MariaDB sans l'erreur 1265 « Data truncated »

« Tout problème devient très puéril dès qu'on vous l'a expliqué. » Sherlock Holmes, dans « Les Hommes dansants » (1903).

Cette entrée est toute petite. La solution tient en trois instructions qu'on comprend en les lisant, et racontée ainsi, on dirait qu'il n'y a rien à raconter. Mais, comme dit Holmes, tout est simple une fois que quelqu'un l'explique. Le jour où cela m'est arrivé, je n'avais personne pour me l'expliquer : je ne savais pas ce que voulait dire l'erreur ni comment m'en sortir, et j'y ai perdu plusieurs heures de travail.

L'ENUM qu'il fallait corriger

J'avais une table avec les codes de plusieurs catalogues, et une colonne ENUM indiquait à quel catalogue appartenait chaque ligne. Toutes les valeurs étaient au pluriel sauf une, provincia, qu'il fallait appeler provincias :

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ú');

Un ENUM ne stocke pas le texte dans chaque ligne : il stocke la position de la valeur dans la liste (1, 2, 3…). Il prend peu de place, et le moteur ne laisse pas enregistrer une valeur qui n'est pas dans la liste. C'est justement ce dernier point qui allait me poser problème.

L'erreur

Le plus naturel est de modifier la définition de la colonne avec le nom corrigé :

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 » est trompeur, car aucun texte long n'est coupé ici. Ce qui se passe, c'est qu'en changeant la définition, MySQL convertit chaque ligne en cherchant sa valeur, en tant que texte, dans la nouvelle liste. La ligne 2 dit provincia, et dans la nouvelle liste il n'y a aucun provincia : pour MySQL, provincias est une autre valeur, pas la même avec une lettre de plus. Comme il ne peut pas enregistrer une valeur absente de la liste, il s'arrête. MariaDB répond exactement pareil, avec le même code.

Heureusement, l'ALTER ne laisse rien à moitié fait : la table reste telle qu'elle était, avec ses deux lignes à provincia.

Sans mode strict, c'est pire

Cette erreur apparaît parce que le serveur est en mode strict (STRICT_TRANS_TABLES dans sql_mode), ce qui est la configuration par défaut des versions actuelles de MySQL et de MariaDB. Sur un serveur sans mode strict, le même ALTER se termine « bien » :

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)

Les deux provinces se sont retrouvées sans catalogue. La valeur vide est la position 0 d'un ENUM, celle que le moteur utilise pour ce qui n'était pas valide, et la table ne garde plus nulle part ce que ces lignes disaient avant. Il ne reste que deux avertissements, qu'il faut demander avec SHOW WARNINGS pour les voir. Entre ça et une erreur, je préfère mille fois l'erreur.

La recette en trois étapes

La solution est de ne pas demander à MySQL de deviner. On ajoute d'abord la nouvelle valeur, puis on fait passer les lignes de l'une à l'autre, et à la fin on retire l'ancienne :

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;

• Le premier ALTER ajoute provincias à la fin de la liste, à côté de provincia. À la fin, parce qu'ainsi les positions des valeurs qui existent déjà ne changent pas, et le moteur n'a qu'à modifier la définition de la colonne, sans réécrire la table, tant que la liste ne dépasse pas 255 valeurs.
• L'UPDATE fait passer les lignes d'une valeur à l'autre. Maintenant c'est possible, parce que les deux existent.
• Le second ALTER retire provincia. Celui-ci réécrit bien la table, mais plus aucune ligne n'a une valeur absente de la nouvelle liste, donc il n'y a rien à tronquer.

À la dernière étape, j'ai mis provincias en deuxième position, là où était provincia. Ce n'est pas pour l'esthétique : un ENUM se trie selon sa position dans la liste, pas par ordre alphabétique, donc un ORDER BY catalogo renvoie les catalogues dans l'ordre de la liste :

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

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

Si j'avais laissé provincias à la fin, les provinces sortiraient après les cantons.

Sur une grande table, le second ALTER prend le temps qu'il faut pour copier la table entière, il vaut donc mieux le faire à un moment de faible charge.

Et si seul un accent change ?

Pour moi, c'était un s. Si j'avais dû corriger un accent, l'histoire aurait été autre, et la recette n'aurait pas marché. Un catalogue de moyens de paiement où il manque l'accent à crédito :

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;

L'ALTER direct fonctionne, sans erreur et sans rien perdre : les deux lignes passent à crédito. La raison, c'est la collation, la règle avec laquelle le moteur compare les textes. Celles que MySQL et MariaDB utilisent par défaut se terminent par _ai_ci : elles ne distinguent ni les accents (accent-insensitive) ni la casse (case-insensitive). Pour elles, credito et crédito sont la même valeur, donc la conversion la trouve dans la nouvelle liste. Il en irait de même avec Crédito.

Et pour la même raison, la recette en trois étapes échoue dès la première, en essayant d'avoir les deux valeurs à la fois :

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

Tout cela dépend de la collation de la colonne. Avec une collation binaire, comme utf8mb4_bin, l'accent compte comme n'importe quelle autre lettre : l'ALTER direct donne l'erreur 1265, comme avec le s, et la recette en trois étapes fonctionne. Avant de corriger un ENUM, je regarde la collation de la colonne, ou celle de la table si la colonne n'en déclare pas, dans ce que renvoie SHOW CREATE TABLE.

Dans PostgreSQL, c'est une ligne

Dans PostgreSQL, un ENUM est un type qu'on crée à part, et depuis la version 10, renommer une valeur tient en une seule instruction, sans UPDATE et sans toucher aux lignes :

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

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

Ce que PostgreSQL n'a pas, c'est l'inverse : il n'y a aucun moyen de retirer une valeur d'un ENUM sans recréer le type.

Trois instructions

Aujourd'hui je les écris sans y penser. Ce jour-là, elles m'ont coûté plusieurs heures, et c'est pour cela que je les laisse ici, pour qu'à celui qui tombe sur le 1265 il suffise de les lire.