How To Skip Replication Errors in GTID-Based Replication

December 8, 2022

Author

Mani Paluru

Insight for DBAs

MySQL

Share this Post:

Replication Errors in GTID-Based Replication 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.

replica1 > SHOW SLAVE STATUSG<br>Last_SQL_Errno: 1032<br>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<br>

1	replica1 > SHOW SLAVE STATUSG<br>Last_SQL_Errno: 1032<br>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<br>

Let’s try skipping the error with SQL_SLAVE_SKIP_COUNTER:

replica1 > STOP SLAVE;<br>Query OK, 0 rows affected (0.02 sec)<br><br>replica1  > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;<br>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<br>

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:

replica1 > SELECT @@gtid_mode;<br>+---------------------+<br>| @@gtid_mode         |<br>+---------------------+<br>|  ON                 |<br>+---------------------+<br>replica1 > STOP SLAVE;<br>Query OK, 0 rows affected (0.02 sec)<br><br>replica1 > SET GTID_MODE=ON_PERMISSIVE;<br>Query OK, 0 rows affected (0.04 sec)<br><br>replica1 > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;<br>Query OK, 0 rows affected (0.00 sec)<br><br>replica1 > START SLAVE;<br>Query OK, 0 rows affected (0.00 sec)<br><br>replica1 > pager grep Seconds<br>PAGER set to 'grep Seconds'<br>replica1 > SHOW SLAVE STATUSG<br>        Seconds_Behind_Master: 0<br>1 row in set (0.00 sec)<br>

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.

replica1 > SELECT @@GTID_MODE;<br>+---------------------- ----+<br>| @@gtid_mode               |<br>+---------------------------+<br>| ON_PERMISSIVE             |<br>+---------------------------+<br><br>replica1 > SET GLOBAL GTID_MODE=ON;<br>Query OK, 0 rows affected (0.04 sec)<br>

1	replica1 > SELECT @@GTID_MODE;<br>+---------------------- ----+<br>\| @@gtid_mode \|<br>+---------------------------+<br>\| ON_PERMISSIVE \|<br>+---------------------------+<br><br>replica1 > SET GLOBAL GTID_MODE=ON;<br>Query OK, 0 rows affected (0.04 sec)<br>

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.

replica1 > STOP SLAVE;<br>Query OK, 0 rows affected (0.02 sec)<br><br>replica1 > SET GTID_MODE=ON_PERMISSIVE;<br>Query OK, 0 rows affected (0.04 sec)<br><br>replica1 > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;<br>Query OK, 0 rows affected (0.00 sec)<br><br>replica1 > SET GLOBAL GTID_MODE=ON;<br>ERROR 3111 (HY000): SET @@GLOBAL.GTID_MODE = ON is not allowed because @@GLOBAL.SQL_SLAVE_SKIP_COUNTER is greater than zero.<br><br>replica1 > START SLAVE;<br>Query OK, 0 rows affected (0.00 sec)<br><br>replica1 > SET GLOBAL GTID_MODE=ON;<br>Query OK, 0 rows affected (0.02 sec)<br>

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?