MySQL's sql_mode: My Suggestions

Just the previous week, Anders mentioned the sql_mode with regard to Joomla. Past week, Shlomi's started a nice discussion pertaining to MySQL's sql_mode. He offers the suggestion to modify the installation procedure so MySQL is by default installed using a so-called "strict" sql_mode. In addition, he suggest it would be better if it would not be allowed to change the sql_mode after starting the database server.

Although I understand Shlomi's motivation in wanting this, I respectfully disagree. I have a few ideas too about the sql_mode and what should be done to make it more valuable and less dangerous to use it. In short, these are my suggestions:

• Make setting the sql_mode after the server has started a privileged action.


• Allow session settings to be defined as part of the user account so they are automatically applied as part of creating a session for the user

Read on if you want to know more about the sql_mode and my motivations for proposing these two changes.

UPDATE: Everybody that wants to automatically set a specific sql mode depending on the user that is connecting, look up this entry in the MySQL bug database:

http://bugs.mysql.com/bug.php?id=42035

Shane Bester put up a comment there that explains how to do it using mysqld's --init-connect option.

The sql_mode

What is the sql_mode, and what does "strict" really mean? Well, the sql_mode is a MySQL server variable. It's value can affect a number of aspects regarding how MySQL parses SQL, and what semantics are attached to it. Functionally, the sql_mode is best understood as a compatibility feature that allows MySQL to behave more like other RDBMS products.

The value for the sql_mode is a set, which is essentially a comma-separated list of predefined values. You can find all possible values here in the reference manual. For example, on windows, the default sql mode looks like this:

mysql> SELECT @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

You can set the value for the sql_mode using the SET syntax. You can also set its value on the mysqld command line, or in an option file.

There is quite a good bunch of atomic pre-defined values that alter or attenuate just one specific aspect of MySQL's behaviour. Some examples in this category are IGNORE_SPACE and ONLY_FULL_GROUP_BY, and all modes shown in the previous listing also fall into this category. There is another bunch of mode values that have the effect of adding a predefined collection of values to the sql_mode. Examples in this category are ANSI (which is equivalent to REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE) and POSTGRESQL.

The "strict" mode

Now what is this "strict" mode? Well, first of all it is good to realize that there is not one "strict mode". Just check the manual:

When this manual refers to “strict mode,” it means a mode where at least one of STRICT_TRANS_TABLES or STRICT_ALL_TABLES is enabled.

What do these modes do? Basically, these modes affect the rules for modifying table data. Old-style MySQL behavior was to implicitly provide defaults for missing values, and to convert out-of range values to the nearest in-range value. You would get a warning to inform you it happened, but that statement would continue. STRICT_ALL_TABLES causes the statement to be aborted in case of invalid data. For transactional tables, this will also cause the statement to be rolled back. At any rate, an error (instead of a warning) is issued in this case. The STRICT_TRANS_TABLES mode works similar to STRICT_ALL_TABLES with regard to transactional tables, but still displays the old behaviour for non-transactional tables.

Now, there is some additional complication, because even in "strict" mode, not all out of range values are automatically taken care of. MySQL supports date values that are invalid in the sense that they do not correspond to a real existing date. Most notably, there is the "zero date", 0000-00-00 00:00:00. By default, it is also allowed to have a zero field within a date, like so: 2001-01-00 (Note the zero day field. Personally I don't mind these date semantics, and I happen to find them convenient. For example, if you know the year, and you know the month, you can still enter a value that calculates like a date, even though the exact day is presently not known). These can be influenced using the NO_ZERO_DATE and NO_ZERO_IN_DATE settings for the sql_mode.

If you are looking for a way to use both a strict mode, and avoid these invalid dates as well, check out the TRADITIONAL mode. This combines the strict modes and disallows the invalid dates, and includes even a few more options to make MySQL behave more like a traditional RDBMS.

The snag with sql_mode

Personally, I think that having the sql_mode is a pretty good thing. Setting an appropriate strict sql_mode allows you to write more robust applications without losing the ability to run older applications that lack the concept and rely on the forgiving old-style MySQL behaviour. However, there are still a few snags to look out for.

You can run into real trouble when specifying a non-default sql_mode in the option file. In a prior article, I wrote that existing applications may not be prepared for this, and they might break. For this reason, the default sql_mode can be overridden on the session level, allowing each application to set exactly the sql_mode they require.

At the same time, the ability to change the sql_mode on the session level is also a source of trouble. Not only does it enable users to change the rules concerning what data can be entered into a table, it can actually lead to corruption and data loss when used in conjunction with partitioned tables. Obviously, this is a serious problem. It just blows my mind why nothing was done to prevent this.

Proposed Solution - two suggestions

Like Shlomi, I agree there is an inherent problem in the way it is now possible to change the sql_mode. I also feel it should change, but I respectfully disagree with Shlomi's suggestions. Perhaps the sql_mode as set by the windows installer (only STRICT_TRANS_TABLES, NO_AUTOCREATE_USER and NO_ENGINE_SUBSTITUTION) is doable, but something like the TRADITIONAL mode would simply break too many existing applications. Being able to set the sql_mode is tied to this matter - if the global sql_mode does not meet the requirements (either more or less strict) of a particular application, you simple need to override it at the session level. At least I don't see how it can be done otherwise.

I think the solution to all these problems is to ensure that the sql_mode is not altered willy-nilly. First of all, it is clear that some users should be able to alter the mode for application compatibility. It is also clear that there are large classes of users that should not be able to do this. Secondly, most cases that require changing the sql_mode involve running specific applications. In these cases, the change of sql_mode is very well defined, and completely confined to the application's schema.

Suggestion 1: altering the sql_mode should be a privileged action

So what I mean is that it should by default not be possible for an arbitrary user to change the sql_mode. I guess it would make sense to allow at least root and perhaps the SUPER users to do it, but otherwise, you would need to be explicitly granted the privilege, something like:

GRANT SET SESSION SQL_MODE TO user@host;

If you like this suggestion, or if you have some other thoughts concerning this matter, go to bug 42034, and add a comment.

Suggestion 2: define default session settings for the account

Many applications use a single database account. It would be extremely helpful if we could define the default session settings at the account level. These could then be applied as soon as the user establishes a connection. For example:

CREATE USER user@host IDENTIFIED BY 'password'
SESSION DEFAULTS SET 
    SQL_MODE := 'TRADITIONAL,NO_ENGINE_SUBSTITUTION,IGNORE_SPACE,PIPES_AS_CONCAT,ANSI_QUOTES'
,   AUTOCOMMIT := OFF
,   STORAGE_ENGINE := INNODB
;

In many cases, this would be enough - if the session settings are applied automatically, there will in most cases not be any need at all to change the sql_mode (or the autocommit mode, or the storage_engine). If you like this suggestion, go to bug 42035 and leave your thoughts there.