MySQL refuses or trims a value: Data truncated for column
Data truncated for column 'status' at row 1Short 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
The 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
You insert 2 and read back a different member than expected.Numbers sent to an ENUM are read as positionsFix - 3
Rows 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
- 1
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
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
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix