Bug #70796 Error messages and warnings for sql-mode behaviours need more verbosity
Submitted: 1 Nov 2013 16:06 Modified: 1 Jan 2014 10:22
Reporter: Morgan Tocker Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Errors Severity:S4 (Feature request)
Version:5.7+ OS:Any
Assigned to: CPU Architecture:Any

[1 Nov 2013 16:06] Morgan Tocker
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.
[16 Jan 2014 23:24] Morgan Tocker
Duplicate of BUG #71402.