EmergencyEMERGENCY? Get 24/7 Help Now!

Fixing errant transactions with mysqlslavetrx prior to a GTID failover

 | December 2, 2015 |  Posted In: GTID, MySQL


GTID and errant transactionsErrant transactions are a major issue when using GTID replication. Although this isn’t something new, the drawbacks are more notorious with GTID than with regular replication.

The situation where errant transaction bites you is a common DBA task: Failover. Now that tools like MHA have support for GTID replication (starting from 0.56 version), this protocol is becoming more popular, and so are the issues with errant transactions. Luckily, the fix is as simple as injecting an empty transaction into the databases that lack the transaction. You can easily do this through the master, and it will be propagated to all the slaves.

Let’s consider the following situations:

  • What happens when the master blows up into the air and is out of the picture?
  • What happens when there’s not just one but dozens of errant transactions?
  • What happens when you have a high number of slaves?

Things start to become a little more complex.

A side note for the first case: when your master is no longer available, how can you find errant transactions? Well, you can’t. In this case, you should check for errant transactions between your slaves and your former slave/soon-to-be master.

Let’s think alternatives. What’s the workaround of injecting empty transactions for every single errant transaction to every single slave? The MySQL utility mysqlslavetrx. Basically, this utility allows us to skip multiple transactions on multiple slaves in a single step.

One way to install the MySQL utilities is by executing the following steps:

  • wget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.6.2.tar.gz
  • tar -xvzf mysql-utilities-1.6.2.tar.gz
  • cd mysql-utilities-1.6.2
  • python ./setup.py build
  • sudo python ./setup.py install

And you’re ready.

What about some examples? Let’s say we have a Master/Slave server with GTID replication, current status as follows:

Add chaos to the slave in form of a new schema:

Now we have an errant transaction!!!!!

The slave status looks different:

By using the GTID_SUBSET function we can confirm that things go from “all right” to “no-good”:



All right, it’s a mess, got it. What’s the errant transaction? The GTID_SUBTRACT function will tell us:

The classic way to fix this is by injecting an empty transaction:

After this, the errant transaction won’t be errant anymore.

Okay, let’s add another slave to the mix. Now is the moment where the mysqlslavetrx utility becomes very handy.

What you need to know is:

  • The slave’s IP address
  • The GTID set

It will be simple to execute:

The verbose output will look something this:

You can run mysqlslavetrx from anywhere (master or any slave). You just need to be sure that the user and password are valid, and have the SUPER privilege required to set the gtid_next variable.

As a summary: Take advantage of the MySQL utilities. In this particular case, mysqlslavetrx is extremely useful when using GTID replication and you want to perform a clean failover. It can be added as a pre-script for MHA failover (which supports GTID since the 0.56 version) or can be simply used to maintain consistency between master and slaves.

Daniel Guzmán Burgos

Daniel studied Electronic Engineering, but quickly becomes interested in all data things. He has worked as a DBA since 2007 for several companies including one of the major social gaming company at that time in Latin America. Daniel lives in Bogotá, Colombia and in his free time he loves to spend time with his family and go out to eat at any kind of restaurant. Working for Percona since 2014, he is part of the Consulting team.

One Comment

  • Thank you! Fixing bug into feezy squizy lead to environment improoving although litium requiring.
    Establishment and standard impact factor so It’has been realizing.

Leave a Reply