Incorrect integer value for column
Error 1366 (SQLSTATE HY000) is raised when a non-numeric string is inserted into an integer column. In strict mode (the default), this is an error. In non-strict mode it is a warning and the value is silently truncated to the numeric prefix of the string (e.g., 'abc' becomes 0, '42abc' becomes 42).
- 1Inserting a non-numeric string (e.g., 'N/A', 'unknown', '') into an INT column
- 2Binding a string parameter to an integer column in a parameterised query without type conversion
- 3A CSV import where some fields contain text placeholders instead of numeric values
A string value is inserted into an INT column in strict mode.
SET sql_mode = 'STRICT_TRANS_TABLES'; CREATE TABLE products (id INT PRIMARY KEY, quantity INT NOT NULL); INSERT INTO products VALUES (1, 'unknown'); -- not a valid integer
expected output
ERROR 1366 (HY000): Incorrect integer value: 'unknown' for column 'quantity' at row 1
Fix
Validate and cast input before inserting
WHEN When data from external sources may contain non-numeric values.
-- Validate in application layer, or use CASE in SQL:
INSERT INTO products VALUES (
1,
CASE WHEN '42' REGEXP '^[0-9]+#x27; THEN CAST('42' AS UNSIGNED) ELSE 0 END
);Why this works
Application-side validation rejects non-numeric values before they reach the database, providing a clear error message. If validation must happen in SQL, CAST + CASE provides type-safe conversion.
✕ Disable strict mode to let non-numeric strings silently become 0
Silent truncation means 'unknown', '', 'N/A' all become 0, which is likely to be a valid business value. The data quality problem becomes invisible.
Strict mode default means this is an error in modern MariaDB. MySQL 5.6 and earlier defaulted to non-strict, silently converting strings.
MariaDB Server error code 1366 / ER_TRUNCATED_WRONG_VALUE_FOR_FIELD
MariaDB CAST function ↗Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev