DBAtlas · Database operations atlas

Find and neutralize errant GTID transactions

MySQLReplication

Command

-- 1) On the source: get gtid_executed
SELECT @@GLOBAL.gtid_executed;

-- 2) On the replica: GTIDs missing on the source (empty result = clean)
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed,
       'af6bc361-c353-11f1-8f12-f69e63ad6c0e:1-13') AS errant_gtids;

-- 3) If the content is harmless: commit an empty transaction per errant GTID on the source
SET GTID_NEXT = 'b030e5a6-c353-11f1-a887-523817ec642a:1'; BEGIN; COMMIT;
SET GTID_NEXT = 'b030e5a6-c353-11f1-a887-523817ec642a:2'; BEGIN; COMMIT;
SET GTID_NEXT = 'AUTOMATIC';

What it does

Detects transactions executed directly on a replica with no counterpart on the source (errant) via GTID set difference, and aligns the GTID sets across the topology by marking them with empty transactions on the source.

When to use it

As a routine pre-failover / switchover check; when Orchestrator or MySQL Shell refuses to promote a replica because of errant GTIDs, or a replica fails with 1236 when attaching to a new source.

Caution

First find out what the errant transaction did from the replica's binlog (mysqlbinlog --include-gtids=...). If it changed data, the empty transaction only papers over the GTID and the data difference remains; rebuild the replica (CLONE) or align the data manually. Empty transactions committed on the source propagate to every replica; that is intended. Keep super_read_only=ON on replicas to prevent recurrence.

Open in DBAtlas →

Related