Description:
Via: http://www.tocker.ca/2013/11/01/proposal-to-simplify-sql-modes.html
"When an error is thrown due to a setting in SQL_MODE, it would be a big help for all involved iif the error message could indicate it can be configured in SQL_MODE. If there were modes based on vendor databases such as MSSQL, DB2 (Or if there were a mode based on ANSI99), it would also be a big improvement (if that's even possible).
Otherwise for a novice, having fewer options _does_ help those who don't type, or who are in a hurry to get things going."
How to repeat:
mysql [localhost] {msandbox} (test) > CREATE TABLE a (a int unsigned);
Query OK, 0 rows affected (0.04 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO a (a) VALUES (-1);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql [localhost] {msandbox} (test) > SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > set sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.01 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO a (a) VALUES (-1);
ERROR 1264 (22003): Out of range value for column 'a' at row 1
Suggested fix:
The error message should be something closer to:
Out of range value for column 'a' at row 1. This behaviour is influenced by the sql_mode setting. When the sql_mode includes STRICT_TRANS_TABLES or STRICT_ALL_TABLES it may produce an error, otherwise it will produce a warning.