Diagnosing gtid_purged Mismatches During Recovery #

The replay dies on the first archived segment with ERROR 1840, a gtid_next rejection, or a duplicate-key error, and every one of those symptoms points to the same root cause: the recovery target’s GTID state disagrees with where the archived chain begins. Either gtid_purged was never seeded after the restore, or it was seeded from the wrong coordinate, so the target thinks it has executed transactions the chain is about to replay — or the reverse. This page is the diagnostic: read the target’s gtid_executed and gtid_purged, compare them to the base backup’s captured coordinate and the archive’s first interval, identify which of the three mismatch shapes you have, and reseed the floor so the replay lines up.

Context & Prerequisites #

This diagnoses failures in the GTID coordinate model from GTID Tracking & Enforcement for Binary Log Archiving and PITR Automation, and it is the failure most often hit while executing Point-in-Time Recovery Workflow Automation. You need MySQL 8.0.22+ with gtid_mode=ON, Python 3.10+, the base backup coordinate, and the archived manifest’s first interval from Automated Binlog Archiving to Object Storage. The seeding step mirrors Aligning XtraBackup Snapshots with gtid_purged.

Step-by-Step Implementation #

1. Read the target’s current GTID state #

Capture what the target believes it has executed and what it treats as purged history.

-- MySQL 8.0.22+  — on the failing recovery target
SELECT @@GLOBAL.gtid_executed AS executed;
SELECT @@GLOBAL.gtid_purged   AS purged;

PITR relevance: gtid_executed is what the target will refuse to re-apply; gtid_purged is the floor it was told is already done. A mismatch between these and the chain is the whole problem.

2. Compare against the base coordinate and chain start #

Diff the three coordinates to classify the mismatch.

# Python 3.10+
def classify_mismatch(executed: str, base_gtid: str, first_seg_gtid: str, conn) -> str:
    cur = conn.cursor()
    cur.execute("SELECT GTID_SUBTRACT(%s, %s)", (first_seg_gtid, executed))
    chain_new = (cur.fetchone()[0] or "")
    cur.execute("SELECT GTID_SUBTRACT(%s, %s)", (executed, base_gtid))
    extra_on_target = (cur.fetchone()[0] or "")
    cur.close()
    match (bool(chain_new), bool(extra_on_target)):
        case (False, _):
            return "overlap"        # first segment already in executed → double-apply
        case (True, True):
            return "wrong_floor"    # target executed more/less than the base coordinate
        case _:
            return "clean"          # chain is all-new relative to executed

PITR relevance: naming the mismatch shape tells you the fix — an overlap needs the floor raised, a wrong floor needs a reset and reseed, a gap needs the chain, not the floor, corrected.

3. Fix an unseeded or wrong floor #

If gtid_purged is empty or wrong, reset GTID state and reseed to exactly the base backup coordinate.

-- MySQL 8.0.22+  — reseed the floor on the target
RESET BINARY LOGS AND GTID_SET;                 -- 8.4 spelling; 8.0: RESET MASTER
SET @@GLOBAL.gtid_purged = '3E11FA47-...:1-400000';   -- the base backup's coordinate
SELECT @@GLOBAL.gtid_executed;                  -- must equal the seeded set

PITR relevance: seeding the floor to the backup coordinate makes the target agree that history up to the coordinate is done, so replay of the chain’s first (post-coordinate) transaction is accepted.

4. Re-verify the seam before retrying #

Confirm the chain’s first interval is now all-new relative to gtid_executed.

-- MySQL 8.0.22+  — must be non-empty (all new) with no overlap
SELECT GTID_SUBTRACT(:first_seg_gtid, @@GLOBAL.gtid_executed) AS should_be_all_new;
SELECT GTID_SUBTRACT(@@GLOBAL.gtid_executed, :first_seg_gtid) AS should_be_empty;

PITR relevance: a clean seam here means the reseed worked and the replay will proceed without a gtid_next rejection.

Configuration Snippet & Reference Table #

Verification Checklist #

• gtid_executed and gtid_purged on the target were read before changing anything.
• The mismatch shape (overlap, wrong floor, gap) is identified, not guessed.
• For a floor fix, the target is reset before gtid_purged is set.
• gtid_purged is seeded to exactly the base backup’s captured coordinate.
• After reseeding, gtid_executed equals the seeded set.
• The chain’s first interval is all-new relative to gtid_executed (no overlap) before retrying.

Gotchas & Version-Specific Caveats #

A gap is not a floor problem. If the mismatch is a real gap — the chain is missing transactions between the floor and the target — reseeding will not help; you must correct the chain, per Detecting GTID Gaps Before PITR Replay. Do not paper over a gap by lowering the floor.

RESET MASTER renamed on 8.4. Use RESET BINARY LOGS AND GTID_SET; the old spelling works on 8.0 but is not canonical on 8.4.

Setting gtid_purged requires empty gtid_executed. ERROR 1840/3546 mean the target already executed something — often a stray write or a partial prior replay. Provision cleanly and reset first.

File:position coordinates can’t seed a GTID floor. If the backup only recorded a file:position, there is no GTID set to seed; the source needed gtid_mode=ON, per GTID Tracking & Enforcement.

Frequently Asked Questions #

Related #

• GTID Tracking & Enforcement — the coordinate model these mismatches violate.
• Aligning XtraBackup Snapshots with gtid_purged — seeding the floor correctly the first time.
• Detecting GTID Gaps Before PITR Replay — distinguishing a gap from a floor mismatch.

Back to GTID Tracking & Enforcement.