Buy Percona ServicesBuy Now!

Repair MySQL 5.6 GTID replication by injecting empty transactions

 | March 26, 2013 |  Posted In: MySQL


In a previous post I explained how to repair MySQL 5.6 GTID replication using two different methods. I didn’t mention the famous SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n for a simple reason, it doesn’t work anymore if you are using MySQL GTID. Then the question is:

Is there any easy way to skip a single transaction?

There is! Injecting empty transactions. Let’s imagine that the replication in slave server is not working because of an error:

There are different ways to find the failed transaction. You can examine the binary logs or you can also check Retrieved_Gtid_Set and Executed_Gtid_Set from the SHOW SLAVE OUTPUT as we can see in the example. This slave server has retrieved transactions 1 to 5 but has only executed 1 to 4. That means that transaction 5 is the one that is causing the problems.

Since SQL_SLAVE_SKIP_COUNTER doesn’t work with GTID we need to find a way to ignore that transaction. The way to do it is creating a new empty transaction with it the GTID we want to skip.

After the START SLAVE the slave checks that transaction 5 is already in its own binary log and that means that it has been executed.

This is an easy way to skip some transactions but take in account that by doing this you will end up with data inconsistencies between Master and Slave servers. pt-table-checksum can help you here, which is found in Percona Toolkit for MySQL.

Last week I gave a talk at Percona MySQL University @Toronto about GTID. It includes an overview of MySQL 5.6 GTID that can help people to start working with this new feature in MySQL 5.6. Here are the slides from that session. I hope you find it useful: MySQL 5.6 GTID in a nutshell

Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow.


  • Maybe wrong place to post this, but as its MySQL 5.6 related, I thought I would bring up the topic (for lack of a better place to do it)

    What is going on with the mysql 5.5 / 5.6 changelogs on Anyone else noticed how extremely quiet they have been since 5.6 went GA? I usually look at them every week… and I have seen no changes for weeks and weeks.

    So either Oracle released what they believe to be an almost bug-free MySQL release… or they are taking bug fixing / development behind closed doors

    I believe it deserves some kind of blog post on Percona asking what’s going on, or answering it [if anyone at Percona knows someone at Oracle who knows]

  • I stopped slave and while executing SET GTID_NEXT=”0c7b6d40-e55d-11e2-952b-00221950c39:15114″; on slave getting error Error Code: 1231. Variable ‘gtid_next’ can’t be set to the value of ‘0c7b6d40-e55d-11e2-952b-00221950c39:15114’

  • What this article does not cover is a multi-master environment – such as a circular replication topology. In that case, you need to know which servers transaction (GTID) to skip. This does explain how to accomplish this. Please update the article.

  • I tried to do this, but I got an error.
    I did,
    SET GTID_NEXT= “8b61c055-e539d-22e3-8894-00163e01174b:962″

    I got the same error in SHOW SLAVE STATUS. Now the value is set to

    What does “2-17:962” means? All i wanted is to skip everything and start from transaction number 962.

    Thank you

  • The “2-17:962” is says that GTIDs 2 through 17 and 962 have been executed. That is important – EXECUTED. That means it was applied to the database If you have an error its probably for GTID 963 (presumably the next one in sequence). However, you need to view the error and you may need to use the mysqlbinlog on the slave-bin-xxxx.log file to see what the next transaction is.

  • i meet a problem..
    “ERROR 1231 (42000): Variable ‘gtid_next’ can’t be set to the value of ‘xxxx’ ”

    ” set gtid_next=’xxx’ ” not work ? why ??

  • Hi
    I am facing the same problem as like Lithium. No errors and no issues in sql and io status. But retrieved_gtid_set is empty on slave.

    Intentionally i have stopped replication and restored dump seperately on both the servers. Then i issued reset master and reset slave. Now starting replication its started but thr transaction id from master to slave is not updating. please help to solve this

  • Hi,

    what happen when I have multiple entries for Executed_Gtid_Set?

    Retrieved_Gtid_Set: e1493393-7c7b-11e4-b3ee-001a4a010192:1186360318-1186905836

    Executed_Gtid_Set: 370048ef-f18c-11e7-bf48-fa163e31bd7a:1-69,



    Auto_Position: 0

Comments are closed