Book: "Pentaho Kettle Solutions", Matt Casters, Roland Bouman, & Jos van Dongen, Wiley 2010 Book: "Pentaho Solutions", Roland Bouman & Jos van Dongen, Wiley 2009

Sunday, January 11, 2009

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.

19 comments:

Shlomi Noach said...

Hi Roland,

suggestion #1: This is certainly an improvement over the existing situation, though I do prefer only letting SUPER change the sql_mode.

suggestion #2: Nice touch on the default storage engine and autocommit! With sql_mode: some modes do fit nicely to this kind of syntax (like PIPES_AS_CONCAT). But my issue is with two users who share the same data, yet have different modes for NO_ZERO_IN_DATE and error for division by zero.
For this reason, suggestion #2 does does not help out in maintaining data integrity, although we throw some responsibility at the 'root' user, whoc creates the accounts.

Allow me to recall two more suggestions:

suggestion #3: Set sql_mode to 'TRADITIONAL' by default. This is an almost instantaneous fix. Old applications would not work out-of-the-box, but for those apps, the sql_mode can be changed to a more relaxed value. This can nicely combine with suggestion #1.

suggestion #4: Add a new set of privileges, like:
GRANT SELECT, INSERT, UPDATE, ZERO_IN_DATE ON world.* TO 'myuser'@'myhost';
In this suggestion, it is clear from the privileges set what is allowed on the DB, tables, columns etc.

MySQL's definition of 'strict' as being 'any mode which contains either STRICT_ALL_TABLES or STRICT_TRANS_TABLES' is rather relaxed. I think when people speak of 'strict' modes it should be clear they're referring to the wide set of 'stricter than not' modes. 'TRADITIONAL', as you mentioned, is a very strict mode.

I also agree with Lukas who wrote that suggested fixes are programmer's centric instead of DBAs centric.

As a programmer, I like the idea that I can choose whatever fits my needs more. As a DBA, I feel that the database is "something you need to learn to work with", and in this respect, the DB should dictate the rules and everyone else need to adjust.

At any case, either one of the suggestions is by far better than the current situation, where anyone can change sql_mode "willy-nilly", as you say.
Regards,
Shlomi

Roland Bouman said...

Hi Shlomi!

Thanks for taking the time to reply, and for the constructive discussion. Some comments here:

"my issue is with two users who share the same data"

Well, I think my suggestion solves exactly that. If it would be implemented you can ensure they always use the same SQL_MODE by including it in their account definition (and not grant extra privileges to alter it afterwards)

"Set sql_mode to 'TRADITIONAL' by default."

This is a bad idea. Many applications will break.

"Add a new set of privileges"

I agree in the sense that logically it should be possible to store the sql_mode at the table level and have it applied automatically, quite like it works now for stored procedures, views and events. However I suspect it is hard to implement this efficiently and correctly.

"I think when people speak of 'strict' modes it should be clear they're referring to the wide set of 'stricter than not' modes"

Then use TRADITIONAL if that is what you mean ;-)

"agree with Lukas who wrote that suggested fixes are programmer's centric"

I didn't post these suggestions in your comment thread. I think Lukas was referring to my statement that "it is on the application to ensure the right mode is set". My suggestions in this post are to simplify that by allowing the appropriate sql_mode to be automatically set for all database users of the application (which will in the vast majority of cases be one or two)

"the DB should dictate the rules and everyone else need to adjust."

I just can't agree...;-) Are you saying that as a DBA, each time you install a application like Joomla, Drupal, etc. you make up the rules what the application database user can or can't do? Suppose the app wants to create a table and supply a 'zero date' as default for some column...what are you going to do, ban the application? Surely it is the apps installer that creates tables and issues the proper grants and whatnot - the app is in the best position to know what the sql_mode should allow or disallow.

Shlomi Noach said...

Hi Roland,

'This is a bad idea. Many applications will break.'
Yes, and the installer will have to manually, knowingly, switch to a more permissive mode.
Gradually applications will learn to fit themselves to a stricter mode.
I suppose I can't argument to well for that in light of the many frustrated people who will fail first installation attempt.

'Then use TRADITIONAL if that is what you mean'
'strict' is such a more fitting word...:) I'm just saying MySQL have used this word improperly. It does not mean what you would assume it means in their jargon.

'I just can't agree...;-) Are you saying that as a DBA, each time you install a application like Joomla, Drupal, etc. you make up the rules what the application database user can or can't do?'
Well, yes. The examples of Drupal, Wordpress etc. are correct. But what about "home made" databases? I have a personal experience in setting up a database, back at the time I wasn't aware of sql_mode, which is based on an empty mode. I do have issues with it today. I wish I knew about sql_mode back then. My application would be better. I wish I could rebuild it; I do not dare now to make far going changes.
I believe a good DBA can and should tell the programmer what his limitations are. As an example, the best sys admins I've met never let me do as I please with the operating system. I always had to "beg" for permissions etc. The result was a better code, a more organized OS, better support for maintainance, deployment.

When consulting companies, I'm sometimes expected to work with the DBA and "solve the problems with the database". I make it up front that I may eventually work with the developer and "solve the problems with the application".

I've been thinking about suggestion #1. I like it, but would further add something like this:

GRANT SET SESSION SQL_MODE (PIPES_AS_CONCAT, NO_ZERO_IN_DATE) TO user@host;

In this suggestion you specify which sql_modes the user is allowed to set or unset.

Regards,
Shlomi

Roland Bouman said...

Hi Shlomi!

thanks again for the reply. Sometimes discussion may look edgy from the outside but I just want to let you know I appreciate exchange of views like this a lot.

As for having a more strict sql_mode by default, I think we should agree to disagree. I think both your and my opinion have merit, albeit for contradicting purposes.

But take one minute to think of your home-grown app. You say yourself you are reluctant to change the SQL_MODE now. I don't know if your app is released to the public, but lets assume you have thousands, or tens of thousands of users. Or even better: assume your app is packaged and used as part of another application. Now suppose that Debian starts packaging MySQL with a TRADITIONAL sql_mode by default, and that it breaks your application (and I trust, many more too). Suddenly, many people suffer loss of service because of it. What is the hotfix you are going to recommend? Don't use Debian's package? Hack my.cnf to restore the global sql_mode? Patch your app real quick to reset the session sql_mode? It's damned if you do and damned if you don't.

As for strict vs. TRADITIONAL: this seems like a semantic battle. Maybe MySQL publicity around 'strict mode' has unnecessarily muddied the waters. Be that as it may, if you want to avoid confusion, i still think it is best to go back to the source, which I strongly feel is the MySQL reference manual in this case.

Maybe it is true what you say, and most people attach the same meaning to "strict" mode as you do, then again - maybe not. There just is no way of telling. So I feel it is more practical to stick to documented terminology.

As for your suggestion to create separate privileges for each sql_mode: well, it would do the job too. Personally, I think it would add more complexity than I'd like, but maybe that is just me. Please add a comment to the bug I reported, and add your point of view. Who knows, maybe this will be picked up and fixed by MySQL.


kind regards, and thanks again,

Roland

Shlomi Noach said...

Hi Roland,

'Sometimes discussion may look edgy from the outside...'
Never crossed my mind! I enjoy and appreciate your comments and critics on my blog posts. Your position is always very professional; I hope my writing does not appear to be negative or anything. If it does, it's only as English is not my native language.

'What is the hotfix you are going to recommend? '
Like I said, it's an issue. In this case, your recommendation #2 is an appropriate solution. And so are the changes you made in the Wordpress php scripts, setting the SQL_MODE upon connection creation.

Regards,
Shlomi

Shlomi Noach said...

Call for support for suggestion #4.

Kindly review and comment on:
http://bugs.mysql.com/bug.php?id=42124

Thanks,
Shlomi

Log Buffer said...

Roland Bouman follows up on last week’s post from Schlomi, with his suggestions on MySQL’s sql_mode. For those of us not familiar with the controversy, Roland kindly outlines it, and then offers his opinions on the right way forward.

-- Log Buffer #131

backup solution said...

I think that it is an improvement but I am still waiting for some more improvements.

Roland Bouman said...

Hi backup solution,

perhaps you can open the bug report I filed and explain exactly what kind of improvements you do like to see.

TIA,

Roland

Anonymous said...

I'm trying to figure out how to set the sql_mode on a per user basis for mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i686) using readline 5.0. The CREATE USER syntax suggested doesn't seem to work and when I put sql-mode="TRADITIONAL" in .my.cnf for my personal account it is not set, but my password is so I know the file is being read. I've put it under [client] and [mysqld]. My goal is to have the sql_mode set differently for different php applications with different users. I'm having no luck finding a solution so might you answer this here?

Roland Bouman said...

Hi anonymous,

