MySQL has sometimes faced criticism for being too relaxed at allowing invalid values, or inserting but truncating values that are out of range. For example:
mysql> CREATE TABLE unsigned_int (a int unsigned); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO unsigned_int (a) VALUES (-1); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SELECT * FROM unsigned_int; +------+ | a | +------+ | 0 | +------+ 1 row in set (0.00 sec)
In MySQL 5.0 two strict sql_mode options were introduced to be able to change this behaviour:
STRICT_ALL_TABLES– Behave more like the SQL standard and produce errors when data is out of range.STRICT_TRANS_TABLES– Behave more like the SQL standard and produce errors when data is out of range, but only on transactional storage engines like InnoDB.
However, these options were disabled by default.
Changes in MySQL 5.6
MySQL 5.6 made the first important step by setting STRICT_TRANS_TABLES “by default” for new installations of MySQL. That is to say that when using one of the MySQL installation packages, the included configuration file includes the line:
# Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Being in a configuration file only, it has given users an easy way to be able to revert to the old behaviour if their application relies on MySQL’s relaxed handling.
(Small Note: The included “default configuration file” could be /etc/my.cnf or BASEDIR/my.cnf which may end up being /usr/my.cnf).
Upcoming Changes in MySQL 5.7
In MySQL 5.7 we are planning to simplify the SQL modes, and potentially even introduce additional strictness to bring MySQL even closer to the SQL standard.
I have previously written about this topic here.
Proposed Changes in MySQL 5.x
We want to make STRICT_TRANS_TABLES the default for all installations. That is to say, whether or not you have specified an sql-mode in configuration, MySQL will reject invalid or out of range values unless you chose to set it otherwise.
However, we also realize that this is one of the changes that impact users the most, so we are soliciting feedback on how we can minimize the impact.
Do you think this change can be included in MySQL 5.7?
Should it wait until MySQL 5.8?
If you discovered that you needed to remove the SQL mode STRICT_TRANS_TABLES from your configuration file after installing MySQL 5.6, what did you think of the experience?
Please leave a comment or get in touch!