Rollback Testing Surfaces a Hidden Risk

Benchmarking the new version revealed no performance anomalies, but the rollback path told a different story. When attempting to downgrade from Percona Server 5.7.32 back to 5.7.21, MySQL refused to start cleanly. The error logs pointed to a root cause that was subtle but severe: the table_name column in the innodb_index_stats and innodb_table_stats system tables had been widened from VARCHAR(64) in 5.7.21 to VARCHAR(199) in 5.7.32. This change made the old server unable to read the statistics it needed at startup.

Worse, when we permitted the calculation of transient stats during startup to run to completion, the process took over a day on some shards due to lengthy table analyze operations. That was unacceptable for any rollback scenario where speed mattered. Allowing full table rebuilds during a rollback—when we might be responding to an incident—was not something we could tolerate operationally.

Our first attempt at a fix was to patch mysql_system_tables_fix.sql in our internal Percona Server fork, setting the column lengths back to values that 5.7.21 expected. This eliminated the column-length errors, but the analyze table process still triggered full table rebuilds and produced the same unacceptable startup times. We had addressed a symptom, not the underlying problem.

The Real Culprit: In-Place Upgrades

After digging further, we realized the issue was likely tied to how we were handling upgrades. Our Chef-based upgrade path performed an in-place upgrade: we restored a backup from a 5.7.21 VM onto a 5.7.32 instance, let it start, and then ran mysql_upgrade. This approach modified system tables in place—which is what led to the schema changes that made rollback so difficult.

The deeper problem was that the upgrade process itself was altering data structures, not just increasing the version number. When you change the definition of system tables that store statistics, you risk breaking compatibility with older binaries that expect the old schema. That is a fundamentally different scenario from a typical patch upgrade where the on-disk format remains stable.

