After a MySQL primary using semisynchronous replication to two replicas crashed, why might the promoted replica lack orders that customers were told had committed?
answer
- acknowledged receipt, not applied
- where the source waits
- a timeout that quietly gives up
- which replica actually acknowledged
- status variables on the source
basics
~20 sMySQL semisync only promises one replica received each commit before the client hears back, and only while it stays on. Loss usually means the source had timed out back to async, used AFTER_COMMIT, or the wrong replica was promoted.
solid answer
~40 sWith `rpl_semi_sync_source_wait_point=AFTER_SYNC`, the default, the source syncs the transaction to its binlog, waits until a replica acknowledges writing it to its relay log, then commits in InnoDB and answers the client, so a failover to that replica loses nothing clients saw. Three things break that promise. First, if no acknowledgement arrives within `rpl_semi_sync_source_timeout` (10000 ms by default), the source silently reverts to asynchronous replication; `Rpl_semi_sync_source_status` turns `OFF` and `Rpl_semi_sync_source_no_tx` counts the commits nobody acknowledged. Second, with `AFTER_COMMIT` the row is visible to other clients before the acknowledgement, so readers can see data the replica never got. Third, `rpl_semi_sync_source_wait_for_replica_count` defaults to 1, so only one of the two replicas is guaranteed to hold each transaction: promote the one with the larger `Retrieved_Gtid_Set` and let it apply its relay log first.
go deeper
Recall that semisynchronous replication makes the source wait for one replica to receive each transaction before answering the client.
Explain the difference between AFTER_SYNC and AFTER_COMMIT, and why an acknowledgement means receipt into the relay log rather than apply.
Diagnose the loss from evidence: the timeout fallback in Rpl_semi_sync_source_status and Rpl_semi_sync_source_no_tx, the wait point, and which replica held the largest received GTID set.
Weigh the timeout and the acknowledgement count against write latency and availability, and decide what failover tooling must check before promoting.
## What semisynchronous replication promises MySQL replication is asynchronous by default: the source commits and answers the client without waiting for any replica. **Semisynchronous replication** adds one wait. It is a pair of plugins, `rpl_semi_sync_source` (`semisync_source.so`) on the source and `rpl_semi_sync_replica` on each replica, switched on with `rpl_semi_sync_source_enabled` and `rpl_semi_sync_replica_enabled` (both `OFF` by default). When it is active: - a committing session blocks until at least one semisynchronous replica **acknowledges receipt** of all the transaction's events; - a replica acknowledges only after the events are **written to its relay log and flushed to disk**; - the acknowledgement says nothing about **applying** them, so an acknowledged replica can still be behind in what queries see. The general trade-off between synchronous and asynchronous acknowledgement is covered with replication modes in general; what follows is how MySQL's implementation can still lose data. ## The wait point: AFTER_SYNC versus AFTER_COMMIT `rpl_semi_sync_source_wait_point` decides where in the commit the source waits: | Setting | Order of events on the source | Who can see the row before the acknowledgement | |---|---|---| | `AFTER_SYNC` (default) | write and sync binlog, **wait**, commit in InnoDB, answer client | nobody | | `AFTER_COMMIT` | write and sync binlog, commit in InnoDB, **wait**, answer client | every other session | With `AFTER_SYNC`, all clients see a transaction at the same moment, after a replica has it, so failing over to that replica is lossless. With `AFTER_COMMIT`, a dashboard or a second request can read an order the instant InnoDB commits; if the source dies before a replica receives it, that order is gone from the promoted server even though people saw it. One more `AFTER_SYNC` consequence the manual spells out: after such a crash the old source **must not simply be restarted and rejoined**, because its binlog may hold transactions that no client was told about and no replica received; recovering them would conflict with the new primary. It is discarded and rebuilt as a replica. ## Three ways the scenario loses orders 1. **The source had already fallen back to async.** If no acknowledgement arrives within `rpl_semi_sync_source_timeout` (default **10000 milliseconds**, 10 seconds), the source stops waiting and reverts to asynchronous replication. It returns to semisync once a replica catches up. Between those points every commit is async. Clients do not get an error, so the fallback is visible only if someone watches the source's status variables. 2. **AFTER_COMMIT was configured.** As above, visible-but-unreplicated rows are expected with this setting. 3. **The wrong replica was promoted.** `rpl_semi_sync_source_wait_for_replica_count` defaults to **1**, so with two replicas each transaction is guaranteed on only one of them, possibly a different one each time. Promoting the replica that acknowledged less loses the difference. Discarding the promoted replica's relay log before it has applied it, for example with `RESET REPLICA`, has the same effect. A related setting decides what happens when replicas disappear mid-wait. `rpl_semi_sync_source_wait_no_replica` defaults to `ON`, so the source keeps waiting for the full timeout even if the number of connected semisync replicas drops below `rpl_semi_sync_source_wait_for_replica_count`. With `OFF`, the source reverts to normal replication as soon as the count drops, which shortens the stall but widens the window of unprotected commits. ## How to tell which one happened - On the source, if it is still readable, or in your metrics history: `Rpl_semi_sync_source_status` (`OFF` means not currently semisynchronous), and `Rpl_semi_sync_source_no_tx` (commits not acknowledged successfully by a replica) against `Rpl_semi_sync_source_yes_tx`. - On each replica: compare `Retrieved_Gtid_Set` with `Executed_Gtid_Set`. The replica with the most received transactions is the promotion candidate, and it should finish applying its relay log before taking writes. - In configuration: the wait point and the replica count. ```sql SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync_source_%'; SHOW GLOBAL VARIABLES LIKE 'rpl_semi_sync_source_%'; ``` ## Hardening it - Keep `AFTER_SYNC`. - Alert on `Rpl_semi_sync_source_status` going `OFF` and on any increase of `Rpl_semi_sync_source_no_tx`; a silent fallback is the most common surprise. - Decide the timeout deliberately. A short timeout protects write latency and gives up durability under stress; a very large one makes commits stall when replicas are slow or gone. - With several replicas, raise `rpl_semi_sync_source_wait_for_replica_count` only if the extra latency is acceptable, and make failover tooling choose the replica with the largest received GTID set rather than a fixed one. - Enable GTIDs, so comparing replicas and repointing the survivors is a set comparison instead of guesswork.
- Why can a MySQL replica that acknowledged a semisync transaction still return stale reads?The acknowledgement means the events are in the replica's relay log and flushed to disk, not that the applier has executed them. Queries on the replica see only what has been applied, so under load an acknowledged transaction can sit in the relay log for some time. Semisync protects durability on failover; it is not read-your-writes consistency on replicas.
- After a crash under AFTER_SYNC, why does MySQL say the old source must be discarded rather than restarted?The source syncs a transaction to its binary log before waiting for the acknowledgement, so at the crash its binlog can hold transactions that no replica received and no client was told committed. Binlog recovery on restart would commit them, giving the old source data the promoted replica never had. Rebuilding it as a replica of the new primary avoids that divergence.
Semisync with AFTER_SYNC is a shop that hands you the receipt only after the carbon copy is in the back office's drawer. AFTER_COMMIT puts the item on display before the copy is filed, and the timeout is the clerk who, after ten seconds of waiting, starts handing out receipts with no copy at all.
- Semisync guarantees that the replica has applied the transaction before the client returns.
- If no replica acknowledges, the source keeps blocking commits until one does.
- AFTER_COMMIT and AFTER_SYNC differ only in performance, not in what clients can see.
- With two semisync replicas, every transaction is on both before the commit returns.
- After the crash, restart the old source and let it rejoin as a replica unchanged.