In the MySQL team, we’ve observed that having a large number of possible SQL modes creates confusion amongst users. For example, if you follow my advice on ‘recommended defaults for new applications’, it is:
sql-mode="STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION, NO_ZERO_DATE, NO_ZERO_IN_DATE, ONLY_FULL_GROUP_BY"
This is quite specific, and may be too much to expect of a novice user to set in configuration.
One proposal we are putting forward to address this is to simply reduce the number of options that are available. Namely:
- Remove the options
- Have these behaviours be enabled when turning on the strict mode (
STRICT_TRANS_TABLESfor transactional tables,
STRICT_ALL_TABLESfor all tables).
Which would then bring the list down to:
sql-mode="STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION, ONLY_FULL_GROUP_BY"
This has some compatibility consequences of course. To understand the change a little more, let me explain what these options do:
Produce an error in strict mode (otherwise a warning) when a division by zero (or
MOD(X,0)) occurs during an
UPDATE. If this mode is not enabled, MySQL instead returns
NULL for divisions by zero. For
INSERT IGNORE or
UPDATE IGNORE, MySQL generates a warning for divisions by zero, but the result of the operation is
SELECT, division by zero returns
NULL. Enabling this mode causes a warning to be generated as well.
In strict mode, do not permit
'0000-00-00' as a valid date. You can still insert zero dates with the
IGNORE option. When not in strict mode, the date is accepted but a warning is generated.
In strict mode, do not accept dates where the year part is nonzero but the month or day part is 0 (for example,
'0000-00-00' is legal but
'2010-01-00' are not). If used with the
IGNORE option, MySQL inserts a
'0000-00-00' date for any such date. When not in strict mode, the date is accepted but a warning is generated.
So in a sense, we are proposing that strict mode will become stricter and that each of these individual behaviours will no longer have their own on/off switches.
What do you think –
- Do you agree that
SQL_MODEusability is an issue?
- Would this change cause you any compatibility issues? i.e. We would be interested to hear a use-case where you need to enable these options individually.
- Do you have any other suggestions on how to improve
We value your input. Please leave a comment, or get in touch.