Repository navigation
feat(python): consistent checksums of written tables - lock and wait for the connector, or per-slice consistent snapshots (Spec 13.06 sections 3.7.1, 3.7.4) - #1548
Merged
Conversation
…ifference (Spec 13.06 section 3.7.3) On a table written during the run the sides read it at different moments, and the MySQL side reads its PK chunks over separate connections, so one pass can differ although no row diverges. Seen on a scheduled run: 15 rows closed and 15 successors inserted mid-scan gave equal counts (27,368,866) and different md5s; the next run matched and the 30 rows are identical on both sides. Both drivers now run each table through verify_table: a DIFFERENT pass is checksummed again --recheck_differences more times (default 1), --recheck_delay_seconds apart (default 60). Earlier passes log the mismatch at INFO; only the last pass logs the WARNING 'Checksum difference' that scheduled jobs fail on, so a difference that persists still fails the job and one the re-check no longer sees does not. A re-check that errors is an ERROR. --recheck_differences 0 restores the single pass, and a caller that builds its own Namespace without the new attributes keeps it. D-13.06-36 (catch-up not established) stays open; this mitigates its noise. Tests: new db_compare/tests/test_checksum_recheck.py (legacy driver end to end with the scheduled job's flags; verify_table in both copies). db_compare, db_dump and db_load: 649 passed, 4 skipped. Mutation (re-check removed): 7 of the new tests fail. Co-Authored-By: Claude Opus 5.5 (1M context) [noreply@anthropic.com]
…for the connector, or per-slice consistent snapshots (Spec 13.06 sections 3.7.1, 3.7.4) A single unlocked pass reads MySQL and ClickHouse at different moments, and the MySQL side reads its PK chunks over separate connections, so a table written during the run can differ on clean data. The fixed 3 s sleep after locking never established that the connector had applied the locked state either (D-13.06-36). Two consistent modes in the legacy driver, both driven by the connector's durable offset (replicas[].clickhouse.offset_table): --lock_tables_on_source --wait_for_connector: the end of the source binary log is read under LOCK TABLES READ (SHOW BINARY LOG STATUS from 8.2, SHOW MASTER STATUS before, chosen from @@Version) and every connector is awaited up to it before the sides run, instead of the fixed sleep. Exact; it holds the table lock for the wait plus the slowest side. --consistent_snapshot: no lock. The table is split into PK-range slices open at both ends (--snapshot_slice_rows); the MySQL side reads each slice in one START TRANSACTION WITH CONSISTENT SNAPSHOT and prints the snapshot's binlog position (Percona Binlog_snapshot_file/position: exact; elsewhere the binary log head read right after: an upper bound), the driver waits for every connector to reach it, then ClickHouse reads the slice. A differing slice is read again with a fresh snapshot; only the last pass logs the WARNING, naming the slice. A read view lives only while one slice is read. The wait (ConnectorFence) ends when the offset is at or past the target, or when the source wrote nothing after the target and the offset has been steady for --fence_idle_seconds; after --fence_timeout_seconds the comparison runs anyway and a difference names the connector position. An unreadable offset or binlog position makes that table ERROR. The packaged driver is unchanged. Tests: offline db_compare/tests/test_checksum_connector_fence.py (27 tests; mutation with both waits removed: 4 fail); end-to-end tests_e2e/mysql/test_mysql_07_consistent_checksum.py runs both modes against a real MySQL, ClickHouse and connector while a writer changes the table, plus a planted divergence and an idle source. db_compare, db_dump, db_load offline: 676 passed, 4 skipped. Destructive-Op-Check: sites=1; result=pass Co-Authored-By: Claude Opus 5.5 (1M context) [noreply@anthropic.com]
#1547 was squash-merged into 2.11.0 with the same content as its commit on this branch (40162c9), and #1537-#1543 were merged with no content change, so the two conflicting files keep this branch's version: 2.11.0's version of them plus this PR's change. Co-Authored-By: Claude Opus 5.5 (1M context) [noreply@anthropic.com]
minguyen9988
added a commit
that referenced
this pull request
Oct 2, 2026
…rsion fence; the driver logs each snapshot position (Spec 13.06 section 3.7.4) Two defects found while fixing the failing python-toolset-e2e-mysql job of #1548: 1. Without a lock the connector keeps applying writes after the snapshot position, so ClickHouse could hold a later state of a written slice than the MySQL snapshot: a slice written while it was read differed on every pass, and the end-to-end test with a background writer could not pass. Connector versions grow in binlog order (spec 02.02 sections 3.3, 3.5), so every transaction after the snapshot is versioned above the slice's highest _version read before it. The driver now reads that floor, the MySQL side holds its snapshot (--exclude_keys_from_stdin) while the driver waits for the connector, and the keys the replica changed above the floor are left out on both sides - sent to the waiting side inside its snapshot and ANDed onto the ClickHouse filter. Every other key must match. Needs an integer key and _version on every replica table; above --snapshot_max_excluded_keys (default 5000) changed keys nothing is left out and the slice is compared as is. 2. The end-to-end test asserted the MySQL side's upper_bound line, which reaches the driver log only at DEBUG. The driver now logs the position it waits on ('Snapshot of TABLE slice [CONDITION] at FILE:POS (KIND)') and the test asserts that line. Tests: TestOneSlice (version fence, cap), TestVersionFenceQueries, TestSnapshotSideProtocol (real pipes, failing wait closes the side), TestMySQLSideExclusionLine; mutation with the fence disabled fails the two fence tests. db_compare, db_dump, db_load: 684 passed, 4 skipped. e2e module: writer churn now expects exclusions and MATCH; slice count only has to split the table (InnoDB row estimate). Co-Authored-By: Claude Opus 5.5 (1M context) [noreply@anthropic.com]
minguyen9988
added a commit
that referenced
this pull request
Oct 2, 2026
… snapshot position in the driver log, one timeout per lagging connector (Spec 13.06 sections 3.7.1, 3.7.4) (#1549) * fix(python): a connector that missed a fence is waited on once per run, not once per slice (Spec 13.06 section 3.7.1) Under --consistent_snapshot every slice, and every re-read of a slice, waits for the connector. When the connector lags far behind, each of those waits ran the full --fence_timeout_seconds, so a large table could take slices x passes x timeout. After the first missed target the connector is marked behind and its later waits check the offset once; the first target it reaches clears the mark. Test: TestBehindConnector. Co-Authored-By: Claude Opus 5.5 (1M context) [noreply@anthropic.com] * fix(python): consistent snapshots compare written slices through a version fence; the driver logs each snapshot position (Spec 13.06 section 3.7.4) Two defects found while fixing the failing python-toolset-e2e-mysql job of #1548: 1. Without a lock the connector keeps applying writes after the snapshot position, so ClickHouse could hold a later state of a written slice than the MySQL snapshot: a slice written while it was read differed on every pass, and the end-to-end test with a background writer could not pass. Connector versions grow in binlog order (spec 02.02 sections 3.3, 3.5), so every transaction after the snapshot is versioned above the slice's highest _version read before it. The driver now reads that floor, the MySQL side holds its snapshot (--exclude_keys_from_stdin) while the driver waits for the connector, and the keys the replica changed above the floor are left out on both sides - sent to the waiting side inside its snapshot and ANDed onto the ClickHouse filter. Every other key must match. Needs an integer key and _version on every replica table; above --snapshot_max_excluded_keys (default 5000) changed keys nothing is left out and the slice is compared as is. 2. The end-to-end test asserted the MySQL side's upper_bound line, which reaches the driver log only at DEBUG. The driver now logs the position it waits on ('Snapshot of TABLE slice [CONDITION] at FILE:POS (KIND)') and the test asserts that line. Tests: TestOneSlice (version fence, cap), TestVersionFenceQueries, TestSnapshotSideProtocol (real pipes, failing wait closes the side), TestMySQLSideExclusionLine; mutation with the fence disabled fails the two fence tests. db_compare, db_dump, db_load: 684 passed, 4 skipped. e2e module: writer churn now expects exclusions and MATCH; slice count only has to split the table (InnoDB row estimate). Co-Authored-By: Claude Opus 5.5 (1M context) [noreply@anthropic.com] * fix(python): parenthesise the MySQL side's key exclusion; exact key-column match; clearer behind-connector log (Spec 13.06 sections 3.7.1, 3.7.4) Review of the version fence: the MySQL side ANDed the exclusion onto its filter without parentheses (an OR in a table's where override would bind it differently from the ClickHouse side) and without an empty-filter guard; it now uses the ClickHouse side's shape. The key-column check uses fullmatch, so a trailing newline is refused. A missed wait of a connector already marked behind logs 'in one check (it is behind)' instead of 'within 0 s'. Spec: a failing wait makes that table ERROR as in 3.7.1; a behind connector's wait ends after its single check. Tests: newline and boolean keys refused. db_compare, db_dump, db_load: 684 passed, 4 skipped. Co-Authored-By: Claude Opus 5.5 (1M context) [noreply@anthropic.com]
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Builds on #1547 (merged): 2.11.0 has been merged into this branch, so the diff is only this change.
Problem: a single unlocked pass of the checksum driver reads MySQL and ClickHouse at different moments, and the MySQL side reads its PK chunks over separate connections, so a table written during the run can differ on clean data. With --lock_tables_on_source the only catch-up is a fixed 3 s sleep: nothing establishes that the connector has applied the locked state (D-13.06-36).
Two consistent modes in the legacy driver (the one the scheduled jobs run). Both wait on the connector's durable offset, read from a new replica config key offset_table (the connector's offset.storage.jdbc.table.name; optional offset_key_contains when the table holds several offsets).
The wait (ConnectorFence) ends when the offset is at or past the target; or when the source wrote nothing after the target and the offset has been steady for --fence_idle_seconds (Debezium records the start of the transaction it is in, so an idle source never moves it past the target); after --fence_timeout_seconds the comparison runs anyway and a difference names the connector position (a lagging connector can only produce a difference, never a false match). An unreadable offset or binlog position makes that table ERROR; the other tables still get verdicts. Both modes need REPLICATION CLIENT on the source. The packaged driver is unchanged and the spec says so.
Spec 13.06: section 3.7.1 (wait_for_connector, fence semantics), new 3.7.4 (consistent snapshots), 3.3 and 3.4 (flags, offset_table), 3.9 (side flag), test lists in 5.1 and 5.3, D-13.06-36 fixed for the two modes and still open for the default single pass.
Verification:
🤖 Generated with Claude Code