Repair MySQL 5.6 GTID replication by injecting empty transactions

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

Share this post

Comments (13)

  • Steve Jackson

    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]

    March 27, 2013 at 9:33 am
  • Lalit

    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’

    January 10, 2014 at 6:19 am
  • Lalit

    sorry is ‘t my mistake it’s working fine , thanks for the post

    January 10, 2014 at 7:08 am
  • Van

    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.

    May 8, 2014 at 8:21 am
  • egezon

    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

    May 23, 2014 at 2:57 pm
  • Van

    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.

    May 24, 2014 at 7:07 am
  • Lithium

    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 ??

    April 14, 2015 at 8:28 am
  • Jeevitha

    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

    May 14, 2015 at 8:45 am
  • Fernando


    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

    January 5, 2018 at 7:11 am
    • Andre

      Hi Fernando,

      you have to find out the “Master_UUID” in “SHOW SLAVE STATUS”.

      In your case it is probably: e1493393-7c7b-11e4-b3ee-001a4a010192


      May 7, 2019 at 1:37 pm
  • Andre


    what GTID_NEXT value should I take in this case?

    Executed_Gtid_Set: 07c6e70b-52b4-11e9-9332-1402ec87c47c:1-801368897:801956080-814877202


    May 7, 2019 at 1:34 pm
  • dolu hasar tamiri

    This is an easy way to skip some transactions but take in account that

    September 7, 2019 at 10:17 am
  • mini onarım

    There is! Injecting empty transactions. Let’s imagine that the replication in slave server

    September 14, 2019 at 11:38 am

Comments are closed.

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