MySQL: ERROR 1265 (01000): Data truncated for column

What it means

“Truncated” means the server could only store the value by cutting it down or replacing it. In strict mode (the default) it refuses instead, with error 1265, naming the column and the row of the statement. Nothing is stored.

Its relatives cover the other ways a value can fail to fit: a string longer than a VARCHAR is 1406, data too long, a number outside the type’s range is 1264, out of range, and text that isn’t a number at all is usually 1366, Incorrect integer value. 1265 is what’s left, above all ENUM and SET columns and ALTER TABLE.

Not every 1265 is an error: storing 12.345 in a DECIMAL(6,2) rounds it to 12.35 with only Note 1265.

Common causes

A value that isn’t in an ENUM list: 'pending' when the column is enum('open','closed'), or an empty string from a form. (Letter case doesn’t matter with the default collations: 'Open' is accepted as open.)
An ALTER TABLE that removes an ENUM value still in use, or renames one: the rows holding the old value don’t fit the new list.
An ALTER TABLE that shrinks a column below data already in it, such as varchar(10) to varchar(5).
LOAD DATA with empty fields: an empty field for an ENUM column is truncated, and an empty field for a number is Incorrect integer value: '' (1366).
A SET column given a member that isn’t in its list.

How to fix it

ENUM: send a value from the list

SHOW COLUMNS FROM tickets LIKE 'status';

Field	Type	Null	Key	Default	Extra
status	enum('open','closed')	NO		open

Map the application’s values onto those, or add the new value to the list. Adding a value at the end of the list is a quick change; changing or removing values rewrites the table:

ALTER TABLE tickets MODIFY status enum('open','closed','pending') NOT NULL DEFAULT 'open';

Changing an ENUM list: move the data first

To rename closed into closed_won and closed_lost, add the new values alongside the old one, update the rows, then drop the old value:

ALTER TABLE tickets MODIFY status enum('open','closed','closed_won','closed_lost') NOT NULL DEFAULT 'open';
UPDATE tickets SET status = 'closed_won' WHERE status = 'closed';
ALTER TABLE tickets MODIFY status enum('open','closed_won','closed_lost') NOT NULL DEFAULT 'open';

Shrinking a column: check the data first

SELECT id, v, CHAR_LENGTH(v) FROM tsmall WHERE CHAR_LENGTH(v) > 5;

Shorten or move those values, then run the ALTER TABLE. See changing a column type for how it locks the table.

LOAD DATA: turn empty fields into NULL

Read the field into a variable and convert it:

LOAD DATA INFILE '/var/lib/mysql-files/tickets.csv' INTO TABLE tickets
FIELDS TERMINATED BY ','
(id, status, @qty)
SET qty = NULLIF(@qty, '');

A field written as \N in the file is read as NULL without any of this.

Don’t switch off strict mode

Without it, an invalid ENUM value is stored as the empty string (the special error value, index 0) with a warning, and the row looks valid until someone reads it.

Reproduce it

On MySQL 8.4.11, with tickets (status enum('open','closed') NOT NULL DEFAULT 'open', flags set('a','b')) and tsmall (v varchar(10)) holding 'abcdefghij':

INSERT INTO tickets (status) VALUES ('pending');
INSERT INTO tickets (status) VALUES ('');
INSERT INTO tickets (flags) VALUES ('c');
ALTER TABLE tickets MODIFY status enum('open','closed_won','closed_lost') NOT NULL DEFAULT 'open';
ALTER TABLE tsmall MODIFY v varchar(5);

ERROR 1265 (01000): Data truncated for column 'status' at row 1
ERROR 1265 (01000): Data truncated for column 'status' at row 1
ERROR 1265 (01000): Data truncated for column 'flags' at row 1
ERROR 1265 (01000): Data truncated for column 'status' at row 4
ERROR 1265 (01000): Data truncated for column 'v' at row 1

The ALTER on tickets named row 4, the first row holding closed. 'Open' was accepted. With sql_mode = '', 'pending' was stored as an empty string with Warning 1265.

In a temporary MySQL 8.4.11 container, LOAD DATA INFILE of a CSV whose second line had empty fields stopped at the ENUM, or, once that was filled in, at the integer:

ERROR 1265 (01000): Data truncated for column 'status' at row 2
ERROR 1366 (HY000): Incorrect integer value: '' for column 'qty' at row 2

\N and SET qty = NULLIF(@qty, '') both loaded the row with qty NULL.

MariaDB 11.4.13 gave the same 1265 errors. It also used 1265 where MySQL used 1366, for '12abc' into a DECIMAL.

In Inlet

Inlet’s structure editor changes an ENUM list or a column’s size in one ALTER TABLE, shows the DDL before it runs, and notes that MySQL may copy the table while it runs. When a statement fails, Inlet shows the error and links to this page.

ERROR 1265 (01000): Data truncated for column