Nasty MySQL Replication Bugs that Affect Upgrade to 5.6

There were two nasty MySQL replication bugs in two different 5.6 releases that would make it difficult to upgrade slaves to MySQL 5.6 while still connected to MySQL 5.5 master.

The first of those bugs is MySQL bug 72610 which affects 5.6.19. Essentially this bug is triggered when the table structure on the slave is different from the table structure on the master which leads to unnecessarily large amount of RAM usage while replicating events that affect that table. The amount of RAM used would generally be more noticeable when the replicated transaction consists of thousands of RBR events.

The most common way this affects how we upgrade a replication hierarchy, is when we have the master running MySQL 5.5 and the slave running MySQL 5.6 and we have transactions involving DATETIME column(s). Tables with DATETIME columns will have different underlying structure when created on MySQL 5.5 versus when created on MySQL 5.6. Ideally you would avoid creating a new table with temporal columns while you still have master and slave on different MySQL major versions. Ideally you would also want to avoid running statements that would ALTER the structure of the table with temporal columns, such as running a NOOP ALTER TABLE or adding a new column to a table with temporal column(s). As any such operation will end up creating a new table with a new underlying structure.

Coming back to the bug itself, this bug was fixed in MySQL 5.6.21 which fixed excessive memory usage issue. However, the fix introduced a crashing bug details of which are reported in a Percona Server bug 1380010. This isn’t specifically a Percona Server bug, but rather a MySQL server bug. The crashing bug again affects MySQL upgrades badly. The bug, again, comes into play when you have master and slave with table(s) that have different underlying structure. Which is, again, something that you will likely see when you are upgrading a replication hierarchy, where for sometime you will have MySQL 5.5 master replicating to MySQL 5.6 slaves.

The good news is that Percona Server 5.6.21-70.1 released on 24 November, 2014 fixes the crashing bug. So its safe again to upgrade to Percona Server 5.6.21-70.1 from an older MySQL or Percona Server version. I would love to see this fix in the upstream MySQL server as well.

Ovais is a storage architect with keen focus on reliability, efficiency and performance of OLTP databases, more specifically MySQL. He currently works at Uber on ensuring storage reliability and scalability. Previously, he helped different web properties scale their MySQL infrastructure. He also worked at Percona as a Senior MySQL consultant and at a few other startups in various capacities.