Building a Robust Database Consistency Check Tool for Seamless Migrations

1. Background

In the wave of X‑Innovation (信创) transformation, the overall migration of information systems—especially the migration of homogeneous and heterogeneous databases across different hardware architectures—has become a focal task. Data‑consistency comparison verification is an indispensable part of migration, directly influencing whether the migration succeeds and whether hidden risks remain after cut‑over.

2. Requirement Scenario

Anyone who has worked as a MySQL DBA is familiar with error code 1062, which usually triggers a data‑consistency check and forces a decision to skip the transaction or rebuild the replica, each with its own risks and costs. Frequent occurrences of this error may stem from unauthorized operations on the replica or from database bugs. For example, MySQL Bug #83030 caused an

auto_increment

mismatch between primary and replica, leading to business interruption after a failover. This bug existed in MySQL 5.7 and was only fixed in 8.0, so enterprises that have not upgraded must still detect and mitigate it. Moreover, the bug affects metadata rather than table data, so checking only table data may miss inconsistencies.

The 2020 “Database and Application System Migration Guide” issued by the China Academy of Information and Communications Technology emphasizes the “data integrity and consistency principle”, which states that migration does not require identical schema and data, but the target database must produce the same business results as the source, avoiding data loss, chaos, or inconsistency.

Therefore, a usable, universal, and reliable database‑consistency verification tool becomes essential in daily operations and migration projects.

3. Tool Overview

Many tools exist for database consistency verification, each with limitations. The widely known Percona Toolkit utilities

pt-table-checksum

and

pt-table-sync

only support MySQL master‑slave consistency checks and repairs. PingCAP’s

sync-diff-inspector

can verify consistency between MySQL/TiDB instances but does not support online verification and requires a static data window. The recently open‑sourced

gt-checksum

from Wanli Database supports both MySQL and Oracle and offers extensibility to other database types.

4. Tool Capability Analysis

Structure consistency verification

Data consistency verification

Support for sampling verification

Support for advanced objects such as indexes, stored procedures, partitions, etc.

Ability to remediate inconsistent data

High comparison efficiency

Additional important capabilities include:

Support for multiple data‑source types and different deployment architectures of the same source

Granular inclusion or exclusion at database, table, and column levels

Minimal impact on the operating environment in terms of load and lock holding

Strict permission control to ensure operational and data security

5. Tool Process Analysis

Based on the study of the

gt-checksum

project and database fundamentals, the core workflow of a consistency‑checking tool can be summarized as follows.

Read configuration : obtain source and target connection information, objects to check, methods, and advanced options.

Check user permissions : ensure read access to metadata and data, and write access if in‑place repair is required.

Select databases and tables to verify : support schema/table matching, ignore patterns, wildcard definitions, and column‑level filtering.

Handle different database objects : collect structures, indexes, partitions, constraints, stored procedures, functions, triggers, etc., and compare them, taking special care with heterogeneous databases to avoid false positives.

Process table data : the most time‑consuming step, involving column handling, permission checks, metadata retrieval, index selection, chunking strategy, execution plan generation, and actual data comparison.

Output results : present differences, provide manual confirmation mechanisms, and generate idempotent repair statements.

The data‑level verification flow includes handling column names (including type compatibility for heterogeneous databases), checking table permissions, retrieving column and index information, generating execution plans based on chunk size and selected indexes, and executing identical SQL on source and target to compare results.

Choosing an appropriate chunk key is critical: a single‑column integer primary key is ideal; composite keys or lack of primary keys require careful index selection. Supporting tables without indexes is low priority due to high cost and limited benefit.

Performance considerations such as metadata retrieval efficiency, memory usage, and Go’s concurrency model still leave room for improvement.

6. Feature Extensions

Given the richness and complexity of various databases, a universal tool must balance verification accuracy with practicality. Future enhancements may include handling character‑set differences, supporting divergent topologies (e.g., sharding on the target side), dynamic chunk size adjustment, and safe concurrency increase to improve comparison efficiency.