As you probably already know, SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n doesn’t work anymore if you use GTID based replication. Is there a way to skip a single transaction in such a case?
Yes, there is!! Inject an empty transaction. MySQL documentation is available at but let’s take a look at an example.
Say replication on a GTID based replication slave is stopped with an error as shown below:
|
1 2 3 4 5 |
Last_SQL_Error: Error 'Duplicate entry '29' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into abc VALUES(0,'tips kaali')' Retrieved_Gtid_Set: 5e41d0c9-2679-28b1-d9a1-562911735d2f:1-250 Executed_Gtid_Set: 5e41d0c9-2679-28b1-d9a1-562911735d2f:1-50 |
The Retrieved_Gtid_set above shows that transactions 1 to 250 have been retrieved from the Master but only transactions 1-50 have been applied. This means that transaction 51 is causing the replication to fail. If your application allows, you can inject an empty transaction number 51 to skip the one retrieved from the Master.
This is how to achieve it.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
STOP SLAVE; SET GTID_NEXT="5e41d0c9-2679-28b1-d9a1-562911735d2f:51"; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC"; START SLAVE; SHOW SLAVE STATUS\G [...] Retrieved_Gtid_Set: 5e41d0c9-2679-28b1-d9a1-562911735d2f:300 Executed_Gtid_Set: 5e41d0c9-2679-28b1-d9a1-562911735d2f:80 |
From the output of SHOW SLAVE STATUS, it is clear that after injecting the empty transaction 51, replication has resumed. It has fetched upto transaction 300 from Master and also applied / executed up to transaction 80 on the Slave.
Note: Injecting transactions like this can introduce data inconsistencies between Master and Slave. It is, however, a useful way to fix replication errors in certain situations. It can also be used to create a new Slave from a snapshot of the Master as mentioned in MySQL the documentation.