Cómo renombrar un valor de un ENUM en MySQL y MariaDB sin el error 1265 «Data truncated»

«Todo problema se vuelve muy infantil una vez que se lo explican a uno.» Sherlock Holmes, en «La aventura de los bailarines» (1903).

Esta entrada es chiquita. La solución son tres sentencias que se entienden al leerlas, y contada así parece que no hubiera nada que contar. Pero, como dice Holmes, todo es sencillo una vez que alguien lo explica. El día que me pasó a mí no tenía a nadie que me lo explicara: no sabía qué significaba el error ni cómo salir de él, y se me fueron varias horas de trabajo.

El ENUM que había que corregir

Tenía una tabla con los códigos de varios catálogos, y una columna ENUM decía a qué catálogo pertenecía cada fila. Todos los valores estaban en plural menos uno, provincia, y había que llamarlo 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 no guarda el texto en cada fila: guarda la posición del valor en la lista (1, 2, 3…). Ocupa poco, y el motor no deja guardar un valor que no esté en la lista. Eso último es justo lo que me iba a dar problemas.

El error

Lo natural es cambiar la definición de la columna con el nombre corregido:

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» despista, porque aquí no se corta ningún texto largo. Lo que pasa es que, al cambiar la definición, MySQL convierte cada fila buscando su valor, como texto, en la lista nueva. La fila 2 dice provincia, y en la lista nueva no hay ningún provincia: para MySQL, provincias es otro valor, no el mismo con una letra más. Como no puede guardar un valor que no está en la lista, se detiene. MariaDB responde exactamente igual, con el mismo código.

Lo bueno es que el ALTER no deja nada a medias: la tabla sigue como estaba, con sus dos filas en provincia.

Sin modo estricto es peor

Ese error sale porque el servidor está en modo estricto (STRICT_TRANS_TABLES en sql_mode), que es como vienen por omisión las versiones actuales de MySQL y de MariaDB. En un servidor sin modo estricto, el mismo ALTER termina «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)

Las dos provincias se quedaron sin catálogo. El valor vacío es la posición 0 de un ENUM, la que el motor usa para lo que no era válido, y la tabla ya no guarda en ningún sitio lo que decían antes. Lo único que queda son dos advertencias, que hay que pedir con SHOW WARNINGS para verlas. Entre eso y un error, prefiero mil veces el error.

La receta de tres pasos

La salida es no pedirle a MySQL que adivine. Primero se añade el valor nuevo, después se pasan las filas de uno a otro y al final se quita el viejo:

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;

• El primer ALTER añade provincias al final de la lista, junto a provincia. Al final, porque así las posiciones de los valores que ya existen no cambian, y el motor sólo tiene que cambiar la definición de la columna, sin reescribir la tabla, mientras la lista no pase de 255 valores.
• El UPDATE pasa las filas de un valor al otro. Ahora sí se puede, porque los dos existen.
• El segundo ALTER quita provincia. Éste sí reescribe la tabla, pero ya ninguna fila tiene un valor que falte en la lista nueva, así que no hay nada que truncar.

En el último paso puse provincias en el segundo lugar, donde estaba provincia. No es por estética: un ENUM se ordena por la posición en la lista, no por orden alfabético, así que un ORDER BY catalogo devuelve los catálogos en el orden de la lista:

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

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

Si hubiera dejado provincias al final, las provincias saldrían después de los cantones.

En una tabla grande, el segundo ALTER tarda lo que tarde copiar la tabla entera, así que conviene hacerlo en un momento de poca carga.

¿Y si sólo cambia una tilde?

Lo mío fue una s. Si hubiera tenido que corregir una tilde, la historia habría sido otra, y la receta no habría servido. Un catálogo de formas de pago al que le falta la tilde en 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;

El ALTER directo funciona, sin error y sin perder nada: las dos filas pasan a decir crédito. La razón está en la intercalación (collation), que es la regla con la que el motor compara textos. Las que traen por omisión MySQL y MariaDB terminan en _ai_ci: no distinguen acentos (accent-insensitive) ni mayúsculas (case-insensitive). Para ellas, credito y crédito son el mismo valor, así que la conversión lo encuentra en la lista nueva. Lo mismo pasaría con Crédito.

Y por la misma razón, la receta de tres pasos falla en el primero, al intentar tener los dos valores a la vez:

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

Todo esto depende de la intercalación de la columna. Con una binaria, como utf8mb4_bin, la tilde cuenta como cualquier otra letra: el ALTER directo da el error 1265, igual que con la s, y la receta de tres pasos funciona. Antes de corregir un ENUM, miro la intercalación de la columna, o la de la tabla si la columna no la declara, en lo que devuelve SHOW CREATE TABLE.

En PostgreSQL es una línea

En PostgreSQL un ENUM es un tipo que se crea aparte, y desde la versión 10 renombrar un valor es una sola sentencia, sin UPDATE y sin tocar las filas:

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

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

Lo que PostgreSQL no tiene es lo contrario: no hay forma de quitar un valor de un ENUM sin volver a crear el tipo.

Tres sentencias

Hoy las escribo sin pensarlo. Aquel día me costaron varias horas, y por eso las dejo aquí, para que a quien le salga el 1265 le baste con leerlas.