Making the case to support +2 version upgrades

In the MySQL team, we have always had a requirement to support upgrades from one major version. For example:

  • Upgrading from MySQL 5.5 to 5.6 is supported.
  • Upgrading from MySQL 5.1 to 5.6 is not supported.

Downgrades are also supported for one major version. For example, if a user upgrades to 5.6 but discovers that it is not working as expected, they have the safety knowing that there is a way to step back to MySQL 5.5. This may come with some limits; for example when new features (such as new row formats or page checksums) are enabled, this may no longer be possible.

Today I wanted to discuss a current non-requirement. We do not support skipping major versions, such as upgrading from MySQL 5.1 to 5.6. Justin however makes the case that despite not being supported, it has often worked (at least when used with mysqldump).

We believe that Justin’s bug report has a lot of merit, and are considering extending our requirements to support a +2 version upgrade (i.e. 5.5 to 5.7). This will have a noticeable impact on our QA team, and the effort required to expand testing to handle additional upgrade scenarios will have to be carefully evaluated.

We are seeking feedback from our users in the community in response to BUG #76264! The specific questions we would like to ask you are:

  • Are you currently running MySQL 5.5 and planning to upgrade directly to MySQL 5.7?
  • If so; are there any constraints that make it too to be able to step through 5.6 as part of the upgrade process?
  • Would it be acceptable if a 5.5 to 5.7 upgrade was only supported via mysqldump?
  • Do you have a requirement for a >+2 version upgrade?

Please leave a comment, or get in touch!

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!