A teacher corrects a student’s standing long jump result from 2.21 to 2.31 meters. The update succeeds, and the teacher sees the corrected value immediately. A report generated later still shows 2.21.
Nothing appears broken. The application is responding, the database is available, and the message queue may even show no backlog. Yet two parts of the platform disagree about the same result.
In the previous article, I discussed separating the storage responsibilities of a student fitness platform: MySQL owns operational records, while ClickHouse supports reporting and analytical queries. This article follows the boundary between them. Once synchronization is asynchronous, we need to answer three practical questions: what happens when delivery stops halfway, how do we distinguish normal delay from incorrect data, and how do we repair a discrepancy without creating another one?
The fitness platform provides the business context. The implementation patterns below describe a concrete recovery design; the sample values illustrate failure cases rather than measured production incidents.
Start with a recoverable business change
Consider a service that updates a fitness result and then publishes an event:
updateFitnessResult(command);
publishResultChangedEvent(event);If the database commits and the process stops before publication, the business change survives but its notification does not. Publishing first creates the opposite risk: a consumer may process a change that the database later rejects.
A transactional outbox closes this particular gap. The authoritative MySQL shard updates the result, advances its version, and inserts an event within one local transaction. Either all three changes commit or none do.
The shard boundary matters. If the fitness record lives on one shard and the outbox lives in a separate central database, an ordinary local transaction cannot provide that guarantee. Each authoritative shard needs an outbox that participates in the same transaction as its business records.
The event should describe the committed state precisely enough for a consumer to reproduce it. A simplified example looks like this:
{
"eventId": "fitness-result-900001-v2",
"dataId": 900001,
"detectId": 10001,
"userId": 4,
"projectId": 3,
"version": 2,
"result": 2.31,
"unit": "m",
"isDeleted": false,
"schemaVersion": 1
}Here, version belongs to the business record. It advances when that record changes, under the same concurrency controls that protect the update. A delivery retry does not create a new business version.
Using a complete state snapshot also makes recovery easier. If version 2 arrives before version 1, the consumer can retain version 2 without needing to apply every earlier change. An event containing only “increase the result by 0.10” would require different ordering and duplicate-handling rules.
The outbox guarantees that a committed change has a durable publication record. It does not, by itself, guarantee that a stopped relay will restart, an exhausted retry will be investigated, or an expired message will remain available forever. Those responsibilities belong to the rest of the recovery design. The transactional outbox pattern also anticipates duplicate delivery, which consumers must handle.
Design the relay around the moment it can fail
A polling relay is a reasonable starting point when the application owns the write path and needs explicit business events. Its behavior is easy to inspect: find eligible outbox records, publish them, and record the outcome. CDC is another option, but it introduces a different operational model; the choice should follow the team’s infrastructure and workload.
With multiple relay instances, task ownership needs to survive beyond a database query. One approach is to claim a small batch inside a short transaction:
SELECT id
FROM outbox_event
WHERE status = 'NEW'
OR (
status = 'FAILED'
AND next_retry_time <= CURRENT_TIMESTAMP
)
ORDER BY id
LIMIT 500
FOR UPDATE SKIP LOCKED;Before committing, the relay marks the selected rows as PROCESSING and assigns a claim token and lease expiry. It then publishes outside the database transaction. This avoids keeping database locks open while waiting for a network operation.
MySQL releases the query’s locks when the transaction ends. SKIP LOCKED helps concurrent workers claim different rows, but the persisted status and lease provide ownership after that point.
If the relay crashes, expired claims become eligible for recovery. Completion updates should check the claim token so an old worker cannot overwrite the status owned by a newer claimant. Publishing timeouts and lease duration also need to agree; otherwise, a healthy but slow operation may be reclaimed prematurely.
Even with those controls, duplicate publication remains possible:
| Failure point | Recovery behavior |
|---|---|
| Before publication | Reclaim the expired task and publish it |
| Broker accepts the event, but the response is lost | Retry because delivery is uncertain |
Publication succeeds, but marking SENT fails | Publish again after recovery |
This is why SENT means the configured broker acceptance condition was satisfied. It does not mean ClickHouse has stored the event or a report has incorporated it.
Retries should use bounded backoff with jitter. Events that repeatedly fail need a visible parked state, an alert, and a controlled replay path. Moving an event to DEAD and forgetting it would break the very recovery promise the outbox was introduced to provide.
Make repeated and delayed events harmless to current-state queries
For the fitness reporting use case, the target is the latest valid state of each result. ClickHouse can retain several physical versions while queries expose one logical record.
ReplacingMergeTree(version) supports that model, but the engine declaration is only part of the contract. It identifies matching records through the complete ORDER BY key. Those key fields must remain stable across versions, and versions of one record should retain a stable partition assignment.
The producer must also ensure that the same business version represents the same state. Ordinary retries should reproduce the same payload. Consumers should never generate a larger version simply because a message arrived later.
These conditions allow version 2 to remain authoritative even if version 1 is delivered afterward. They do not make every ordinary query correct automatically. Background merges are asynchronous, and queries can still encounter multiple versions. Current-state reads therefore need an explicit strategy such as FINAL or aggregation that selects the latest complete row.
For this design, repeated inserts of the same version and content are acceptable at the logical level, provided reporting consistently reads the latest state. Physical duplicates may still exist. Any downstream aggregation must honor the same contract; summing every inserted version would count corrections more than once.
An eventId helps trace retries and identify repeated delivery, but its presence does not implement deduplication. A separate “processed events” table can introduce another failure boundary: recording an event as processed before the ClickHouse write can lose work, while recording it afterward still permits retries. The target write and query model must tolerate that uncertainty.
The consumer should acknowledge a message only after the required ClickHouse write acknowledgment. If asynchronous insertion is enabled, that acknowledgment must correspond to the intended persistence behavior rather than merely accepting data into an intermediate buffer.
Reconcile business state, not message counts
A quiet queue is evidence about transport. It says little about whether the consumer mapped the right fields or selected the right records.
Suppose a mapping defect writes an incorrect school identifier while preserving dataId and version. Publication succeeds, insertion succeeds, and the queue drains. A comparison of IDs and versions still passes.
Reconciliation therefore needs a definition of equality that reflects the report’s business meaning. For a fitness result, that might include the record identifier, source version, deletion state, project, result value, unit, and the school or grade associated with the test.
Before comparing those fields, both sides need a compatible scope and time boundary. Comparing current MySQL data with a ClickHouse projection that is still catching up will report normal delay as an error.
A practical starting point is a completed fitness campaign whose results are no longer changing. If edits remain possible, the reconciliation job can detect a change in the campaign’s revision during its scan and retry the comparison after synchronization catches up. More demanding workloads may need source snapshots and explicit progress tracking. A simple timestamp filter cannot reconstruct an earlier source state after records have been overwritten.
The source of comparison also matters. A MySQL table organized by campaign may itself be an asynchronous projection. If so, comparing it with ClickHouse could miss a change that both projections lost. The authoritative records, or a validated snapshot derived from them, should establish the reference state.
Within that scope, reconciliation can become progressively more detailed:
| Comparison | Useful for finding | Remaining blind spot |
|---|---|---|
| Logical counts by campaign and project | Large omissions or unexpected extras | Different records can produce equal counts |
| Record IDs and source versions | Missing records and outdated versions | Equal-version field errors |
| Canonical fields or content digests | Incorrect values and transformations | Requires consistent normalization |
“Logical counts” means counts after selecting the latest non-deleted record, not raw physical rows in a versioned ClickHouse table.
Content digests need an agreed representation of nulls, timestamps, units, and numeric precision. Otherwise, formatting differences can look like business differences. For example, a value represented as 2.31 and another as 2.310 should not produce a false discrepancy merely because their text differs.
Aggregate checks help prioritize work, but a matching count should not permanently exempt a campaign from deeper validation. Periodic bucket comparisons or sampling can catch errors that cancel out at the summary level.
Repair according to the kind of discrepancy
Replaying authoritative state is effective for missing records and outdated versions. If MySQL holds version 4 and ClickHouse exposes version 3, a repair job can read version 4 and send it through the normal transformation and insertion path.
That repair must retain source version 4. Giving the replay an artificially large version based on the repair time could cause it to outrank legitimate future business updates.
Other discrepancies need different treatment:
| Discrepancy | Recovery approach |
|---|---|
| Missing record or older target version | Replay authoritative state with its existing version |
| Source deletion absent from the target | Deliver a versioned deletion record |
| Same version with incorrect content | Fix the transformation and use a controlled correction or rebuild |
| Unexpected target record | Investigate its origin, then remove or supersede it through an auditable process |
Deletion deserves explicit support. Once a source row is physically removed, rereading the current business table cannot tell a repair job that the downstream copy should disappear. A versioned tombstone, retained deletion log, or another durable deletion record is necessary. Its retention period must account for delayed events and recovery needs.
Equal-version corruption is more subtle. If a faulty consumer produced the wrong content for version 4, simply sending version 4 again does not establish a durable ordering between the wrong and corrected representations. A delayed retry from the old path can reintroduce bad content.
For a bounded affected dataset, a controlled rebuild can be easier to reason about. Fix the transformation, create a clean projection from authoritative state, incorporate changes that occur during rebuilding, validate it, and switch readers only after verification. The defective write path must be retired or isolated as part of that process. This is more work than a replay, but it addresses a different failure.
Every repair should record its scope, reason, source versions, outcome, and verification result. Repairs should also be rate-limited so a large historical recovery does not prevent current fitness results from reaching reports.
The operational signals should follow the same end-to-end view: the age of the oldest unsent outbox event, expired claims, parked events, projection lag, unresolved discrepancies, and repairs awaiting verification. These expose failures that a queue-depth chart alone cannot explain.
For this platform, the consistency promise is concrete: MySQL commits the authoritative fitness result and its publication record together; the reporting projection may lag; retries preserve source identity and version; reconciliation checks business content; and recovery handles omissions, deletions, and corrupted projections explicitly.
A teacher correcting a result does not need to understand those mechanisms. They need the correction to survive, appear in reports, and remain correct after a retry or outage. That is the standard the synchronization design should be built to meet.