Building a Dead-Letter Queue for Failed Binlog Uploads #

An archiver that retries a failing upload forever eventually blocks the whole pipeline behind one poisoned segment; an archiver that drops a failing upload after N tries silently punches a hole in the recovery chain. Neither is acceptable when the payload is a binary log segment that a future point-in-time recovery depends on. The correct design routes a segment that exhausts its retries into a dead-letter queue (DLQ): it is removed from the hot path so healthy segments keep flowing, but it is preserved, alerted on, and recoverable, and — critically — the cursor never advances past it, so the gap is visible rather than silent. This page builds that DLQ: the transient-versus-permanent failure taxonomy that decides what dead-letters, the record schema that makes a dead-lettered segment reprocessable, and the reprocessing path that reinserts it without breaking order.

Context & Prerequisites #

This extends the retry taxonomy in Error Handling & Retry Logic for MySQL Binary Log Archiving and PITR Automation; read that for the backoff and classification model the DLQ terminates. You need Python 3.10+ with tenacity, the archiving daemon from Automated Binlog Archiving to Object Storage, and durable storage for DLQ records. The ordering guarantee the DLQ must preserve comes from the strict-FIFO commit rule in Async Processing & Queue Management.

Step-by-Step Implementation #

1. Classify the failure: transient vs permanent #

Only permanent failures dead-letter; transient ones stay on the retry path. Misclassifying a transient error as permanent dead-letters needlessly; the reverse retries forever.

# Python 3.10+
class Transient(RuntimeError): ...     # 5xx, throttling, timeouts — retry
class Permanent(RuntimeError): ...     # checksum mismatch, 4xx auth, corrupt source — DLQ

def classify(exc: Exception) -> type:
    match exc:
        case _ if _is_throttle_or_5xx(exc):
            return Transient
        case _:
            return Permanent

PITR relevance: a checksum mismatch is permanent — retrying cannot repair a corrupt payload — so it must dead-letter and page, not spin, or archiving lag climbs while the segment never lands.

2. Define a reprocessable DLQ record #

A dead-lettered segment must carry everything needed to retry it later: its name, GTID interval, source path, digest, failure reason, and attempt count.

# Python 3.10+
from dataclasses import dataclass, asdict
import json, time
from pathlib import Path

@dataclass(slots=True, frozen=True)
class DeadLetter:
    segment: str
    gtid: str
    source_path: str
    sha256: str
    reason: str
    attempts: int
    dead_lettered_at: float

def dead_letter(dlq_dir: Path, rec: DeadLetter) -> None:
    p = dlq_dir / f"{rec.segment}.json"
    tmp = p.with_suffix(".tmp")
    tmp.write_text(json.dumps(asdict(rec)))
    tmp.replace(p)      # atomic

PITR relevance: preserving the GTID interval and source path means a dead-lettered segment can be re-archived from the local file while it still exists, closing the gap before retention purges it.

3. Remove from the hot path without advancing the cursor #

Dead-lettering takes the segment off the worker queue but must leave the durable cursor pointing before it, so the chain gap stays visible to the validation gates.

# Python 3.10+
def handle_permanent(seg, dlq_dir, cursor, alert) -> None:
    dead_letter(dlq_dir, seg.to_dead_letter())
    alert.page(f"binlog segment {seg.segment} dead-lettered: {seg.reason}")
    # NB: cursor is NOT advanced — the segment remains unconfirmed / a known gap

PITR relevance: an unadvanced cursor is what turns a silent data-loss bug into a loud, detectable gap that Detecting GTID Gaps Before PITR Replay will catch.

4. Reprocess in order after fixing the root cause #

Once the cause is resolved, reinsert dead-lettered segments in GTID order at the front of the pipeline, re-verifying each before the cursor may pass it.

PITR relevance: reprocessing in order preserves the sequential dependency mysqlbinlog relies on, so a repaired segment slots back into a replayable chain rather than an out-of-order one.

Configuration Snippet & Reference Table #

Verification Checklist #

• Transient failures retry; only permanent failures dead-letter.
• A checksum mismatch dead-letters and pages rather than retrying.
• DLQ records are written atomically and carry the segment’s GTID interval and source path.
• The cursor is not advanced past a dead-lettered segment (gap stays visible).
• Dead-lettering a segment pages, treating it as a recovery-readiness incident.
• Reprocessing reinserts segments in GTID order and re-verifies each checksum.

Gotchas & Version-Specific Caveats #

Do not advance the cursor to “unblock” the pipeline. Skipping a dead-lettered segment to keep archiving flowing hides the gap and is the exact silent-data-loss failure the DLQ exists to prevent. Let healthy segments flow around it, but keep the cursor honest.

Re-archive before retention purges the source. A dead-lettered segment can only be re-archived from the local file if it still exists; if binlog_expire_logs_seconds purges it first, the gap becomes permanent. Set retention with margin, per Binlog Retention Boundaries.

Poison messages need an attempt cap. Without a max-attempts bound, a genuinely permanent failure can masquerade as transient and never dead-letter; cap attempts and dead-letter on exhaustion.

Reprocessing out of order breaks replay. Reinsert dead-lettered segments by GTID order, not by when they were fixed, or the chain becomes non-contiguous for mysqlbinlog.

Frequently Asked Questions #

Related #

• Error Handling & Retry Logic — the retry and classification model the DLQ terminates.
• Handling S3 Throttling During High-Throughput Binlog Archiving — the transient failures that should never reach the DLQ.
• Detecting GTID Gaps Before PITR Replay — the gate that catches a dead-lettered segment’s gap.

Back to Error Handling & Retry Logic.