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!