Documentation Home
MySQL Replication
Related Documentation Download this Excerpt
PDF (US Ltr) - 1.4Mb
PDF (A4) - 1.4Mb
HTML Download (TGZ) - 326.4Kb
HTML Download (Zip) - 332.7Kb


MySQL Replication  /  ...  /  Skipping Transactions With GTIDs

2.7.3.1 Skipping Transactions With GTIDs

When GTIDs are in use (gtid_mode is ON), the GTID for a committed transaction is persisted on the slave even if the content of the transaction is filtered out. This feature prevents a slave from retrieving previously filtered transactions when it reconnects to the master using GTID auto-positioning. It can also be used to skip a transaction on the slave, by committing an empty transaction in place of the failing transaction.

If the failing transaction generated an error in a worker thread, you can obtain its GTID directly from the LAST_SEEN_TRANSACTION field in the Performance Schema table replication_applier_status_by_worker. To see what the transaction is, issue SHOW RELAYLOG EVENTS on the slave or SHOW BINLOG EVENTS on the master, and search the output for a transaction preceded by that GTID.

When you have assessed the failing transaction for any other appropriate actions as described previously (such as security considerations), to skip it, commit an empty transaction on the slave that has the same GTID as the failing transaction. For example:

SET GTID_NEXT='aaa-bbb-ccc-ddd:N';
BEGIN;
COMMIT;
SET GTID_NEXT='AUTOMATIC';

The presence of this empty transaction on the slave means that when you issue a START SLAVE statement to restart replication, the slave uses the auto-skip function to ignore the failing transaction, because it sees a transaction with that GTID has already been applied. If the slave is a multi-source replication slave, you do not need to specify the channel name when you commit the empty transaction, but you do need to specify the channel name when you issue START SLAVE.

Note that if binary logging is in use on this slave, the empty transaction will enter the replication stream if the slave becomes a master or primary in the future. If you need to avoid this possibility, consider flushing and purging the slave's binary logs, as in this example:

FLUSH LOGS;
PURGE BINARY LOGS TO 'binlog.000146';

The GTID of the empty transaction is persisted, but the transaction itself is removed by purging the binary log files.