Sql Strict Mode

Issue Type

Operational

Problem

You are not running MySQL in strict mode (without sql_mode including STRICT_ALL_TABLES or STRICT_TRANS_TABLES).

It's recommend that sql_mode is set to strict avoid data truncation, bad charset conversion, weird groupings or other weird issues and have it throw errors on insertion.

Remediation

Be aware that sql_mode is a union of different values, so don't just set it to STRICT_ALL_TABLES or STRICT_TRANS_TABLES. Check it's current value and adjust accordingly.

To see the current value, use:

 SELECT @@sql_mode AS sql_mode;
+---------------------------------------------------------------------------------------------------+
| sql_mode                                                                                          |
+---------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------------+

You can either change the variable at runtime

SET GLOBAL sql_mode='STRICT_ALL_TABLES,...'

Or you can change the variable at startup by modifying your my.cnf configuration

[mysqld]
sql_mode=STRICT_ALL_TABLES,...

External Resources

• SQL Mode
• SQL Strict Mode