In this blog, I’m going to discuss how to easily skip the replication errors in GTID (Global Transaction Identifier)-based replication.
In the MySQL world, if replication is broken we all use the famous SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; to skip the replication error. It always works if it’s a traditional binlogs events-based replication and is helpful to quickly resume the replication. But does this same method work for GTID-based replication? Let’s see.
In GTID-based replication, skipping a replication error in a broken replica is not as simple as binlogs-based replication. We have one old blog post — Repair MySQL 5.6 GTID replication by injecting empty transactions —where one of our colleagues showed us one way to skip and fix the replication.
Let’s get into action and see another way of skipping an errored transaction in GTID replication.
1 2 3 | replica1 > SHOW SLAVE STATUSG Last_SQL_Errno: 1032 Last_SQL_Error: Could not execute Delete_rows event on table work.emp; Can't find record in 'emp', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000001, end_log_pos 6094 |
Let’s try skipping the error with SQL_SLAVE_SKIP_COUNTER:
1 2 3 4 5 | replica1 > STOP SLAVE; Query OK, 0 rows affected (0.02 sec) replica1 > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction |
As you can see we are unable to skip the error and we got an error message while skipping with SQL_SLAVE_SKIP_COUNTER. As per the error message, it was unable to skip as this replica is running with GTID_MODE = ON.
Let’s test the trick and skip this error:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | replica1 > SELECT @@gtid_mode; +---------------------+ | @@gtid_mode | +---------------------+ | ON | +---------------------+ replica1 > STOP SLAVE; Query OK, 0 rows affected (0.02 sec) replica1 > SET GTID_MODE=ON_PERMISSIVE; Query OK, 0 rows affected (0.04 sec) replica1 > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; Query OK, 0 rows affected (0.00 sec) replica1 > START SLAVE; Query OK, 0 rows affected (0.00 sec) replica1 > pager grep Seconds PAGER set to 'grep Seconds' replica1 > SHOW SLAVE STATUSG Seconds_Behind_Master: 0 1 row in set (0.00 sec) |
Here we have stopped the replication and changed the GTID_MODE from ON to ON_PERMISSIVE (New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions) and skipped it using SQL_SLAVE_SKIP_COUNTER = 1 and started the replication.
Once replication is caught up, we need to set the GTID_MODE back to ON.
1 2 3 4 5 6 7 8 9 | replica1 > SELECT @@GTID_MODE; +---------------------- ----+ | @@gtid_mode | +---------------------------+ | ON_PERMISSIVE | +---------------------------+ replica1 > SET GLOBAL GTID_MODE=ON; Query OK, 0 rows affected (0.04 sec) |
The SQL SQL_SLAVE_SKIP_COUNTER must be defined as 0 in order for MySQL to permit changing the GTID_MODE from ON_PERMISSIVE to ON.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | replica1 > STOP SLAVE; Query OK, 0 rows affected (0.02 sec) replica1 > SET GTID_MODE=ON_PERMISSIVE; Query OK, 0 rows affected (0.04 sec) replica1 > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; Query OK, 0 rows affected (0.00 sec) replica1 > SET GLOBAL GTID_MODE=ON; ERROR 3111 (HY000): SET @@GLOBAL.GTID_MODE = ON is not allowed because @@GLOBAL.SQL_SLAVE_SKIP_COUNTER is greater than zero. replica1 > START SLAVE; Query OK, 0 rows affected (0.00 sec) replica1 > SET GLOBAL GTID_MODE=ON; Query OK, 0 rows affected (0.02 sec) |
According to the MySQL documentation, there are four different GTID modes available as noted below
- OFF: Both new and replicated transactions must be anonymous.
- OFF_PERMISSIVE: New transactions are anonymous. Replicated transactions can be either anonymous or GTID transactions.
- ON_PERMISSIVE: New transactions are GTID transactions. Replicated transactions can be either anonymous or GTID transactions.
- ON: Both new and replicated transactions must be GTID transactions.
Conclusion: Changing the GTID_MODE online is available since MySQL 5.7.6, and hope this method helps in quickly skipping the errors. Please be aware that you are making data inconsistent between the primary and replica by skipping a transaction, so we might have to fix the data inconsistency. pt-table-checksum and pt-table-sync can help you with fixing data inconsistency.
Looking for database support your team can count on?
I’m not sure what to think about this post.
That’s not the recommended method – The documentation has a very clear example of how to skip transactions when GTID is enabled, without messing around with the modes globally (notice how that “old” method has been moved down in the procedure list).
https://dev.mysql.com/doc/refman/8.0/en/replication-administration-skip.html
Hey Francisco, you’re barely right, therefore, would you mind specifying exactly where are the right steps for the specific issue?
You pointed out almost everything. Please be more specific, if you don’t mind, otherwise I’ll mess around everything anyway.
Read the referenced Oracle MySQL page, you have the procedure step by step in that page.
The method described in this post is, per Oracle documentation, to be used when GTID is not enabled… not when GTIDs are enabled. For GTID enabled servers the procedure is 17.1.7.3.1.
17.1.7.3.1 Skipping Transactions With GTIDs17.1.7.3.2 Skipping Transactions Without GTIDs17.1.7.3.2.1 Skipping Transactions With
SET GLOBAL sql_slave_skip_counter17.1.7.3.2.2 Skipping Transactions With
CHANGE MASTER TO