2010-11-18

Which SQL Mode Should I Use with MySQL?

Dealing with sql_mode can be a tricky business, and many people have blogged about it before (here and here). My intent is not to explain the details of what each option means (since it's covered in the manual), but rather to answer this simple and common question:
Which SQL mode should I use with MySQL? / What is the recommended SQL mode for MySQL?

My general purpose recommendation is this one:
sql-mode="TRADITIONAL,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY"
This "traditional" setting includes most of what you'd want your database to behave like, namely:
STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER
This has a nice benefit of returning an error instead of a warning on truncating a varchar column, not let you inserts completely broken date values, and give out division by zero errors. That's pretty traditional as I see it.

The other two settings make sure you don't create InnoDB tables as MyISAM by mistake, and that you include all the columns in the GROUP BY clause, like in other databases.

That's my choice for a reasonable all-around setting - for different applications you might need different ones (see excellent comment by Roland Bouman below).
Labels:

6 comments:

  1. Rob11/18/10, 4:50 AM

    ONLY_FULL_GROUP_BY has issues. When it was discussed by drizzle dev it was decided after a lot of discussion to drop it.

    Interesting reading: http://bugs.mysql.com/bug.php?id=8510

    ReplyDelete
  2. shlomoid11/18/10, 5:01 AM

    Very interesting!
    That bug report you attached is troubling to say the least...

    Not specifying fields in the group by clause usually means you forgot to include a SUM/MAX/etc operator, or some other similar mistake.

    So it's either accept common mistakes or to prevent some legitimate queries from working? Tough choice.

    ReplyDelete
  3. Rob11/18/10, 6:07 AM

    ...

    Most commercial databases do not get GROUP BY checking right. More good reading:
    http://rpbouman.blogspot.com/2007/05/debunking-group-by-myths.html

    Postgres 9.1, which will not be released for around a year, has a patch in trunk for better checking of functional dependency. See http://www.depesz.com/index.php/2010/08/08/waiting-for-9-1-recognize-functional-dependency-on-primary-keys/

    Best,

    Rob Wultsch

    ReplyDelete
  4. rpbouman11/18/10, 2:01 PM

    Hi!

    To answer the question, one should consider what applications will run on the MySQL instance.

    Just about any popular blog/CMS/project management/etc PHP application that supports MySQL typically assumes defaults. They get terribly confused or just break apart if you start requiring ANSI_QUOTES, or ALLOW_INVALID_DATES or ERROR_FOR_DIVISION_BY_ZERO etc. So in these cases, I recommend to just use the default-out-of-the-box sql_mode if you want to keep your life simple and enjoy some leisure time.

    When developing your own applications, things are different. I typically try to get MySQL to behave as close to standard SQL as possible, so I typically have:

    STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES,NO_AUTO_CREATE_USER,ERROR_FOR_DIVISION_BY_ZERO

    ..and I set this at the application level as part of the code that establishes the connection. That way, I can keep the default server setting for SQL mode and still install apps that don't set their own sql_mode.

    The setting I just proposed still has some problems: with NO_ZERO_DATE and NO_ZERO_IN_DATE you'll run into issues when defining TIMESTAMP columns (might overcome this by specifying column defaults).

    Other than that, some people are very attached to MySQL's non-standard double quoted strings, and backticks for quoted identifiers. I hate them, so I have ANSI_QUOTES enabled.

    NO_BACKSLASH_ESCAPES is kind of the same thing as ANSI_QUOTES. If you're fond of the MySQL-is \ escape, chances are you like the double quote for strings too. I do not like it at all, so I have NO_BACKSLASH_ESCAPES. This does however require special care when making backups with mysqldump: regardless of the actual setting of NO_BACKSLASH_ESCAPES, it must be the same when restoring the dump or you'll run into big trouble.

    I also have PIPES_AS_CONCAT. I admit it doesn't help me much because the overwhelming majority of MySQL code uses CONCAT. I gave in to that habit as well, for me it's just not worth it to resist and stubbornly use the standard || for string concatenation. But if you'd want to, you could :) I do however very much resent using || instead of simply OR. Fortunately almost nobody uses it.

    I am on the fence about ERROR_FOR_DIVISION_BY_ZERO. MySQL's default (and I think, non-standard) behaviour of returning NULL in a divide by zero case doesn't seem unreasonable to me, and in my opinion, the NULL works quite the way I like it to. That said, I don't actually run into divide by zero cases so it's kind of theoretical for me whether this is better.

    This leaves us with a set of mode constants that, in my opinion, are simply always good idea. These are:

    STRICT_ALL_TABLES
    IGNORE_SPACE
    NO_ENGINE_SUBSTITUTION
    NO_AUTO_CREATE_USER

    I just can't think of any reason why anyone wouldn't want this, and I have no hesitation to recommend this as a minimal sql_mode setting.

    ReplyDelete
  5. shlomoid11/23/10, 7:09 AM

    @Roland
    Thanks for the excellent comment! It's bigger than the blog post itself :)

    ReplyDelete
  6. rpbouman11/24/10, 8:38 AM

    heh :) that's because I can't keep my mouth shut :(

    ReplyDelete

Subscribe to: Post Comments (Atom)