Errant 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> show master status; +------------------+----------+--------------+------------------+------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------+ | mysql-bin.000002 | 530 | | | 66fbd3be-976e-11e5-a8fb-1256731a26b7:1-2 | +------------------+----------+--------------+------------------+------------------------------------------+ 1 row in set (0.00 sec) mysql> show slave statusG ... Executed_Gtid_Set: 66fbd3be-976e-11e5-a8fb-1256731a26b7:1-2 Auto_Position: 1 1 row in set (0.00 sec) |
Add chaos to the slave in form of a new schema:
1 2 | mysql> create database percona; Query OK, 1 row affected (0.00 sec) |
Now we have an errant transaction!!!!!
The slave status looks different:
1 2 3 4 5 6 | mysql> show slave statusG ... Executed_Gtid_Set: 66fbd3be-976e-11e5-a8fb-1256731a26b7:1-2, 674a625e-976e-11e5-a8fb-125cab082fc3:1 Auto_Position: 1 1 row in set (0.00 sec) |
By using the GTID_SUBSET function we can confirm that things go from “all right” to “no-good”:
Before:
1 2 3 4 5 6 7 | mysql> select gtid_subset('66fbd3be-976e-11e5-a8fb-1256731a26b7:1-2','66fbd3be-976e-11e5-a8fb-1256731a26b7:1-2') as is_subset; +-----------+ | is_subset | +-----------+ | 1 | +-----------+ 1 row in set (0.00 sec) |
After:
1 2 3 4 5 6 7 | mysql> select gtid_subset('66fbd3be-976e-11e5-a8fb-1256731a26b7:1-2,674a625e-976e-11e5-a8fb-125cab082fc3:1','66fbd3be-976e-11e5-a8fb-1256731a26b7:1-2') as is_subset; +-----------+ | is_subset | +-----------+ | 0 | +-----------+ 1 row in set (0.00 sec) |
All right, it’s a mess, got it. What’s the errant transaction? The GTID_SUBTRACT function will tell us:
1 2 3 4 5 6 7 | mysql> select gtid_subtract('66fbd3be-976e-11e5-a8fb-1256731a26b7:1-2,674a625e-976e-11e5-a8fb-125cab082fc3:1','66fbd3be-976e-11e5-a8fb-1256731a26b7:1-2') as errand; +----------------------------------------+ | errand | +----------------------------------------+ | 674a625e-976e-11e5-a8fb-125cab082fc3:1 | +----------------------------------------+ 1 row in set (0.00 sec) |
The classic way to fix this is by injecting an empty transaction:
1 2 3 4 5 6 7 8 9 10 11 | mysql> SET GTID_NEXT='674a625e-976e-11e5-a8fb-125cab082fc3:1'; Query OK, 0 rows affected (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> SET GTID_NEXT='AUTOMATIC'; Query OK, 0 rows affected (0.00 sec) |
After this, the errant transaction won’t be errant anymore.
1 2 3 4 5 6 7 8 | mysql> show master status; +------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+ | mysql-bin.000002 | 715 | | | 66fbd3be-976e-11e5-a8fb-1256731a26b7:1-2, 674a625e-976e-11e5-a8fb-125cab082fc3:1 | +------------------+----------+--------------+------------------+----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
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:
1 | mysqlslavetrx --gtid-set=6aa9a742828411e5a09b12aac3869fc9:1 --verbose --slaves=user:password@172.16.1.143:3306,user:password@172.16.1.144 |
The verbose output will look something this:
1 2 3 4 5 6 7 8 9 | # GTID set to be skipped for each server: # - 172.16.1.143@3306: 6aa9a742828411e5a09b12aac3869fc9:1 # - 172.16.1.144@3306: 6aa9a742828411e5a09b12aac3869fc9:1 # # Injecting empty transactions for '172.16.1.143:3306'... # - 6aa9a742828411e5a09b12aac3869fc9:1 # Injecting empty transactions for '172.16.1.144:3306'... # - 6aa9a742828411e5a09b12aac3869fc9:1 # #...done. # |
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.
Thank you! Fixing bug into feezy squizy lead to environment improoving although litium requiring.
Establishment and standard impact factor so It’has been realizing.
hi;
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.
William, of course not. MariaDB has completely different GTID implementation then MySQL, and mysqlslavetrx is an official MySQL tool, not MariaDB one.