Description:
In MySQL Server version 5.7.5, WL#7764 added STRICT_TRANS_TABLES to the default sql_mode value.
The manual section at http://dev.mysql.com/doc/refman/5.7/en/ignore-strict-comparison.html does not reflect this. It says:
"Default (no IGNORE, no strict SQL mode)"
and
"An example of when the default is to produce a warning is inserting a value of the wrong data type into a column (such as inserting the string 'abc' into an integer column)."
For example, inserting a wrong data type into a column no longer produces a warning by default. When Strict mode is enabled (default as of 5.7.5), this will produce an error, unless IGNORE is used.
How to repeat:
Read http://dev.mysql.com/doc/refman/5.7/en/ignore-strict-comparison.html and compare with default behavior of the server, e.g.:
mysql> CREATE TABLE mytable (col_datetime DATETIME);
Query OK, 0 rows affected (0.66 sec)
mysql> SELECT @@sql_mode;
+---------------------------------------------------------------+
| @@sql_mode |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO mytable VALUES ('2003');
ERROR 1292 (22007): Incorrect datetime value: '2003' for column 'col_datetime' at row 1
mysql> SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES',''));
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @@sql_mode;
+-------------------------------------------+
| @@sql_mode |
+-------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO mytable VALUES ('2003');
Query OK, 1 row affected, 1 warning (0.12 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1265 | Data truncated for column 'col_datetime' at row 1 |
+---------+------+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mytable;
+---------------------+
| col_datetime |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)
Suggested fix:
Redesign the table and corresponding text, or use different terms than "Default" for "no IGNORE, no strict SQL mode".
