Reconciliation, cutover, and backout¶
Fix one consistency boundary before comparing source and target. Concurrent reads during ongoing writes can differ because of sampling time; matching or mismatching results alone do not certify migration.
Validation layers¶
Layer |
Checks |
|---|---|
Objects |
Tables, columns/types/precision/defaults, keys/constraints, views/procedures, privileges, application SQL |
Key sets |
Shards/key ranges, missing/extra keys, duplicate business keys, not only total counts |
Critical values |
NULL/empty strings, exact numbers, Unicode/bytes, timestamps/offsets, states/watermarks |
Aggregates/content |
Business-dimension sums/counts, normalized shard hashes, row comparison in suspect ranges |
Business |
Real queries/transactions, authentication, collation, indexes, jobs, external dependencies |
Do not compare vendor-specific CHECKSUM functions or default string representations directly. Define common serialization: column order, explicit NULL markers, decimal scale, UTC/original-offset policy, character/binary encoding, and stable ordering. Tables without primary keys need reproducible comparison keys or partitioning.
Negative control: matching counts, wrong data¶
Two MO fixture tables simulate source/target reconciliation here; this tests validation logic, not an external source or CDC. Corrupt one amount: counts remain 2/2 while the difference count becomes 1.
CREATE DATABASE mo_migration_validation_lab;
USE mo_migration_validation_lab;
CREATE TABLE source_fixture(id INT PRIMARY KEY, amount DECIMAL(20,4), note VARCHAR(30));
CREATE TABLE target_fixture(id INT PRIMARY KEY, amount DECIMAL(20,4), note VARCHAR(30));
INSERT INTO source_fixture VALUES(1,10.2500,NULL),(2,20.5000,'');
INSERT INTO target_fixture SELECT * FROM source_fixture;
UPDATE target_fixture SET amount=99 WHERE id=2;
-- Expected-Values: 2, 2
SELECT (SELECT COUNT(*) FROM source_fixture) AS source_count,
(SELECT COUNT(*) FROM target_fixture) AS target_count;
-- Expected-Value: 1
SELECT COUNT(*) AS different_rows
FROM source_fixture s JOIN target_fixture t ON s.id=t.id
WHERE NOT(s.amount <=> t.amount) OR NOT(s.note <=> t.note);
-- Expected-Value: 0
SELECT COUNT(*) AS missing_rows
FROM source_fixture s LEFT JOIN target_fixture t ON s.id=t.id WHERE t.id IS NULL;
-- Expected-Value: 0
SELECT COUNT(*) AS extra_rows
FROM target_fixture t LEFT JOIN source_fixture s ON t.id=s.id WHERE s.id IS NULL;
UPDATE target_fixture SET amount=20.5000 WHERE id=2;
-- Expected-Value: 0
SELECT COUNT(*) FROM source_fixture s JOIN target_fixture t ON s.id=t.id
WHERE NOT(s.amount <=> t.amount) OR NOT(s.note <=> t.note);
DROP DATABASE mo_migration_validation_lab;
After repair, the difference count is 0. In production, partition by key ranges and check missing/extra keys plus critical fields. Matching a total amount or digest alone does not establish row correctness.
Cutover gates¶
Record the full-copy consistency point and incremental start. Suspend new source writes, capture the final commit position, wait for consumption through that position, inspect errors/missing tables/retries, and reconcile before switching traffic. Check old pools, scheduled jobs, Tasks, and manual write paths to prevent two writers.
Log retention must cover the longest full copy, outage, catch-up, and revalidation. A two-hour recommendation is not universally sufficient. Error skipping, INSERT IGNORE, and continue-on-error policies require exception inventories; absence of errors does not prove completeness.
Backout boundaries¶
Before target business writes, follow the approved plan to restore source routing and retain target evidence. After target writes, first handle reverse replication, compensation, or reconciliation before enabling source writes. Changing the connection string back does not recover new target data automatically. Record acceptable loss, suspension window, owner, and final recovery point.
The fixture experiment executed and checked results on single-CN v4.2.5. Production cutover, reverse CDC, and cross-system exactly-once delivery were not verified.