1366WARNINGTier 1 — Safe✅ HIGH confidence

Incorrect integer value for column

Category: Data TruncationVersions: All MariaDB / MySQL versions

What this means

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).

Why it happens

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

How to reproduce

A string value is inserted into an INT column in strict mode.

trigger — this will ERROR

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

ERROR 1366 (HY000): Incorrect integer value: 'unknown' for column 'quantity' at row 1

Fix 1: Validate and cast input before inserting

When data from external sources may contain non-numeric values.

fix

-- Validate in application layer, or use CASE in SQL:
INSERT INTO products VALUES (
  1,
  CASE WHEN '42' REGEXP '^[0-9]+
    
  

 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.

What not to do

✗

Disable strict mode to let non-numeric strings silently become 0

Why it's wrong: Silent truncation means 'unknown', '', 'N/A' all become 0, which is likely to be a valid business value. The data quality problem becomes invisible.

Version notes

MariaDB 10.2+Strict mode default means this is an error in modern MariaDB. MySQL 5.6 and earlier defaulted to non-strict, silently converting strings.

Sources

📚 Official docs: https://mariadb.com/kb/en/sql-mode/

🔧 Source ref: MariaDB Server error code 1366 / ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

📖 Further reading: MariaDB CAST function

Confidence assessment

✅ HIGH confidence

Stable. Strict mode behaviour is well-documented.