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.

Published by

morgo

I joined MySQL AB in 2006, left, and am now back at Oracle working on the MySQL team. I’ve also worked at Percona and InPowered.

  • Kristian Köhntopp

    When I was still working at Booking.com, we had one DBA turning on strict mode and other flags to make MySQL behave more like other SQL databases.

    This found two places where Booking.com was using erroneous SQL that worked by accident, and created a long, long list of breakage due to reliance on certain date behaviour, one bug in the ONLY_FULL_GROUP_BY implementation and many other smaller breakages at the level of annoyances.

    We reverted to SQL_MODE=”” again on most databases, because SQL_MODE has many problems when introduced into an existing project.

    That problem set is amplified by the fact that it is possible to set SQL_MODE per connection, and depending on how you configure the applications that are accessing your database, some may or may not be using SQL_MODE or different SQL_MODE settings. Also, code in the database (that includes VIEWS, stored routines, and triggers) persist the SQL_MODE that was enabled at the time of their definition and run under that SQL_MODE and not the SQL_MODE of the connection or the SQL_MODE that the application designers expect.

    The usability problems that SQL_MODE has do not come from the amount of flags or their definition.

    The usability problems that SQL_MODE has come from the fact that it is not defined by schema or instance, but by connection and that different artefacts inside the server may have different SQL_MODE settings.

    Also, code pathes that actually have a non-empty SQL_MODE are traditionally exposed to less usage and testing and hence have a higher rate of errors per loc.

    You will to tackle these problems and not the flags in order to make progress.

    • I was just discussing strategies to retrofit sql-mode compliance with someone yesterday. The suggestion they had, was to use their feature flag system to tag a percentage of users to enable strict mode on for their session. This doesn’t work for background jobs, but it helps get one step closer.

      One of the problems with keeping the current defaults, is that we trap a whole new generation of applications into making the same mistakes. I have no problem with legacy apps disabling strict mode.