Detecting GTID Gaps Before PITR Replay #

A gap in the archived binlog chain is the most dangerous defect in a recovery program because replaying a gapped chain can succeed — mysqlbinlog happily applies the segments it has, silently skipping the transactions it does not, and the recovered database is missing writes with no error to warn you. The only way to catch this is arithmetic performed before replay: prove that the union of the archived segments’ GTID intervals is contiguous from the base-backup coordinate to the target, with nothing missing. This page builds that gap detector — a pure computation over the manifest that reads no event bodies and mutates nothing, yet is the single check that separates “we can recover” from “we think we can recover.” It is the highest-value gate in Recovery Validation Gates.

Context & Prerequisites #

This is the focused contiguity check referenced throughout Point-in-Time Recovery Workflow Automation and its drills. You need MySQL 8.0.22+ (for GTID_SUBTRACT, run against any reachable instance as a calculator), Python 3.10+, and the segment manifest with per-object GTID intervals from Automated Binlog Archiving to Object Storage. The base coordinate comes from the snapshot, per Snapshot & Binlog Coordination. This depends on a gap-free upstream pipeline as designed in GTID Tracking & Enforcement.

Step-by-Step Implementation #

1. Build the required interval #

The required set spans from the base coordinate (exclusive) to the target (inclusive). Anything in this range that the archive lacks is a gap.

# Python 3.10+
def required_interval(base_gtid: str, target_gtid: str) -> tuple[str, str]:
    # required = everything the target has that the base floor does not.
    return base_gtid, target_gtid       # arithmetic done in SQL via GTID_SUBTRACT

PITR relevance: framing the check as a set difference makes it exact — you are not sampling or heuristically comparing filenames, you are proving a mathematical property of the GTID sets.

2. Union the archived intervals #

Fold the manifest’s per-segment intervals into a single GTID set representing everything the archive can supply.

# Python 3.10+
def archive_union(conn, segments) -> str:
    cur = conn.cursor()
    have = ""
    for seg in segments:
        cur.execute("SELECT GTID_SUBTRACT_UNION(%s, %s)"
                     if _has_union_fn(conn) else "SELECT %s", (have, seg.gtid))
        # portable fallback: accumulate with GTID_SUBTRACT-based merge
        have = _merge(have, seg.gtid, cur)
    cur.close()
    return have

PITR relevance: the union is “everything recovery could apply.” Comparing it to the requirement is what turns a pile of objects into a proven-or-disproven chain.

3. Compute the missing set #

Subtract what the archive has from what the target needs. An empty result is a recoverable chain; anything else names the gap.

-- MySQL 8.0.22+  — missing = required minus (base ∪ archive)
SELECT GTID_SUBTRACT(:target_gtid,
         GTID_SUBTRACT_UNION(:base_gtid, :archive_union)) AS missing;

PITR relevance: the missing set is the precise list of transactions a recovery would silently drop. If it is non-empty, the recovery to that target is impossible, full stop — and now you know before the outage, not during it.

4. Fail closed and report the gap #

Refuse to replay when the missing set is non-empty, and surface exactly which interval is absent.

# Python 3.10+
def assert_recoverable(missing: str) -> None:
    if missing.strip():
        raise RuntimeError(f"unrecoverable: missing GTID interval(s) {missing}")

PITR relevance: a fail-closed detector converts a silent data-loss bug into a loud, actionable alert that names the missing transactions, which is what lets you re-archive or adjust the target before committing to a recovery.

Configuration Snippet & Reference Table #

Verification Checklist #

• The base floor is the snapshot’s captured GTID set, not an assumed value.
• Every manifest interval is folded into the archive union, including all server_uuid sources.
• The missing set is computed as GTID_SUBTRACT(target, base ∪ archive).
• An empty missing set is required before any replay is permitted.
• A non-empty missing set names the exact absent interval in the alert.
• A deliberately removed test segment is detected as a gap by the check.

Gotchas & Version-Specific Caveats #

Filename contiguity is not GTID contiguity. mysql-bin.000041, 000042, 000043 looks complete, but a segment can be present and still leave a GTID hole if the archiver skipped a rotation. Always diff GTID sets, never file sequences.

Multi-source sets must all be unioned. In a multi-server_uuid topology, missing one source’s intervals from the union produces a false gap; include every source.

GTID_SUBTRACT_UNION availability varies. It exists in modern MySQL; where unavailable, accumulate the union with repeated GTID_SUBTRACT-based merges, which the portable fallback above does.

A replay can succeed over a gap. This is the whole reason the check exists: mysqlbinlog does not error on a missing intermediate segment, it just applies what it is given. Never rely on replay to surface a gap.

Frequently Asked Questions #

Related #

• Recovery Validation Gates — the full gate ladder this contiguity check anchors.
• Snapshot & Binlog Coordination — where the base floor coordinate comes from.
• GTID Tracking & Enforcement — the upstream pipeline that keeps the chain gap-free in the first place.

Back to Recovery Validation Gates.