I have previously written about the new replication protocol that comes with GTIDs in MySQL 5.6. Because of this new replication protocol, you can inadvertently create errant transactions that may turn any failover to a nightmare. Let’s see the problems and the potential solutions.

In short

  • Errant transactions may cause all kinds of data corruption/replication errors when failing over.
  • Detection of errant transactions can be done with the GTID_SUBSET() and GTID_SUBTRACT() functions.
  • If you find an errant transaction on one server, commit an empty transaction with the GTID of the errant one on all other servers.
  • If you are using a tool to perform the failover for you, make sure it can detect errant transactions. At the time of writing, only mysqlfailover and mysqlrpladmin from MySQL Utilities can do that.

What are errant transactions?

Simply stated, they are transactions executed directly on a slave. Thus they only exist on a specific slave. This could result from a mistake (the application wrote to a slave instead of writing to the master) or this could be by design (you need additional tables for reports).

Why can they create problems that did not exist before GTIDs?

Errant transactions have been existing forever. However because of the new replication protocol for GTID-based replication, they can have a significant impact on all servers if a slave holding an errant transaction is promoted as the new master.

Compare what happens in this master-slave setup, first with position-based replication and then with GTID-based replication. A is the master, B is the slave:

As expected, the mydb database is not created on A.

mydb has been recreated on A because of the new replication protocol: when A connects to B, they exchange their own set of executed GTIDs and the master (B) sends any missing transaction. Here it is the create database statement.

As you can see, the main issue with errant transactions is that when failing over you may execute transactions ‘coming from nowhere’ that can silently corrupt your data or break replication.

How to detect them?

If the master is running, it is quite easy with the GTID_SUBSET() function. As all writes should go to the master, the GTIDs executed on any slave should always be a subset of the GTIDs executed on the master. For instance:

Hum, it looks like the slave has executed more transactions than the master, this indicates that the slave has executed at least 1 errant transaction. Could we know the GTID of these transactions? Sure, let’s use GTID_SUBTRACT():

This means that the slave has 2 errant transactions.

Now, how can we check errant transactions if the master is not running (like master has crashed, and we want to fail over to one of the slaves)? In this case, we will have to follow these steps:

  • Check all slaves to see if they have executed transactions that are not found on any other slave: this is the list of potential errant transactions.
  • Discard all transactions originating from the master: now you have the list of errant transactions of each slave

Some of you may wonder how you can know which transactions come from the master as it is not available: SHOW SLAVE STATUS gives you the master’s UUID which is used in the GTIDs of all transactions coming from the master.

How to get rid of them?

This is pretty easy, but it can be tedious if you have many slaves: just inject an empty transaction on all the other servers with the GTID of the errant transaction.

For instance, if you have 3 servers, A (the master), B (slave with an errant transaction: XXX:3), and C (slave with 2 errant transactions: YYY:18-19), you will have to inject the following empty transactions in pseudo-code:

Conclusion

If you want to switch to GTID-based replication, make sure to check errant transactions before any planned or unplanned replication topology change. And be specifically careful if you use a tool that reconfigures replication for you: at the time of writing, only mysqlrpladmin and mysqlfailover from MySQL Utilities can warn you if you are trying to perform an unsafe topology change.

10 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Baron

I think the $5 question many readers will ask is, how do you actually DO this: “…inject an empty transaction … with the GTID of the errant transaction” ?

0
Jean-François Gagné

Stéphane, the approach you describe to solve errant transactions is to add the transactions everywhere, tricking MySQL to think the transactions have been executed. I like the approach but, as you point out, it is tedious when having lot of slaves.

What do you think of the flip-side approach: instead of adding the transactions everywhere, lets get rid of them on the offending slave. We can do that by:
1. stopping the slave,
2. running SHOW MASTER STATUS on the slave to get Executed_Gtid_Set,
3. doing a RESET MASTER on the slave,
4. initializing GTID_PURGED on the slave to Executed_Gtid_Set without the offending transactions,
5. starting back the slave.
Maybe this scales better when having lot of slaves. The downsides I see are:
a. the offending slave needs to be stopped,
b. this slave cannot be used as a new master until it builds back its binary logs.
But when having lot of slaves, those 2 could be minor inconveniences.

1
Stephane Combaudon

Baron,

You’re right. As explained here: http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-failover.html#replication-gtids-failover-empty, injecting an empty transaction means running these statements:

mysql> set gtid_next=’UUID:NN’;
mysql> begin;commit;
mysql> set gtid_next=automatic;

0
Stephane Combaudon

Jean-François,

To get rid of an offending transaction, you basically have 2 options:
– inject a fake transaction with the GTID of the real one
– permanently exclude the transaction from the history of GTIDs

My strategy relies on option #1, yours on option #2, and they’re both valid.

Scripting will be helpful anyway, and for the strategy I explained in the post, there’s already code available in the MySQL Utilities.

0
hiller1

Inject empty trx ，I can’t understand ，Example for too simple， can you simulate a real fault scenarios, and then gives the solution？Your article, people doesn’t understand it.

0
hiller1

hi,Stephane:

Thank you very much !

0
Egezon Berisha

We are implementing MHA (http://code.google.com/p/mysql-master-ha/) manual failover for now, it works pretty good for failover (master promotion), but it does not take care of Errant transactions. And the slave fails to remster. We’re going write a script to ignore the transactions that happened on slave at the time of failover. It should work pretty good, with X number of slaves.

0
Chriss

Stephane,
In every place that I checked I see that empty transaction should be injected to every host in the replication topology. I’m really curious – why inserting it on the master won’t be enough? In fact, in my tests, I did inject empty transactions on master only and they replicated down the chain filling nicely gaps in ‘Executed_Gtid_Set’ in the process. What’s the problem with this approach that Oracle had to develop mysqlslavetrx to handle injection on multiple slaves in complex environments? Am I just very lucky that it works for me?

0
Stephane Combaudon

Chriss,

Injecting empty transactions on the master indeed works. But there is one situation when it doesn’t work: if you realize that one slave has an errant transaction at failover time when the master is dead.
In this case, the only option you have is to inject empty transactions on every slave, hence mysqlslavetrx.

0