Come rinominare un valore di un ENUM in MySQL e MariaDB senza l'errore 1265 «Data truncated»

«Ogni problema diventa infantile una volta che te lo hanno spiegato.» Sherlock Holmes, in «L'avventura degli omini danzanti» (1903).

Questa voce è piccolina. La soluzione sono tre istruzioni che si capiscono leggendole, e raccontata così sembra che non ci sia niente da raccontare. Ma, come dice Holmes, tutto è semplice una volta che qualcuno lo spiega. Il giorno in cui è successo a me non avevo nessuno che me lo spiegasse: non sapevo cosa volesse dire l'errore né come uscirne, e ci ho perso diverse ore di lavoro.

L'ENUM da correggere

Avevo una tabella con i codici di diversi cataloghi, e una colonna ENUM diceva a quale catalogo apparteneva ogni riga. Tutti i valori erano al plurale tranne uno, provincia, che bisognava chiamare 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 non salva il testo in ogni riga: salva la posizione del valore nella lista (1, 2, 3…). Occupa poco, e il motore non lascia salvare un valore che non sia nella lista. Proprio quest'ultima cosa mi avrebbe dato problemi.

L'errore

La cosa naturale è cambiare la definizione della colonna con il nome corretto:

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» trae in inganno, perché qui non si taglia nessun testo lungo. Quello che succede è che, cambiando la definizione, MySQL converte ogni riga cercando il suo valore, come testo, nella nuova lista. La riga 2 dice provincia, e nella nuova lista non c'è nessun provincia: per MySQL, provincias è un altro valore, non lo stesso con una lettera in più. Siccome non può salvare un valore che non è nella lista, si ferma. MariaDB risponde esattamente allo stesso modo, con lo stesso codice.

La cosa buona è che l'ALTER non lascia niente a metà: la tabella resta com'era, con le sue due righe su provincia.

Senza modalità strict è peggio

Quell'errore esce perché il server è in modalità strict (STRICT_TRANS_TABLES in sql_mode), che è come arrivano di default le versioni attuali di MySQL e di MariaDB. Su un server senza modalità strict, lo stesso ALTER finisce «bene»:

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)

Le due province sono rimaste senza catalogo. Il valore vuoto è la posizione 0 di un ENUM, quella che il motore usa per ciò che non era valido, e la tabella non conserva più da nessuna parte quello che quelle righe dicevano prima. Restano solo due avvisi, che bisogna chiedere con SHOW WARNINGS per vederli. Tra questo e un errore, preferisco mille volte l'errore.

La ricetta in tre passi

La via d'uscita è non chiedere a MySQL di indovinare. Prima si aggiunge il valore nuovo, poi si spostano le righe dall'uno all'altro e alla fine si toglie quello vecchio:

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;

• Il primo ALTER aggiunge provincias in fondo alla lista, accanto a provincia. In fondo, perché così le posizioni dei valori che esistono già non cambiano, e il motore deve solo cambiare la definizione della colonna, senza riscrivere la tabella, finché la lista non supera i 255 valori.
• L'UPDATE sposta le righe da un valore all'altro. Adesso si può, perché esistono tutti e due.
• Il secondo ALTER toglie provincia. Questo sì riscrive la tabella, ma nessuna riga ha più un valore che manchi nella nuova lista, quindi non c'è niente da troncare.

Nell'ultimo passo ho messo provincias al secondo posto, dove stava provincia. Non è per estetica: un ENUM si ordina per la posizione nella lista, non in ordine alfabetico, quindi un ORDER BY catalogo restituisce i cataloghi nell'ordine della lista:

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

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

Se avessi lasciato provincias in fondo, le province uscirebbero dopo i cantoni.

Su una tabella grande, il secondo ALTER impiega quanto serve a copiare la tabella intera, quindi conviene farlo in un momento di poco carico.

E se cambia solo un accento?

Il mio caso era una s. Se avessi dovuto correggere un accento, la storia sarebbe stata un'altra, e la ricetta non sarebbe servita. Un catalogo di metodi di pagamento in cui a crédito manca l'accento:

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 diretto funziona, senza errori e senza perdere niente: le due righe passano a dire crédito. Il motivo è la collation, cioè la regola con cui il motore confronta i testi. Quelle che MySQL e MariaDB usano di default finiscono in _ai_ci: non distinguono gli accenti (accent-insensitive) né le maiuscole (case-insensitive). Per loro, credito e crédito sono lo stesso valore, quindi la conversione lo trova nella nuova lista. Lo stesso succederebbe con Crédito.

E per la stessa ragione, la ricetta in tre passi fallisce al primo, quando prova ad avere i due valori insieme:

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

Tutto questo dipende dalla collation della colonna. Con una binaria, come utf8mb4_bin, l'accento conta come qualsiasi altra lettera: l'ALTER diretto dà l'errore 1265, come con la s, e la ricetta in tre passi funziona. Prima di correggere un ENUM, guardo la collation della colonna, o quella della tabella se la colonna non la dichiara, in quello che restituisce SHOW CREATE TABLE.

In PostgreSQL è una riga

In PostgreSQL un ENUM è un tipo che si crea a parte, e dalla versione 10 rinominare un valore è una sola istruzione, senza UPDATE e senza toccare le righe:

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

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

Quello che PostgreSQL non ha è il contrario: non c'è modo di togliere un valore da un ENUM senza ricreare il tipo.

Tre istruzioni

Oggi le scrivo senza pensarci. Quel giorno mi sono costate diverse ore, ed è per questo che le lascio qui, perché a chi si trova davanti il 1265 basti leggerle.