Asymmetric Replication Topology Failure

When a read replica diverges from the primary by silently skipping or misapplying transactions, queries to the replica return factually incorrect data with no error, no exception, and no replication error in the logs. The divergence accumulates undetected over days or weeks until a data audit reveals the discrepancy. All reads served from the diverged replica during this window are potentially incorrect, and no automated mechanism in the replication layer detects or corrects the divergence.

Statement-based replication (MySQL SBR) and row-based replication (MySQL RBR, PostgreSQL logical replication) both carry the risk of silent divergence under specific conditions. In MySQL row-based replication, the replica applies a row-change event by locating the target row using a primary key or unique key lookup and applying the delta. If the replica's row does not match what the primary expected (due to a previous incorrect application, manual modification, or software bug), the replica may: (a) silently overwrite the row with the wrong version, (b) skip the row update because it doesn't find the expected row, or (c) throw a duplicate key error and stop replication: which is the detectable failure mode. The silent cases are more dangerous.

Manual modifications on replicas are the most common cause. An operator runs a data fix directly on the replica (not the primary) to resolve an incident. This puts the replica out of sync with the primary. Subsequent replication events from the primary may conflict with the manual modification and be silently skipped (if slave_exec_mode=IDEMPOTENT in MySQL) or cause incorrect row states (if the delta is applied to the wrong base value). The operator may believe the fix was successful, not realizing that subsequent replication events are operating on incorrect state.

Software bugs in the database engine can also cause silent divergence. MySQL has had documented bugs where row-format replication events are misapplied under specific conditions (multi-column primary keys, generated columns, virtual columns, certain JOIN update patterns). These bugs are fixed in subsequent releases, but systems running older MySQL versions are exposed without knowing the replication stream has produced an incorrect replica state.

PostgreSQL logical replication is more conservative: it will stop and throw an error if a replication row cannot be applied (row not found, unique constraint violation). This makes divergence detectable but also means replication stops and manual intervention is required to resume. Physical (streaming) replication cannot diverge because it replays WAL byte-for-byte; divergence is impossible unless there is a hardware error affecting the WAL byte stream.

The operational impact is that applications routing reads to a diverged replica may make incorrect business decisions (show wrong inventory counts, incorrect order history, stale financial balances) without any technical signal of a problem. The divergence may persist for the lifetime of the replica before being discovered in an audit or when a user explicitly compares replica and primary data.