MySQL GTID Errant Transactions: Find and Fix Them Before Failover

GTID-based replication has one promise at its core: every transaction has a globally unique identity, so any server can compute exactly what any other server is missing and send it. That promise is also the trap. The moment a write happens directly on a replica, that replica owns a GTID that exists nowhere else in the topology. MySQL calls this an errant transaction, and it is the single most common reason a clean-looking GTID failover turns into an outage.

The cruel part is the timing. An errant transaction causes no error when it happens. Replication keeps running. Dashboards stay green. It detonates weeks later, during a failover, when the server carrying it becomes the source and every other replica in the topology asks it for transactions they have never seen. This article covers how errant transactions get created, how to detect them with GTID set functions, how to fix them safely, and why failover tooling like Orchestrator cares so much about them.

How errant transactions happen

Nobody creates an errant transaction on purpose. The recurring sources I have seen in real fleets:

• Manual fixes on a replica: a DBA deletes a duplicate row or patches drifted data directly on the replica to clear a replication error, with binary logging enabled. The fix works; the GTID stays.
• read_only without super_read_only: read_only=ON does not stop users with elevated privileges. Until super_read_only is also ON, every admin session is one typo away from an errant write.
• Tooling that writes where it runs: backup wrappers, schema-migration tools, monitoring agents that maintain their own state tables, cron jobs pointed at the wrong host.
• The event scheduler: events replicated from the source are correctly disabled on replicas, but events created directly on the replica run and write locally.
• Restores and clones: restoring a dump into a replica without resetting GTID state, or seeding a server whose gtid_purged does not match reality.

The common thread: the write itself is often harmless or even correct. What is dangerous is the GTID bookkeeping that now disagrees across the topology.

Finding errant transactions with GTID set functions

Detection is a set comparison. A replica is clean when its executed GTID set is a subset of the source's executed GTID set. MySQL ships two functions for this. GTID_SUBSET(a, b) returns 1 if every GTID in a is also in b. GTID_SUBTRACT(a, b) returns the GTIDs in a that are not in b — the errant set itself.

First collect gtid_executed from the source and from each replica, then run the comparison anywhere:

-- On each server:
SELECT @@GLOBAL.gtid_executed;

-- Comparison (paste the two sets):
SELECT GTID_SUBSET(
  'replica-gtid-executed-here',
  'source-gtid-executed-here'
) AS replica_is_clean;

SELECT GTID_SUBTRACT(
  '3e11fa47-71ca-11e1-9e33-c80aa9429562:1-877,bb6c5da2-3a1c-11ec-9018-0242ac110002:1-5',
  '3e11fa47-71ca-11e1-9e33-c80aa9429562:1-877'
) AS errant_gtids;

The subtraction result is the actionable artifact: a set like bb6c5da2-...:1-5 whose server UUID tells you which server originated the writes, and whose transaction range tells you how many there are. If the UUID belongs to the replica itself, someone wrote to it directly. If it belongs to a server that is no longer in the topology, you are looking at archaeology from an old failover.

Then find out what the errant transactions actually did, because the fix depends on it. If the binary logs are still on the replica, locate and inspect the exact range:

-- Find which binlog file covers the errant range:
SHOW BINARY LOGS;
SHOW BINLOG EVENTS IN 'binlog.000042' LIMIT 50;
-- Then from the shell, extract just those transactions:
--   mysqlbinlog --include-gtids='bb6c5da2-3a1c-11ec-9018-0242ac110002:1-5' binlog.000042

Run this comparison on a schedule, not just before failovers. An errant transaction found the day it happens is a five-minute conversation with whoever ran it. One found during an incident is forensic work under pressure. The deeper mechanics of gtid_executed and gtid_purged — and how the model compares to PostgreSQL's — are covered in GTID vs replication slots.

Why errant transactions break promotions

Walk through a failover. The source dies. The replica carrying errant set bb6c5da2:1-5 is the most current, so tooling promotes it. Every other replica now connects to it with auto-positioning and sends its own gtid_executed; the new source computes the difference and must send everything each replica lacks — including bb6c5da2:1-5.

Three outcomes, all bad. If the binlog containing those transactions still exists on the new source, the replicas apply five transactions that were never supposed to be part of the dataset — silent data divergence at best, duplicate-key errors that halt replication at worst. If that binlog was purged, replication fails immediately with the infamous error 1236 — the source has purged binary logs containing GTIDs the replica requires — and every replica in the fleet stops at once, during a failover, when you have exactly one server left. And if the errant write conflicts with rows that later changed through normal replication, the apply errors are non-obvious and tend to get "fixed" with more direct writes to replicas, compounding the original sin.

This is why failover orchestrators refuse to be casual about it. Orchestrator explicitly checks candidate replicas for errant GTIDs and will flag or block promotions, because promoting a server with errant transactions poisons the topology in a way that is very hard to walk back. How Orchestrator and Patroni differ on this class of problem is the subject of Orchestrator vs Patroni failover.

Fixing errant transactions before they matter

There are two sanctioned fixes, and choosing between them requires knowing what the errant transactions did.

Option 1: inject empty transactions on the source. This makes the rest of the topology claim the errant GTIDs without executing their content. After this, GTID sets agree everywhere and failover is safe. It is the right fix when the errant write was harmless (a tooling state table, an already-reverted experiment) or when its local effect on the replica is acceptable to keep.

-- On the current source, once per errant GTID:
SET gtid_next = 'bb6c5da2-3a1c-11ec-9018-0242ac110002:1';
BEGIN; COMMIT;
SET gtid_next = 'bb6c5da2-3a1c-11ec-9018-0242ac110002:2';
BEGIN; COMMIT;
-- ...repeat through :5, then:
SET gtid_next = 'AUTOMATIC';

Note what this does not do: it does not undo the errant write on the replica. If that write changed real data, the replica still differs from the source, and only a table checksum comparison will tell you by how much. Empty transactions fix the GTID bookkeeping, not the drift.

Option 2: remove the errant state from the offending replica. If the errant write must not survive anywhere, revert its data effect manually — with sql_log_bin=0 in the session, so the revert does not itself become a new errant transaction — and then rebuild the replica's GTID state. In practice that means re-seeding the replica from a fresh backup or clone of the source, because surgically rewriting gtid_executed via RESET MASTER and gtid_purged is easy to get wrong under pressure. Re-seeding is boring, and boring is the point.

Prevention is configuration, not discipline

Every prevention that relies on humans remembering will eventually fail. The ones that work are mechanical. Run super_read_only=ON on every replica, always, flipped off only by the promotion automation itself. Make sql_log_bin=0 a standard part of any approved break-glass procedure on a replica. Schedule a topology-wide GTID_SUBTRACT check and alert on any non-empty result, treating it with the urgency of a failed backup. And enforce the cultural rule that replication problems are fixed by changes on the source or by re-syncing the replica — never by writing to the replica — because that habit is where most errant transactions are born.

Where MonPG fits

Full disclosure on tooling: MonPG is a PostgreSQL monitoring platform today, and MySQL support is under active development — see MySQL monitoring (coming soon). Continuous errant-transaction detection is exactly the kind of check we are building into it, because it is cheap to run and catastrophic to skip. Everything in this article works with stock MySQL and a cron job in the meantime. If your team also operates PostgreSQL, you can start there now and get replication and failover-readiness evidence in the same spirit.