Fixing errant transactions with mysqlslavetrx prior to a GTID failover

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
  • tar -xvzf mysql-utilities-1.6.2.tar.gz
  • cd mysql-utilities-1.6.2
  • python ./ build
  • sudo python ./ 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.

Share this post

Comments (3)

  • Frank79

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

    December 7, 2015 at 4:33 am
  • william

    Is mysqlslavetrx can be use in MariaDB 10.2, can’t find ‘executed gtid-set’. How can i fix errant transactions in MariaDB 10.2? Thank you.

    October 13, 2017 at 9:43 am
    • Przemysław Malkowski

      William, of course not. MariaDB has completely different GTID implementation then MySQL, and mysqlslavetrx is an official MySQL tool, not MariaDB one.

      February 20, 2018 at 3:08 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.