How to Fix MySQL Error 1366: Incorrect Integer Value for Column

ERROR 1366 (HY000): Incorrect integer value: '<value>' for column '<column>' at row <n> is raised when MySQL cannot coerce a given value into the target column's data type and strict SQL mode is active. The error symbol is ER_TRUNCATED_WRONG_VALUE_FOR_FIELD.

Impact

In strict mode (STRICT_TRANS_TABLES or STRICT_ALL_TABLES), MySQL rejects the statement outright and rolls back any changes made by that row. The client receives error 1366 and the insert or update does not persist.

Without strict mode, MySQL silently truncates or coerces the bad value — an empty string or a non-numeric string inserted into an INT column becomes 0, a floating-point string like '3.7' inserted into a TINYINT column is silently rounded to 4, and so on. This permissive behaviour is a common source of silent data corruption that only surfaces later when strict mode is enabled, often during a MySQL upgrade or environment change. ORMs such as Django ORM, ActiveRecord, and SQLAlchemy will surface the error as a database exception and roll back the current transaction.

Common Causes

Inserting an empty string into a numeric column. A form or API that submits '' (an empty string) for a numeric field is the single most common trigger. INSERT INTO orders (quantity) VALUES ('') fails in strict mode.
Inserting a non-numeric string into a numeric column. Values like 'N/A', 'unknown', '-', or 'null' (as a string literal rather than NULL) cannot be cast to an integer.
Incorrect column type in the schema. A column intended to store integers was defined as VARCHAR, or vice versa — the application sends the right value but the schema does not match.
Enabling strict mode on an existing permissive database. Upgrading MySQL from 5.6 to 5.7+ (where STRICT_TRANS_TABLES is part of the default sql_mode) or explicitly adding strict mode to an older installation will surface inserts that previously silently coerced bad values.
Locale or locale-formatted numbers. Strings such as '1,234' or '1.234,56' (using locale-specific separators) cannot be parsed as integers even though they look numeric to a human.
ORM sending empty string instead of NULL. Some ORMs send '' when a nullable integer field is left blank, rather than NULL. This is valid without strict mode but fails with it.

Troubleshooting and Resolution Steps

Identify the current SQL mode.
```
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
```
If the output includes STRICT_TRANS_TABLES or STRICT_ALL_TABLES, the server is in strict mode and will reject bad values.
Reproduce and inspect the offending value. Check your application logs or enable the general query log temporarily to capture the exact statement:
```
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';
```
Look for the value MySQL reports in the error message — it tells you exactly what string it could not coerce.
Fix the application layer (preferred fix). Validate or sanitize inputs before sending them to MySQL. Convert empty strings to NULL for nullable columns, or to 0 for columns that require a default:
```
# Python example
quantity = int(form_value) if form_value.strip() else None
```
Use NULL for missing numeric values in your schema. If a column is legitimately optional, declare it nullable and send NULL rather than an empty string:
```
ALTER TABLE orders MODIFY COLUMN quantity INT NULL;
```
Then ensure your application sends NULL (not '') when the value is absent.
Use NULLIF to handle empty strings at the SQL level. If you cannot change the application immediately, use NULLIF to convert empty strings to NULL inside the query:
```
INSERT INTO orders (quantity) VALUES (NULLIF('', ''));
-- stores NULL instead of raising error 1366
```
Audit existing data before enabling strict mode. Before switching a permissive database to strict mode, find rows that would fail:
```
-- Find rows in a VARCHAR column that cannot be cast to INT
SELECT id, quantity_col
FROM orders
WHERE quantity_col REGEXP '[^0-9]' OR quantity_col = '';
```
Clean these rows first, then enable strict mode.

Check the column definition. Confirm the column is the type you expect:

DESCRIBE orders;
-- or
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders';

Additional Information

Related error codes: Error 1292 (ER_TRUNCATED_WRONG_VALUE) is raised for date/time fields under similar conditions. Error 1265 (WARN_DATA_TRUNCATED) is the warning-level counterpart emitted in permissive mode.
Strict mode was made default in MySQL 5.7. If your application ran correctly on MySQL 5.6 and breaks on 5.7+, the root cause is almost always previously-silenced coercions becoming hard errors.
STRICT_TRANS_TABLES vs STRICT_ALL_TABLES: STRICT_TRANS_TABLES only enforces strictness for transactional storage engines (InnoDB). STRICT_ALL_TABLES enforces it for all engines including MyISAM.
Temporarily disabling strict mode is a workaround, not a fix. Permissive mode lets corrupted data accumulate silently, making later migrations and audits harder.
JDBC and connection string: Java applications can set sessionVariables=sql_mode='' in the JDBC URL as a temporary workaround, but this should not be left in production.

Frequently Asked Questions

Why did this error only appear after upgrading MySQL? MySQL 5.7 changed the default sql_mode to include STRICT_TRANS_TABLES, which turns previously silent coercions into hard errors. Data that was silently stored as 0 before the upgrade is now rejected. Audit the affected columns for bad data and fix the application inputs.

What does MySQL store if I disable strict mode? Without strict mode, MySQL inserts the closest valid value and emits a warning instead of an error. A non-numeric string like 'abc' inserted into an INT column becomes 0. A float string '3.9' inserted into a TINYINT becomes 4. You can see the warnings with SHOW WARNINGS immediately after the statement.

My column is nullable — why is the empty string still rejected? NULL and '' (empty string) are different values. A nullable INT column accepts NULL but still rejects '' in strict mode, because an empty string is not a valid integer representation. Send NULL explicitly from your application or use NULLIF(value, '') in the query.

Can I fix this at the ORM level without changing SQL mode? Yes. Most ORMs let you define a custom validator or type coercer. In Django, use blank=True, null=True together (not just blank=True) for nullable integer fields. In ActiveRecord, use before_validation to convert blank strings to nil. This is the correct long-term fix — adjusting SQL mode papers over the root cause.