We also noted that one of the things triggering the analyze table process was our startup routine, which runs ALTER TABLE commands to set a minimum AUTO_INCREMENT value on tables (a workaround for the fact that the auto-increment counter isn't persisted across restarts in MySQL 5.7—a long-standing limitation that is addressed in MySQL 8.0). This routine was interacting badly with the schema changes from the in-place upgrade, causing MySQL to rebuild tables unnecessarily during startup.

A Safer Upgrade Path

Given these findings, we decided to abandon the in-place upgrade approach entirely. Instead of upgrading a VM from its existing state, we would provision entirely new VMs with the new version and the old data. This approach—which we call a restore-based upgrade—ensures that each instance is pristine from the start, avoiding any residual state from the previous version that could cause issues during rollback.

The new upgrade flow looks like this:

  1. Take a backup from an existing 5.7.21 VM using our standard backup system, which leverages Google Cloud persistent disk snapshots.
  2. Spin up a new VM with the new Chef role that specifies Percona Server 5.7.32.
  3. Restore the backup onto the new VM.
  4. Let the MySQL server start and run mysql_upgrade to update system tables to the new version.
  5. Join the new instance to the replication topology as a replica.
  6. Optionally, take backups from this upgraded instance to seed other replicas.
  7. After validation, perform a takeover to promote the upgraded instances to primary roles.

This approach eliminates the risk of upgrade-induced schema corruption. The new VM has only ever seen MySQL 5.7.32, so there's no opportunity for old version artifacts to create compatibility issues later.

Rollback Testing, Cleanly

The restore-based approach also simplifies rollback. If the upgrade goes wrong after we've promoted new versions to primary, we can simply restore from a pre-upgrade backup onto a new 5.7.21 VM and rejoin the topology. There's no need to downgrade in place—we simply provision a new environment with the old version.

We tested this rollback path thoroughly. Restoring a 5.7.32 backup onto a fresh 5.7.21 VM worked cleanly, with MySQL starting up without the table rebuild issues we had seen with the in-place downgrade. This gave us confidence that if we ever needed to revert, we could do so without risking multi-day startup times or data corruption.

Reproducing the Failure

With a hypothesis in hand, the team set up a debugging session. They found that the problem could be triggered by a specific sequence of backup, restore, and ALTER TABLE operations across versions:

  1. On 5.7.32, restore a backup taken from 5.7.21.
  2. Run a metadata-only ALTER TABLE, such as ALTER TABLE t AUTO_INCREMENT=n; this succeeds instantly.
  3. Take a new backup from this 5.7.32 instance.
  4. On 5.7.21, restore the backup taken in step 3.
  5. Start the 5.7.21 server; mysql_upgrade performs the in-place downgrade.
  6. Run the same metadata-only ALTER TABLE again. The server unexpectedly performs a full table rebuild.

Using the GNU Debugger (GDB), the team traced the failure to the has_index_def_changed function in sql/sql_table.cc. At that point, the flags value for the old table version (table_key->flags) differed from the new version (new_key->flags), even though only a metadata change was intended. The difference was enough to make MySQL’s index-change detection conclude that a rebuild was necessary.

A History of Partial Fixes

This turned out to be a recurring problem. The MySQL 5.7.23 release notes mention a bug where an indexed VARCHAR column expansion using ALTER TABLE ... INPLACE failed if the index size exceeded InnoDB’s 767-byte limit for COMPACT or REDUNDANT row formats. The fix that shipped changed how certain index flags were set.

But the story doesn’t end there. The 5.7.27 release notes describe a follow-up bug (Bug #29375764, Bug #94383): tables created before 5.7.23 that had an index on a VARCHAR column would, after upgrading to 5.7.23 or higher, trigger full table rebuilds for simple ALTER TABLE statements that should have been in place. The second fix addressed that symptom but still missed cases where a downgrade path left the flags inconsistent.

Shopify’s team filed Bug #104413 with a proposed patch, but it wasn’t accepted by the MySQL team. To keep the upgrade safe, they patched Percona Server in their own internal fork. After successful rollback tests on the patched build, the upgrade was cleared to proceed.

The Packed Keys Connection

The root cause lies in the PACK_KEYS feature, which is specific to the MyISAM storage engine. When a MyISAM table has indexed VARCHAR columns and those columns are expanded past eight bytes, the storage engine switches from unpacked to packed keys — a real change that rightfully requires an index rebuild.

InnoDB does not support packed keys at all; its index format is entirely different. But the first fix in 5.7.23 applied the same flag logic to InnoDB tables, even though the flags have no meaning there. To correct this, the HA_PACK_KEY and HA_BINARY_PACK_KEY flags stopped being set from 5.7.23 onward for storage engines that don’t support them. That created a new problem for tables created before that version: their internally stored flags still exist, and on upgrade they trigger the false "index changed" signal.

The second fix removed the flags entirely for unsupported storage engines, but it still didn’t handle scenarios where the flag values changed between versions but the difference should have been ignored. Shopify’s patch filled that gap: during a downgrade, if the old table version (5.7.32) lacks the flag but the new version (5.7.21) has it, the index rebuild is skipped.

Improving Mason for Scale

In parallel, the engineering team was extending Mason, Shopify’s MySQL topology manager, with features needed for the upgrade effort. The requirements shaped several key additions:

  • A “priority” lane so self-healing requests always beat provisioning requests from scale-ups.
  • Throttling for the scale-up provisioning queue to limit concurrent work.
  • Feature flags to restrict scale-up provisioning to a controlled set of shards.
  • A dry-run mode to test the new features without affecting live systems.

Caution was central to this work. With a fleet of hundreds of shards at petabyte scale, a mistake could mean unnecessary GCP resource usage or hours of decommissioning work. Stabilizing Mason itself was also a priority; it had become a critical piece of infrastructure, so early efforts went into hardening its staging environment, adding monitoring, and emitting metrics to Datadog when the topology was underprovisioned.

Mason’s interactions with many external components — GCP APIs, Chef, Kubernetes, ZooKeeper, Orchestrator, and the database VMs themselves — made failure scenarios hard to predict. The team notes that improving integration testing remains an ongoing goal. As more people joined the project and features stacked up, the codebase’s brittleness became a bottleneck. Concurrent feature work became more difficult, highlighting the value of a well-structured codebase in avoiding blockers. Despite the challenges, the project shipped on time, and the team is now directing effort into making Mason more maintainable and developer-friendly.

The Production Rollout

Rollback testing doubled as canary coverage: several shards had already been running 5.7.32 for months, with regular load tests providing extra confidence. The final rollback plan relied on maintaining a 5.7.21 VM per shard and taking backups from them, but the team ultimately dropped that idea. Keeping hundreds of spare VMs running for each shard would require significant tooling and monitoring, all to support an option they expected to use only as a last resort.

The decision was to fix forward and de-risk via extensive rollback testing instead. When provisioning began on August 25th, the upgrades were staggered into several shard batches. That let recently upgraded shards “bake” and contained the blast radius of any unforeseen problem, while also limiting the resource churn across Google Cloud.

On September 7th, the final shards completed their upgrade — closing out the project.

Lessons From the Upgrade

The upgrade effort made one thing clear: thorough rollback testing is non-negotiable. Only through extensive validation did Shopify discover a critical bug that would have blocked a downgrade. While rebuilding the entire fleet with the previous version would have been painful, patching MySQL 5.7.21 gave the team confidence to proceed, knowing a safe fallback existed if needed.

Mason's Rising Importance

Mason, Shopify's database tooling, grew significantly in importance over the course of the project. Previously considered a lower-tier application, it was often disabled as a quick fix when it misbehaved, and bug fixes weren't prioritized. But as the fleet scaled, the team recognized Mason's critical role in reducing toil and maintaining healthy on-call expectations.

This realization led to increased investment: improved test coverage and refactoring of key code sections to reduce complexity and enhance readability. Future work includes upgrading local development environments and streamlining Mason's deployment pipeline.

Repeatability Over Reinvention

Documentation and repeatability proved to be major wins. Early in the planning phase, discovering how past upgrades were executed required significant institutional knowledge and a scavenger-hunt-like search. By creating clear guidelines and documentation, the team transformed the MySQL upgrade process into a straightforward series of steps executed with existing tooling.

This approach shifts the effort from a manual, context-heavy process that offers no future benefit to a structured workflow that makes subsequent upgrades faster, safer, and more efficient. The next milestone is already in sight: MySQL 8.