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!

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.

  • MySQL in the Oracle era has accelerated the pace of major versions (on the order of a 2 year cycle instead of a 3 year cycle in the pre-Oracle years), so there are bound to be more people who are still using MySQL 2 major versions back when the new major version comes out.

    But I would be in favor of increasing the QC effort for the upgrade process even if you don’t expand the scope of upgrades with this proposal. There were a number of tricky bugs and WTF’s during the 5.5 -> 5.6 upgrade process, which made people in the community wonder if the upgrade scenarios had been tested adequately.

    If MySQL were a startup, it would have a high proportion of greenfield sites, and you could get away with breaking minor things during an upgrade or requiring a dump & restore. But MySQL is used in a large number of mature sites with lots of data and requirements for minimal downtime. Requiring a dump & restore is a sure way to make people postpone the upgrade for years.

    A current example site using MySQL is still using 5.1.x and they have over 2TB of data spread over more than half a dozen shard servers. In a sharded architecture, it’s difficult to do the dump & restore path, because you either have to quiesce the whole site during the process, or else if you go with the restore-to-slave route, you have a complete set of duplicate hardware. Both of these paths are impractical when the dataset is in the TB scale. So they have to do an in-place upgrade.

    There are a number of breakages that occur when variable defaults change, or the upgrade fails to run mysql_upgrade, or the new optimizer features cause pegged CPU, or the internal format for temporal data types changes and causes havoc with RBR. Many of these pitfalls are documented, but tools for testing or executing an upgrade are not provided AFAIK.

    If one is skipping versions, you find a few more cases where things could break. For example, features or config variables that were responsibly deprecated and generated a warning in 5.6, but which are removed completely in 5.7. This could cause a surprise for the casual DBA if the server fails to start up.

    The upgrade process needs a tool that inspects the configuration and the query log, and produces a report of which config values are likely to change without the DBA intention, which config values should be changed, and which queries are going to behave differently on the new version. Also report missing system tables/columns, or user data or metadata that deserves attention.

  • Domas Mituzas

    Morgan, did MySQL ever support in-place upgrades? Every time it was told that “it is supported but not recommended and please don’t file bugs because nobody will work on them and why do you even bother, just dump and reload”
    As it happens, apparently skipping a major release is a standard operation practice at many places (at least ones that have people who value their time). Some upgrades are just not worth it (and I had lots of pleasure skipping 4.1, 5.0 and 5.5 at places 😉

    • If I am not mistaken, you skipped 5.5 because you ‘frankensteined’ 5.1 to behave like 5.5? 🙂

      We are working hard to make in-place upgrades work. 5.6->5.7 is something that our QA team has spent a lot of time working on.

      • Domas Mituzas

        To be honest, some of us frankensteined 4.0 to behave like 5.6.

      • Mark Callaghan

        5.5? Never heard of it, never ran it.

      • Justin Swanhart

        Yahoo! is well known for skipping major versions and not “frankensteining” older ones. For a lot of reasons, production installations might not get upgraded to every major release, particularly if the release (like 5.0) is not considered stable. I’m sure many other large companies are the same.

        • 5.0 was a bit of a special case because of how damn awful it was when released.

    • Simon J Mudd

      I’ve done in-place major version upgrades from 5.0 to 5.7. They’ve all worked fine up to 5.7.5.
      I’ve also argued that using MySQL dump+load is not really a feasible option in many cases.
      I did just bump into http://bugs.mysql.com/bug.php?id=76262 in 5.7.6 and it’s being looked at, but I think people should expect a binary upgrade with mysql_upgrade to work even if the documentation hopes you’ll dump your 10MB DB and load it back into a new clean upgraded instance.

  • Justin Swanhart

    Typical scenario: hosting provider provides version X.z. I am upgrading from X.x. I have no access to console, I can’t install X.y, I don’t have a shell. How do I upgrade to X.z?

    • If this hosting provider is limiting you access, wouldn’t it be the case that you are dumping an individual schema (without the permissions tables)? This can still produce incompatibilities, but the scope is quite different.

      • Justin Swanhart

        No, assume I have super access in both, that is not uncommon. For example, I can connect to and dump my hosted mysql instance (which I created through my hosting provider web interface), but I don’t have a shell, I have to do all my work with remote tools or phpmyadmin. I have one hosting provider that allows me to create up to ten mysql instances, each with their own different users, databases, etc. This is not uncommon in the least. I would never use a provider like RDS, which doesn’t give me super.

        • Kevin White

          Is this a shared hosting provider or a VPS like service?

          Isn’t it on the hosting provider to actually perform the ugprade? It’s been a LONG time since I’ve used shared hosting, but I never recall having system access to upgrade any applications, I always thought this would fall on the provider.

          • Justin Swanhart

            I think you missed the point. I have two providers. Both let me create an instance and the instance is mine. neither give me a shell account. I can not control the version of the providers. I want to take my data from provider A to provider B. I use mysqldump to do so. Again, I can not control the version of either provider. One is 5.1 there other is 5.7. I want to go to the second provider because I want 5.7 features. I can’t run 5.6 on the second provider, it is only 5.7, and again, I have no shell. The second provider is not going to downgrade my instance (or instances) to 5.6, then upgrade them to 5.7 again. This is not really rocket science.

  • Eric Bergen

    For an in place upgrade the procedure is to upgrade and then run mysql_upgrade. Depending on what changed mysql_upgrade may choose to rebuild all of your tables which is effectively the same as a dump and reload.

    Sometimes enabling new features doesn’t work in-place at all. When GTIDs shipped they were completely broken requiring rebuilding an entire replicaset to enable them.

    My vote is to focus on making single version upgrades not require a rebuild of an instance or a replicaset and then worry about multiple versions.

    • Thank you for adding the context 🙂

      Yes, in the past if data types have changed internal format a mysql_upgrade could perform a lot of work internally. I am not aware of any of these cases for 5.6->5.7, so the in-place upgrade should be more straight forward.

      To clarify on another point in case it wasn’t clear:
      It was 5.7.6 that implemented support for an in-place upgrade from 5.6 (did not work prior.)

      • Justin Swanhart

        The best example of this was 5.0 -> 5.1 where utf_general_ci changed the order of u with umlaut.

        There are good reasons to dump/restore from 5.5 to 5.6, like the temporal format change for temporal types. MySQL will upgrade tables individually, but a fast alter suddenly becomes a surprising slow table copy, and suddenly RBR breaks because the master is sending old format rows and you are in the middle of a rolling schema upgrade to simply add an index.

  • Kevin White

    Justin brought up a good point I didn’t think of, but other than that I don’t see a reason for needing to go from version x to version x + 2 without being able to step through version x + 1 first. Currently I see no need for it.

    For the environment I work in it would definitely be unacceptable to go from 5.5 -> 5.7 through a mysqldump and we haven’t had any problems in the past upgrading through 5.1->5.5->5.6 in place.

  • Justin Swanhart

    From my facebook page:
    Stewart Smith: Like it or not, mysqldump is an archival format. *Something* has to continue to read it. Breaking pre-5.0 dumps could be justified, but anything since then… eep.

    • Yeah, mysqldump *is* an archival format. This is what people are using to take long-lasting backups and a “if everything else fails, I can read this”. It’s also used as a “This is how to set up your database” format for applications (although less so now than it used to be).

      The whole dump&restore thing has been considered safer than in-place upgrades, and indeed, has been used when making giant leaps forward. It would be good to maintain this compatibility, and it shouldn’t be *that* hard to do so.

  • Justin Swanhart

    To upgrade to 5.6 from ANY previous version, MySQL recommends that you dump your tables with mysqldumpbefore upgrading and reload the dump file after upgrading. Use the –all-databases option to include all databases in the dump. If your databases include stored programs, use the –routines and –events options as well.

    emphasis on ANY is mine:
    http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html

    • Justin Swanhart

      That one major version upgrade policy sounded fishy to me. It has always been the case that mysql_upgrade is only supported for the previous major version. Any other version should be upgraded via mysqldump, rather than going through multiple in-place upgrades.

      It seems the 5.7 manual page has been updated to this new “go through every previous version” policy and it is not a long standing policy.

      • Justin Swanhart

        Actually the 5.7 manual still says it:
        http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

        To upgrade to 5.7 from any previous version, MySQL recommends that you dump your tables with mysqldumpbefore upgrading and reload the dump file after upgrading. Use the –all-databases option to include all databases in the dump. If your databases include stored programs, use the –routines and –events options as well.

        • Thanks for pointing out the mysqldump bit in the manual.

          At the moment we’ve only been ensuring that mysql_upgrade works one version back. Do a 5.1 to 5.6 change and we don’t do QA to ensure that it works, while we do for 5.5 to 5.6. There seems to be internal agreement that that should change, we just need
          to pick a number. We’ve also been doing a fair bit of work on other aspects of upgrade/downgrade since we think we can improve that more broadly.

          The reality of what we in the Support team will tell people to do, as well as what we expect end users to do, is to use mysqldump and rely on mysql_upgrade doing the work, if possible, or human patching if not. So we’re trying to get what we do and test better aligned with what we really expect people to do. For big data cases we’d probably suggest sequential in-place upgrades instead.

          What’s happening at the moment is that we’re looking to see how far back we can sensibly go with mysql_upgrade, balancing development work and ongoing QA work requirements with the number of people who we think will benefit from each version step. It’s too soon to say what the answer to that will be but from the MySQL Support team side I would probably accept an answer in the 4.1, 5.0 or 5.1 set.

          If you’d like to argue for a particular threshold, particularly one beyond 5.1, now’s not a bad time to do it, explaining why and the size user population that you think would benefit from the work vs having to do some hand statements instead. The biggest cost is the extra and ongoing QA work, not the initial programming.

          Once we’ve decided internally how far back we think it’s sensible to go Morgan will probably blog about that, seeking more feedback.

          James Day, MySQL Senior Principal Support Engineer, Oracle