Posted on by morgo

Proposal to simplify SQL_MODE options

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 ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE and NO_ZERO_IN_DATE.
  • Have these behaviours be enabled when turning on the strict mode (STRICT_TRANS_TABLES for transactional tables, STRICT_ALL_TABLES for 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:

ERROR_FOR_DIVISION_BY_ZERO
Produce an error in strict mode (otherwise a warning) when a division by zero (or MOD(X,0)) occurs during an INSERT or 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 NULL.

For SELECT, division by zero returns NULL. Enabling this mode causes a warning to be generated as well.
NO_ZERO_DATE
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.
NO_ZERO_IN_DATE
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-00-01' and '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_MODE usability 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 SQL_MODE usability?

We value your input. Please leave a comment, or get in touch.