Suggestions for transitioning into Strict SQL-Mode

As MySQL 5.6 now enables the SQL Mode STRICT_TRANS_TABLES for new installations, I have been running into users who have been upgrading from previous versions, and not quite sure how to test their application for compatibility with this new default.

For some applications converting warnings to errors presents an unknown-unknown, in that the operators of the system can not easily detect what may break. Certainly many deployments will have QA environments, but there is always some fear production queries are just a little bit different.

Since sql_mode is configurable on a per-session basis, there are some strategies that I have been recommending to ease transition:

  • Whitelist: Have all new application components enable strict mode by default. For example, if you are building a set of cron jobs to rebuild caches of data – have these scripts set sql-mode strict as soon as they connect to MySQL, but initially leave existing applications without STRICT mode.
  • Blacklist: Modify existing application components that have not extensively been tested with strict mode to explicitly unset this SQL mode when connecting to MySQL. This is an important change, since all new components will then default to being strict.
  • Staged Rollout: Have the ability to turn on/off strict SQL mode on a per user-basis, with perhaps internal users or beta users being the first to have strict mode enabled for. This offers a more gradual transition where you can contain any errors to within a small number of users. This strategy was suggested by @geodbz

That’s my list to date. I would love to hear if anyone has any suggestions on how to better manage the transition process!

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.

  • There’s a Percona Toolkit tool to help: pt-upgrade. This tool can be used in a QA environment, to replay a log of queries against multiple MySQL instances, for example a 5.5 and a 5.6 instance. It compares the responses from both instances, and reports if there are any differences in the result sets (that would be bad), in performance, or (here’s the point) any differences in errors or warnings.

    • Thanks Bill. I think this helps, but the difficult thing to know is how the application responds when it gets these errors (or warnings).

      On a similar note: I would love to have a development feature to randomly generate fake deadlocks and other assorted errors.

    • This is definitely helpful. My fear would still be a lack of 100% test coverage in QA and that thing that isn’t on anyone’s list blowing up in production.

      We’ll probably ease our way into this for new apps. Slowly.

      • Right — testing shows the presence of bugs, not their absence.

  • Simon J Mudd

    No mention of masters and slaves running different major versions which means that behavioural changes may not be noticed until you move to the new master. Also you need to be aware that at least in this case the sql_mode is passed downstream in the replication stream so things should be ok. If interpretation of these modes changes from one major release to another then that could be quite complicated and I’m not sure here if that may be the case, but it should be taken into account as otherwise slaves may not behave the same as upstream masters of a lower version and that may trigger data being stored differently or replication breakage. Given the old and new timestamp issues which have plagued me with 5.5 to 5.6 migrations I’d hate for the sql_mode changes to do the same thing.