Making strict sql_mode the default

MySQL has sometimes faced criticism for being too relaxed at allowing invalid values, or inserting but truncating values that are out of range. For example:

mysql> CREATE TABLE unsigned_int (a int unsigned);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO unsigned_int (a) VALUES (-1);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> SELECT * FROM unsigned_int;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

In MySQL 5.0 two strict sql_mode options were introduced to be able to change this behaviour:

  • STRICT_ALL_TABLES - Behave more like the SQL standard and produce errors when data is out of range.
  • STRICT_TRANS_TABLES - Behave more like the SQL standard and produce errors when data is out of range, but only on transactional storage engines like InnoDB.

However, these options were disabled by default.

Changes in MySQL 5.6

MySQL 5.6 made the first important step by setting STRICT_TRANS_TABLES "by default" for new installations of MySQL. That is to say that when using one of the MySQL installation packages, the included configuration file includes the line:

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Being in a configuration file only, it has given users an easy way to be able to revert to the old behaviour if their application relies on MySQL's relaxed handling.

(Small Note: The included "default configuration file" could be /etc/my.cnf or BASEDIR/my.cnf which may end up being /usr/my.cnf).

Upcoming Changes in MySQL 5.7

In MySQL 5.7 we are planning to simplify the SQL modes, and potentially even introduce additional strictness to bring MySQL even closer to the SQL standard.

I have previously written about this topic here.

Proposed Changes in MySQL 5.x

We want to make STRICT_TRANS_TABLES the default for all installations. That is to say, whether or not you have specified an sql-mode in configuration, MySQL will reject invalid or out of range values unless you chose to set it otherwise.

However, we also realize that this is one of the changes that impact users the most, so we are soliciting feedback on how we can minimize the impact.