"I'm trying to figure out how to set the sql_mode on a per user basis "

That's the point of my post...you can't although I'd like to..

"when I put sql-mode="TRADITIONAL" in .my.cnf for my personal account it is not set, but my password is so I know the file is being read."

I don't think this works for the client. Anyway - if it's that important I would not trust the client to read the option file.

"My goal is to have the sql_mode set differently for different php applications with different users."

I understand and this is also what I would recommend. My suggestion is to ensure you explicitly set the exact mode you want directly after connecting. With only one statement you can set your entire environment:

SET session.sql_mode := 'TRADITIONAL'
, autocommit := OFF
, group_concat_max_len := @@max_allowed_packet
;

You need to do this only once, directly after connect.

Alternatively you could use the init-connect or perhaps more appropriate, init-file options to ensure the logic is applied for each user regardless of the application.

Perhaps I should blog that.

Anonymous said...

Hi Roland,

Thanks for replying. I have been able to set the sql_mode from the MySQL command line using syntax such as:

set @@session.sql_mode='traditional';
set @@global.sql_mode='traditional';

and in /etc/my.cnf globally

[mysqld]
sql-mode='TRADITIONAL'

but I want it to be set for a php user for a number of php applications that share a single connection file. I have not figured out how to do it from php where I am limited to the mysql_ commands and do not have mysqli_, PDO, etc.

BTW.... you're suggested syntax doesn't seem to work for me on the mysql command line. I shortened it to:

SET session.sql_mode := 'TRADITIONAL';

mysql> SET session.sql_mode := 'TRADITIONAL';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sql_mode := 'TRADITIONAL'' at line 1

I get a similar error when I paste in the whole command as you have posted it.

Doug Coombs
Los Alamos National Laboratory, USA

Roland Bouman said...

mmm, too bad...it turns out you cannot change the sql_mode permanently from inside a procedure...so I'm afraid my init_connect idea does not work.

Roland Bouman said...

Hi!

"I have not figured out how to do it from php where I am limited to the mysql_ commands"

what about:

mysql_query("SET SESSION sql_mode = 'TRADITIONAL'");

"BTW.... you're suggested syntax doesn't seem to work for me on the mysql command line."

bummer, i always get that wrong...it is either

SET @@session.foo := bar

or

SET SESSION foo := bar

Michel SALAIS said...

I think that "strict mode" should be associated to column/table/sdhema and should not be treated by SQL_MODE. The privilege permitting to modify it could be more or less restricted as desired then.

Simon Mudd said...

Where I work we're going through the same issue of making the database stricter in what it accepts. SQL_MODE is good for this but the developers are complaining. It's certainly pointless to have a sql_mode that everyone can change as any improved "strictness" can not be enforced if the users can choose the setting they want. So I support the 2 new features.

The comments that the change to sql_mode = 'TRADITIONAL' or similar will break the database or the application just show that it was designed with many features which are probably rather questionable. They may be fine for a small database but as you grow the default "laxness" is far from ideal.

Roland Bouman said...

"It's certainly pointless to have a sql_mode that everyone can change as any improved "strictness" can not be enforced if the users can choose the setting they want."

This is what I don't get. Sure, if you have a malicious application, it won't do you any good. But the point is, if you're developing an application, and you are only trying to protect yourself from messing up, this is simply a non-issue: you commit yourself to a particular strictnesss,set it at connect and that is the end of it. It is a wee effort for the app developers.

I understand your argument about breaking apps by setting the sql_mode, the unfortunate truth is however that this is the case for not just many, but the majority of popular MySQL based applications. For most people, it won't do to say: "ok, so I'll just go without Wordpress. Its great blogging software but it just doens't meet my db strictness".

Simon Mudd said...

My point is it should be the DBA that decides, not the developers/users. The DBAs "own" the boxes, we have to "manage" them, fix them, ... So if we decide that the applications need to use "TRADITIONAL" then they SHOULD comply, and SHOULD NOT be able to work around this.

So our issue is that for in house usage we want to control this. The development change to apply this new sql_mode requires work by several people and as such generates some resistance. I'm not able to avoid the developers working round my new "strict" mode, nor am I easily able to even see if they are "misbehaving".

In reality things are really as bad as this, but they could be.

Roland Bouman said...

Hi Simon,

sorry for misunderstanding, and sorry if I sounded a bit cranky.

I agree completely - setting the sql_mode should be a privileged action.

thanks for adding your insights.

kind regards,

Roland