Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL refuses or trims a value: Data truncated for column

ErrorData truncated for column 'status' at row 1

Short answer

MySQL reports truncation when a value does not fit the column. The common trigger is an ENUM column given a string that is not in its list. In strict mode the insert fails. In non-strict mode MySQL stores an adjusted value and only warns. Read the allowed values, fix the data, and keep strict mode on.

  • Applies to MySQL 8.0, MySQL 8.4 LTS
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the column definition and the warnings from the last statement.

    SHOW COLUMNS FROM orders LIKE 'status';
    SHOW WARNINGS;
  2. 2Match what you see, then jump to its fix

    1. 1The column type in SHOW COLUMNS starts with enum(...), and the rejected value is spelled differently (case, spaces, a code like 1 instead of a name).The value is not in the ENUM listFix
    2. 2You insert 2 and read back a different member than expected.Numbers sent to an ENUM are read as positionsFix
    3. 3Rows are saved, the client shows Query OK with warnings, and some values are changed.Non-strict SQL mode turns the failure into a silent warningFix

What does the error look like?

  • Data truncated for column 'status' at row 1

Cause 1 of 3

The value is not in the ENUM list

1Check

Read the allowed members. Strict mode raises an error for a value outside the list; non-strict mode stores the empty error value with index 0. Find rows that already hold it.

SHOW COLUMNS FROM orders LIKE 'status';
SELECT * FROM orders WHERE status = 0;

2Fix

Send one of the exact members, or add the new member to the column. Redefine the whole column and keep its other attributes.

ALTER TABLE orders MODIFY status ENUM('new','paid','shipped','cancelled') NOT NULL DEFAULT 'new';

3Verify

The insert that failed now succeeds.

INSERT INTO orders (status) VALUES ('cancelled');

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

Numbers sent to an ENUM are read as positions

1Check

A number stored into an ENUM column is taken as the index of the member, while a quoted string is matched by name. LOAD DATA treats everything as strings.

SELECT status, status + 0 AS idx FROM orders LIMIT 5;

2Fix

Send the member name as a quoted string, from your app code as well.

INSERT INTO orders (status) VALUES ('2');

3Verify

The stored member is the one you sent.

SELECT status FROM orders ORDER BY id DESC LIMIT 1;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

Non-strict SQL mode turns the failure into a silent warning

1Check

Without strict mode MySQL inserts adjusted values and produces warnings. Check the active mode.

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

2Fix

Restore the 8.4 default, which includes STRICT_TRANS_TABLES, so bad data is rejected at once. Then fix the data the app sends.

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

3Verify

A bad value now fails with an error instead of a warning.

INSERT INTO orders (status) VALUES ('typo');

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Keep the server on the default strict SQL mode, and set the same mode in tests.
  • Validate enum-like input in the app against one list shared with the schema.
  • Check SHOW WARNINGS after bulk loads; do not ignore 'with warnings'.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.