Do you think this change can be included in MySQL 5.7?
Should it wait until MySQL 5.8?
If you discovered that you needed to remove the SQL mode STRICT_TRANS_TABLES from your configuration file after installing MySQL 5.6, what did you think of the experience?

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.

  • http://karwin.blogspot.com Bill Karwin

    It would be very tricky to adopt strict mode (or a 'stricter' mode you may have in mind), because it will break many widely used applications.

    For example, WordPress has given up trying to fix their code to make it work with MySQL strict mode. The bug is actually closed, and the last comment from the lead developer is "WordPress just pretty simply does not support strict mode." (https://core.trac.wordpress.org/ticket/8857)

    Unless you want some major backlash, and possibly to kill any possibility of major hosting companies upgrading to 5.7, you must provide an easy way for MySQL 5.7 to revert to backward-compatible, non-strict mode.

    The choice between doing this in 5.7 or 5.8 is immaterial, in my opinion. It will be painful anytime you do it.

    I think MySQL should reach out to the dev teams of the top apps that primarily use MySQL, and provide some free consulting to help them test against strict mode and fix problems that are discovered.

    Maybe sponsor some Google Summer of Code projects?

    • http://www.tocker.ca/ Morgan Tocker

      Thanks for the feedback!

      If I can answer to one of your points specifically - hosting providers could change the defaults and disable strict mode with one line of configuration. The proposal does not remove this functionality.

      In the case of WordPress as well, if they decide they are unable to accept the new defaults, it is possible to set the sql-mode on a per-session basis. So it might be as simple as changing the DB connect code to run one quick SET statement.

      Assisting the projects is an interesting suggestion. I think the first piece of the puzzle is communicating the intended direction (with the future as the default), which to some level I am trying to start with this post. But in writing this, I am also acknowledging there will be challenges as well.

      One interesting piece pointed out to me in twitter is that using strict mode helps find bugs in application code. So there is a general willingness to upgrade to it, it's just a challenge to prepare people for it.

      • http://www.tocker.ca/ Morgan Tocker

        WordPress now has a 'disable incompatible SQL modes' feature: https://core.trac.wordpress.org/ticket/26847

        • Justin Swanhart

          How braindead to keep using broken code and just turn of the safeties. Sigh.

          • http://www.tocker.ca/ Morgan Tocker

            WordPress has backwards compatibility requirements just like MySQL :) There are third party plugins which will be difficult to upgrade.

          • Justin Swanhart

            I didn't consider plugins, as I am not a wordpress user.

  • DaniĆ«l van Eeden

    This can help to increase security. For an example:

    http://vagosec.org/2013/09/wordpress-php-object-injection/

  • http://www.xaprb.com/ Baron Schwartz

    Maybe instead of making MySQL strict and making it easy for people to get the old nasty behavior, make it simpler for them to enable strict behavior. I realize you're planning to do this. Currently my my.cnfs have this:

    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

    That's a lot of mess.

  • jobinau

    Good plan.
    implicit magics are major pain in debugging and data corruption.
    Better to get rid of that ASAP.
    not much point in holding it for Legacy systems. I am not able to understand why a software piece should depend on this kind of implicit magics anymore. what is stopping them from fixing the code.
    if Legacy wants to use latest version of MySQL, let them enable to non-strict behaviour explicitly.

  • ronaldbradford

    Maintaining forever continuing backward compatibility I believe is a poor practice when you a fixing a serious issue. The loss of data consistency, i.e. what you put in is what you get out, is a problem with many clients.

    To add my 2 cents worth. Every new install I do starts with

    sql_mode=NO_ENGINE_SUBSTITUTION,
    STRICT_ALL_TABLES,
    NO_AUTO_CREATE_USER,
    NO_ZERO_DATE,
    NO_ZERO_IN_DATE,
    ONLY_FULL_GROUP_BY

    which is a poor representation of what data integrity needs to be.

    Perhaps you should go the extra mile, and make the default a new mode called SANE (I believe from memory ANSI is already used). You can then have another one called LEGACY which is all the crap that the poor defaults are,

    Part of upgrading software is compatibility testing. Enforcing a better RDBMS standard trumps keeping poor practices alive for ever.

  • http://www.jooq.org Lukas Eder

    Is there any chance that NO_BACKSLASH_ESCAPES could make it into this strict mode as well? I personally find this flag (or rather: not setting it) a major security risk when using MySQL and any other database from an application. In almost all other databases, backslashes in string literals have no special meaning.

    For the time being, we're going to be adding such a flag to jOOQ as well, to keep users from needing to think about this...

    • http://www.tocker.ca/ Morgan Tocker

      There is some risk to merging NO_BACKSLASH_ESCAPES into STRICT mode:

      - mysqldump from all current versions uses backslashes as an escaping sequence (and explicitly sets its SQL mode to NO_AUTO_VALUE_ON_ZERO)

      - Since this disables backslash escape, importing mysqldump from earlier versions would fail :(

      An alternative proposal would be to enable NO_BACKSLASH_ESCAPES by default (which would allow mysqldump to still work). But this would still have some complications as we would need to run it by our driver community, and make sure that each connector can offer an sql-mode aware escaping function.

      A good start to the transition would be to make sure all our tools (such as mysqldump) escape this way by default so that users can get used to it.

      • http://www.jooq.org Lukas Eder

        There's probably a lot of risk in merging anything into STRICT mode :-)

        • Jess Balint

          Hey Lukas,

          You can set this with a normal statement to set the SQL mode on the session level. Connector/J provides a "sessionVariables" URL property that can also be used to set it immediately after making the connection.

          I don't think client info would be an appropriate place to expose this. The client information is intended to be to identify an individual connection with client-specific context information.

          If you have any ideas on how to make this easier, let me know.

          • http://www.jooq.org Lukas Eder

            Hi Jess,

            So far, I haven't had any ideas about how to make this any easier. I guess we'll just query either the database directly, or the Connector/J specific API as Connector/J already caches that value... Hard to say what's best, because that cache risks running stale...

      • http://www.jooq.org Lukas Eder

        Hmm, in fact, a jOOQ user made me aware of this interesting comment here:


        // Ignore sql_mode=NO_BACKSLASH_ESCAPES in current implementation.

        // Add introducer _utf8 for NATIONAL CHARACTER
        StringBuffer buf = new StringBuffer((int) (x.length() * 1.1 + 4));
        buf.append("_utf8");
        buf.append(''');
        // [...]

        Found at http://bazaar.launchpad.net/~mysql/connectorj/5.1/view/head:/src/com/mysql/jdbc/PreparedStatement.java#L4781 (revision 1370).

        • Jess Balint

          Thanks. Guess we'll have to fix that. ;) MySQL works great without NVARCHAR through.

          If you look at where that code is, you see it's in setNString() which is used (according to the spec) to convert to a different character set. Connector/J already handles this in setString() depending on the character set of the connection.

          • http://www.jooq.org Lukas Eder

            Hi Jess, thanks for the reply. It's probably not that bad, otherwise, people would've reported it more often. Few people probably really use those NCHAR / NVARCHAR types every day...

  • Simon J Mudd

    I seem to have found a gotcha in the 5.7 change which I assume was not expected by the developers. See: http://bugs.mysql.com/bug.php?id=75439

    • http://www.tocker.ca/ Morgan Tocker

      Thank you for sharing here. I am trying to think of the correct behavior here; if the replication thread were to ignore these errors (accounting for mode changes) this could incorrectly lead users to believe their statements are safe for 5.7. If they then promote the 5.7 to a master, bad things will happen.

      We will have to take a look at the bug report and decide on what action can be taken.

      • Simon J Mudd

        I think that often when new features have been added to MySQL sometimes more thought could have been taken for those people who _upgrade_ a replicated setup and thus can't afford to turn off all systems to change them. I imagine that these are possibly larger installations where in theory the people managing these servers are aware of the issues and will work around them one way or another.

        They can be rather painful though.

        Things that affect me: use of the new separate undo tablespace or indeed 5.6's separate temporary tablespace can only happen on a new server that's been built from scratch. If doing inplace upgrades these features can not be added.

        In many cases this prevents me from using these features even though they are the features I "crave" for as the provide improved performance and more flexibility.

        http://bugs.mysql.com/bug.php?id=74211 is an example of a new feature in 5.6 which I've not used yet. The feature is good but there's no safe way to enable it in 5.6 from a setup that's currently running with the default setting.

        The feedback people may provide me is: "well dump the data, reimport it and then migrate to servers cloned off this new one." The problem here is that it's hard to interrogate a server to see if it's been built one way or the other, and the required /etc/my.cnf would depend on that, so this would require very careful management of a group of servers undergoing such a change. On some systems dumping and reloading is not an option due to the size of the databases and the time this would take, but in many cases such as these new tablespace changes I could probably take a short (1 or 2 hours maximum) downtime of the server and get a program to rewrite or rearrange the tablespaces on the server to match the new intended layout. That would work but is not possible now and it would be for a much shorter period than a full dump + load.

        The sql_mode thing I've brought up is only a problem during migration, but I guess a lot of people will migrate and those migrating with STRICT_ALL_TRANS set are likely to be bitten by this. The problem is slightly different but the end result is the same. Planning for migration from version 5.X to 5.Y requires time on the part of the users doing such a migration but it seems to me that the Oracle developers involved in these new features perhaps need to talk more to the community about the impact of such a change so these new features can be adopted more easily and the cost of such adoption can be minimised where this is reasonably possible.

        So maybe it would be good to send them out into the field to get some more real life experience of the odd things that their users do with the MySQL software...