How to rename an ENUM value in MySQL and MariaDB without error 1265 "Data truncated"

“Every problem becomes very childish when once it is explained to you.” Sherlock Holmes, in “The Adventure of the Dancing Men” (1903).

This entry is a small one. The fix is three statements that make sense as soon as you read them, and told like this it looks as if there were nothing to tell. But, as Holmes says, everything is simple once someone explains it. The day it happened to me I had nobody to explain it: I didn't know what the error meant or how to get out of it, and several hours of work went into it.

The ENUM that needed fixing

I had a table with the codes of several catalogs, and an ENUM column said which catalog each row belonged to. All the values were plural except one, provincia, and it had to be called 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ú');

An ENUM doesn't store the text in each row: it stores the position of the value in the list (1, 2, 3…). It takes little space, and the engine won't let you store a value that isn't in the list. That last part is exactly what was going to give me trouble.

The error

The natural thing is to change the column definition with the corrected name:

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" is misleading, because no long text is being cut here. What happens is that, when the definition changes, MySQL converts each row by looking up its value, as text, in the new list. Row 2 says provincia, and the new list has no provincia: to MySQL, provincias is a different value, not the same one with an extra letter. Since it can't store a value that isn't in the list, it stops. MariaDB answers exactly the same, with the same code.

The good news is that the ALTER leaves nothing half done: the table stays as it was, with its two rows in provincia.

Without strict mode it's worse

That error appears because the server is in strict mode (STRICT_TRANS_TABLES in sql_mode), which is how current versions of MySQL and MariaDB come by default. On a server without strict mode, the same ALTER finishes "fine":

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)

The two provinces were left without a catalog. The empty value is position 0 of an ENUM, the one the engine uses for whatever wasn't valid, and the table no longer keeps anywhere what those rows said before. All that's left are two warnings, which you have to ask for with SHOW WARNINGS to see them. Between that and an error, I'll take the error a thousand times over.

The three-step recipe

The way out is not to ask MySQL to guess. First you add the new value, then you move the rows from one to the other, and finally you remove the old one:

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;

• The first ALTER adds provincias at the end of the list, next to provincia. At the end, because that way the positions of the values that already exist don't change, and the engine only has to change the column definition, without rewriting the table, as long as the list doesn't go past 255 values.
• The UPDATE moves the rows from one value to the other. Now it can, because both exist.
• The second ALTER removes provincia. This one does rewrite the table, but no row has a value missing from the new list anymore, so there is nothing to truncate.

In the last step I put provincias in second place, where provincia was. It isn't for looks: an ENUM sorts by its position in the list, not alphabetically, so an ORDER BY catalogo returns the catalogs in the order of the list:

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

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

If I had left provincias at the end, the provinces would come out after the cantons.

On a large table, the second ALTER takes as long as copying the whole table, so it's best done at a time of low load.

What if only an accent changes?

Mine was an s. If I'd had to fix an accent, the story would have been different, and the recipe wouldn't have worked. A catalog of payment methods where crédito is missing its accent:

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;

The direct ALTER works, with no error and nothing lost: both rows now say crédito. The reason is the collation, which is the rule the engine uses to compare text. The ones MySQL and MariaDB use by default end in _ai_ci: they are accent-insensitive and case-insensitive. To them, credito and crédito are the same value, so the conversion finds it in the new list. The same would happen with Crédito.

And for the same reason, the three-step recipe fails at the first step, when it tries to have both values at once:

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 of this depends on the column's collation. With a binary one, such as utf8mb4_bin, the accent counts like any other letter: the direct ALTER gives error 1265, just as with the s, and the three-step recipe works. Before fixing an ENUM, I check the column's collation, or the table's if the column doesn't declare one, in what SHOW CREATE TABLE returns.

In PostgreSQL it's one line

In PostgreSQL an ENUM is a type created separately, and since version 10 renaming a value is a single statement, with no UPDATE and without touching the rows:

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

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

What PostgreSQL doesn't have is the opposite: there is no way to remove a value from an ENUM without creating the type again.

Three statements

Today I write them without thinking. That day they cost me several hours, and that's why I leave them here, so that whoever gets a 1265 only has to read them.