How to Fix MySQL Warning 1265: Data Truncated for Column

Warning 1265 (01000): Data truncated for column '<col>' at row <n> is raised when MySQL must silently cut or coerce a value to make it fit a column's type or length constraint. The error symbol is ER_WARN_DATA_TRUNCATED.

Impact

In permissive (non-strict) SQL mode, MySQL 1265 is a warning, not an error. The INSERT or UPDATE succeeds, but the stored value differs from what the application supplied — a string may be silently shortened, a decimal may lose fractional digits, or an out-of-range ENUM value may become an empty string. The application receives no exception; it only sees the truncated value the next time it reads the row. This is a common source of silent data corruption.

In strict SQL mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES), the same situation is promoted to a hard error, the statement is rolled back, and the client receives the warning message as an error. Most production configurations and recent MySQL defaults (5.7.5+) enable strict mode, so developers who develop locally with permissive settings may encounter this error for the first time in staging or production.

Common Causes

String value longer than the column's character limit. Inserting a 120-character string into a VARCHAR(100) column causes the trailing 20 characters to be dropped.
Inserting an out-of-range value into an ENUM or SET column. If the supplied value does not match any defined member, MySQL stores an empty string (permissive) or raises an error (strict).
Decimal or numeric value with more fractional digits than the column allows. A DECIMAL(10,2) column will truncate 99.999 to 100.00 (rounding) without complaint in permissive mode. Values that also exceed the integer range raise a separate overflow error.
Wrong data type passed by the application. An ORM may serialise an object or array as a string longer than the target column, or a migration may shrink a column without updating application code.
Character set conversion overflow. A multi-byte UTF-8 character stored in a column with a smaller byte budget (e.g., a VARCHAR(10) in a latin1 column where the source string contains multi-byte characters) can trigger truncation.

Troubleshooting and Resolution Steps

Identify which column is affected. The warning message names the column directly. After a failed statement run SHOW WARNINGS; to see the full list:

SHOW WARNINGS;
-- +-------+------+------------------------------------------------+
-- | Level | Code | Message                                        |
-- +-------+------+------------------------------------------------+
-- | Warning | 1265 | Data truncated for column 'bio' at row 1     |
-- +-------+------+------------------------------------------------+

Inspect the column definition. Use DESCRIBE or query information_schema to confirm the current length or type:

DESCRIBE users;

-- or more detail:
SELECT COLUMN_NAME, COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'users' AND COLUMN_NAME = 'bio';

Widen the column if the data is legitimately longer. If the application genuinely needs to store longer strings, extend the column:
```
ALTER TABLE users MODIFY COLUMN bio VARCHAR(500);
-- or for very long text:
ALTER TABLE users MODIFY COLUMN bio TEXT;
```
Fix ENUM mismatches. If the value is valid in your domain but not in the column definition, add it to the ENUM:
```
ALTER TABLE orders MODIFY COLUMN status ENUM('pending','processing','shipped','cancelled','refunded');
```
Truncate or validate in the application before writing. For cases where the column width is intentional (e.g., a CHAR(2) country code), validate or truncate in application code rather than relying on the database.
Check your SQL mode. Confirm whether strict mode is active and understand the behaviour difference:
```
SELECT @@SESSION.sql_mode;
SELECT @@GLOBAL.sql_mode;
```
To enable strict mode for the current session (recommended for testing):
```
SET SESSION sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
```

Find existing truncated rows. If warnings were silently swallowed in the past, query for rows that appear truncated:

SELECT id, bio
FROM users
WHERE CHAR_LENGTH(bio) = 100   -- exactly at the old limit
ORDER BY updated_at DESC
LIMIT 50;

Additional Information

Related warning codes: 1264 (ER_WARN_DATA_OUT_OF_RANGE) is raised when a numeric value is out of the column's representable range. 1366 (ER_TRUNCATED_WRONG_VALUE_FOR_FIELD) is similar but covers type coercion failures such as inserting a non-numeric string into an integer column.
Strict SQL mode: MySQL 5.7.5+ enables STRICT_TRANS_TABLES by default. MySQL 8.0 also includes NO_ENGINE_SUBSTITUTION. Older applications originally developed against MySQL 5.5 or 5.6 with permissive defaults may have been silently truncating data for years and only surface this as an error after a version upgrade.
ORM behaviour: Hibernate, SQLAlchemy, ActiveRecord, and most ORMs surface this as a database-level warning/exception only if the underlying driver propagates it. Some drivers suppress warnings by default. Enabling jdbc:mysql://...?useWarnings=true (Java Connector/J) or SHOW WARNINGS after each statement in your ORM logging can surface otherwise-invisible truncation.
SHOW WARNINGS vs SHOW ERRORS: In strict mode, the message appears under SHOW ERRORS. In permissive mode it appears under SHOW WARNINGS. Both can coexist.

Frequently Asked Questions

Why does my INSERT succeed but the data looks wrong in the database? You are running in non-strict SQL mode. MySQL silently truncated the value and returned only a warning, which most clients and ORMs ignore. Enable strict mode (STRICT_TRANS_TABLES) to turn truncation into an error so these problems surface immediately rather than causing silent data loss.

How do I see which rows were affected by past silent truncation? There is no automatic audit log. You need to inspect the data — look for rows where the column value is exactly at the maximum defined length, which is a strong indicator that the original value was longer and was cut off.

Will changing the sql_mode fix existing corrupted data? No. Changing the SQL mode only affects future statements. Data that was already truncated cannot be recovered from MySQL — the lost characters are gone. You may need to restore from backup or re-import from the original source.

Does this warning affect SELECT statements? No. Warning 1265 is only generated during data-modification statements (INSERT, UPDATE, LOAD DATA). A SELECT that retrieves a value that was previously truncated will not generate this warning — it will simply return the stored (truncated) value.