Consistency Boundaries in MySQL CDC: Snapshots, Offsets, and Idempotency

Scope: the concepts and documented semantics of MySQL 8.0, Debezium 2.7, and Kafka 3.9. This is a design discussion, not a production configuration checklist.

Getting database changes into a message queue is usually the easy part. Recovery exposes the harder questions: What happens when an update arrives twice? Can changes made during a snapshot be missed? Could a late, older update recreate an object that has already been deleted?

Distinguish three kinds of position

The source binlog position, the offset persisted by the connector, and the downstream Kafka consumer offset answer different questions. They are not interchangeable, and progress in any one of them does not prove that the business result is correct.

Debezium’s initial snapshot establishes a baseline before streaming changes from the log. Recovery can repeat events. If the binlog history needed for recovery has already been purged, the connector cannot reconstruct it from nothing. Snapshot and failure behavior should be checked for the selected mode in the Debezium 2.7 MySQL connector documentation .

Operational planning therefore needs the source-log retention window, maximum tolerable downtime, connector lag, downstream backlog, and the time required to rebuild a baseline. A seven-day binlog retention setting is only a configuration value until a recovery exercise demonstrates what it actually supports.

Identify the entity and the change separately

For an order-state table, a normalized change event might look like this:

{
  "entity_key": "order:1001",
  "event_id": "source-a:tx-17:row-2",
  "version": 12,
  "operation": "upsert",
  "value": {"status": "PAID"}
}

This is an illustrative format. event_id must come from a real, stable event-identity rule. version represents a monotonically increasing version maintained explicitly by the upstream application; it is not a universal sequence number supplied by Debezium. A binlog filename or timestamp cannot be assumed to provide a total order across failover, snapshot rebuilding, and multiple sources.

For a sink that stores the latest state, define the rules explicitly: a higher version replaces a lower one; the same version with identical content is a replay; the same version with different content is a conflict to investigate; and lower versions are ignored and counted. The comparison and write must be atomic, or concurrent consumers can still overwrite state incorrectly.

For an aggregate that increments on every change, overwriting by entity key is insufficient. Record processed event IDs and commit the deduplication record and aggregate update in the same transaction, or use a processing system that can commit state and progress together. The deduplication retention period must cover the possible replay window. Replaying after those records expire can cause duplicate increments again.

Deletes and reordering need explicit rules

Physically deleting a target row also removes its version. A later replay of an old update can then insert the row again. One option is to retain a logical deletion marker with its version until the replay boundary has passed. This application-level deletion marker is different from a null-valued tombstone in a compacted Kafka topic.

Also cover primary-key changes, snapshot read events, schema changes, and parsing failures. Silently skipping an unreadable field can make lag look healthy while the sink is missing rows. When isolating failed events, retain sanitized context, error classification, and source position so the data can be replayed after a fix.

Kafka transactions do not close every boundary

Kafka transactions can coordinate output records and consumer positions within Kafka. Writing to an external database, search index, or HTTP service requires cooperation from the destination. The boundary is described in Kafka 3.9 message delivery semantics .

For example, the destination database may commit successfully just before the consumer crashes, before its Kafka offset is committed. On recovery, the consumer reads the same event again. A version-conditional, idempotent write can prevent that replay from creating an additional business effect. Committing the offset before writing to the database creates the opposite failure window: progress is recorded, but the result is absent.

Validate with a failure matrix

I prefer the following experiments to a single comparison of total row counts:

Finally, reconcile counts, important fields, and deletion state by primary-key range, and retain a small sample of row-level differences. Equal total counts cannot expose a missing row canceled out by a duplicate elsewhere. A dependable CDC pipeline must let an operator explain, recover, and reconcile business-state changes after a failure.

References

• Debezium 2.7: MySQL Connector, Snapshots, Change Events, and Failure Handling
• Apache Kafka 3.9: Message Delivery Semantics