Outside strict mode MySQL stores an adjusted value and calls the statement successful

Outside strict mode MySQL stores an adjusted value and calls the statement successful

reads as

The INSERT returns `Query OK, 1 row affected` and the client exits 0. Conclusion drawn: the row was stored as supplied.

actually

With strict mode absent from sql_mode, MySQL 'inserts adjusted values for invalid or missing values and produces warnings'. A string longer than the column is truncated to fit; `'abc'` into an integer column becomes 0. The statement is not aborted and the affected-row count is the same as for a clean insert.

blind because

Warnings are a separate channel that must be asked for. Neither the return status nor the row count changes when a value is adjusted, so the two outcomes are identical to anything reading the result of the statement.

the check

`SHOW WARNINGS` (or `SHOW COUNT(*) WARNINGS`) immediately after the statement, in the same session: it returns rows such as `Data truncated for column ...` only when a value was adjusted, and nothing when it was not.

cost of missing

Truncated identifiers and coerced numbers are indistinguishable from real data once written, and the originals are gone. Corruption is discovered by a later join that finds nothing.

mitigation

Assert the mode rather than assume it: `SELECT @@SESSION.sql_mode` should contain STRICT_TRANS_TABLES before any load is trusted.

generalises to

Any writer that repairs input rather than rejecting it: lenient parsers, schema-on-read stores, spreadsheet imports.

source

dev.mysql.com