Data verification principles

This topic describes the data verification feature of OceanBase Migration Service (OMS). OMS identifies and processes inconsistent data by means of data sharding, caching, joining, and verification.

Terms

Data verification procedure

OMS performs full data verification in the following procedure:

1. The sharding thread splits the source table and places all shards into queue 1.

2. The data source read threads in the source and target databases fetch shards from queue 1 and concurrently read data from the source and target databases based on the shards.

3. The system delivers the data read from the source and target databases for the same shard to the same cache. In the cache, the system joins data based on their primary keys or unique keys. This ensures that source data and target data corresponding to the same shard are stored to the same cache and each shard corresponds to one cache. If the join operation is successful, source data and target data corresponding to the specified primary key or unique key are ready. Then, the system places joined data into queue 2.

4. The verification thread fetches joined data from queue 2 for verification. If the data corresponding to a primary key or unique key is inconsistent, the system sends the primary key or unique key of the record to the reverification scheduler.

5. The reverification scheduler uses a specific algorithm to determine the primary keys or unique keys of inconsistent records that meet the reverification condition, and adds them to queue 3.

6. The reverification thread fetches the primary keys or unique keys of inconsistent records from queue 3 and reverifies the records.

Technical mechanism of data verification

OMS supports regular verification and IN mode verification. The two verification modes differ in the method for reading data from the target table.

Read data from the source table

OMS splits the source table into multiple shards and reads data by shard. For example, a table contains 300 records and has a primary key id whose value ranges from 1 to 300. Assume that the table is split into three shards:

• Shard 1 with the id value less than 100

• Shard 2 with the id value ranging from 101 to 200

• Shard 3 with the id value greater than 200

OMS reads data from the source table by shard regardless of the verification mode.

// Read data of shard 1 from the source table.
SELECT * FROM t WHERE id <= 100 
// Read data of shard 2 from the source table.
SELECT * FROM t WHERE id > 101 AND id <= 200

Read data from the target table

OMS reads data from the target table in different ways based on the verification mode. In regular verification, OMS reads data from the target table by shard the same way it reads data from the source table.

// The table name suffixed with `_map` indicates the target table corresponding to the source table.
// Read data of shard 1 from the target table.
SELECT * FROM t_map WHERE id <= 100 
// Read data of shard 2 from the target table.
SELECT * FROM t_map WHERE id > 101 AND id <= 200

In IN mode verification, OMS does not read data from the target table by shard. Instead, it calculates primary key values based on the data read from a shard of the source table, and then reads data from the target table based on the primary key values.

// Read data of shard 1 from the source table.
SELECT * FROM t WHERE id <= 100
// Assume that three data records corresponding to the primary key values 7, 23, and 37 are read.
// Read data from the target table based on the primary key values.
SELECT * FROM t_map WHERE id = 7 OR id = 23 OR id = 37;


// Read data of shard 2 from the source table.
SELECT * FROM t WHERE id > 101 AND id <= 200
// Assume that five data records corresponding to the primary key values 111, 112, 113, 116, and 117 are read.
// Read data from the target table based on the primary key values.
SELECT * FROM t_map WHERE id = 111 OR id = 112 OR id = 113 OR id = 116 OR id = 117;

Scenarios of IN mode verification

1. Manual configuration: You can use the filter.verify.inmod.tables parameter to specify the tables to which you want to apply IN mode verification. If you set this parameter to .*;.*;.*, all tables will be verified in IN mode.

   Notice

   When you specify this parameter, make sure that the setting is appropriate.

   You can apply IN mode verification to all tables in the following scenarios:

   1. Data is synchronized from multiple source tables to one target table. For example, data is migrated from tables A_1, A_2, ..., and A_N tables in the source database to table A in the target database. In this case, you must apply IN mode verification to all tables.

   2. The target table contains a large amount of data. When you create a task to migrate data to the target table, if you select Ignore for Processing Strategy When Target Table Has Records, the system delivers the parameter that specifies to apply IN mode verification to all tables.

2. Partitioned-table verification. When you migrate data between HASH- or KEY-partitioned tables that use different partitioning algorithms, the same data may be distributed to different partitions in the source and target tables. In this case, excessive data inconsistency errors may be falsely returned if you use the regular verification mode.

3. Inconsistent partition definitions. When the source and target tables have different partition definitions, we recommend that you use IN mode verification.

4. Multi-table aggregation. When data is synchronized from multiple source tables to one target table, IN mode verification can avoid excessive false inconsistency errors about data that exists only in the target table.