All Products
Search

This Product

    PolarDB:Verify data in the primary and secondary instances

    Document Center

    PolarDB:Verify data in the primary and secondary instances

    Last Updated:Mar 28, 2026

    When replication issues occur in a Global Database Network (GDN), you need a reliable way to detect whether the secondary instance has drifted from the primary. CHECK REPLICA TABLE lets you submit, monitor, pause, resume, inspect, and cancel data verification tasks directly from the secondary instance using SQL.

    Prerequisites

    Before you begin, ensure that you have:

    Usage notes

    • Run all commands in this topic on the secondary instance.

    • Verification tasks consume storage resources. Schedule them during off-peak hours.

    Submit a verification task

    Syntax

    CHECK REPLICA TABLE {`db_name`.`table_name`} | {`db_name`}
  [CHANNEL='channel_name']
  [MODE=direct|snapshot];

    Parameters

    ParameterDescription
    db_name.table_nameVerify a single table.
    db_nameVerify all tables in the database.
    CHANNELThe channel name of the secondary instance. Get this value by running SHOW SLAVE STATUS on the secondary instance and reading the Channel_Name field. See SHOW SLAVE STATUS.
    MODEThe verification mode. See Choose a verification mode.

    Choose a verification mode

    ModeHow it worksAccuracyWhen to use
    snapshotBuilds consistent snapshots of upstream and downstream data before comparing.HighUse when you need definitive consistency results. Requires Sync Point to be enabled first.
    directReads upstream and downstream data directly without snapshotting.Lower — results may be inaccurate.Use for quick checks when approximate results are acceptable.

    Limits

    • Tables without a primary key cannot be verified.

    • If a task is submitted for a table that is currently being verified, the new task is ignored.

    • If a task is submitted for a table that was already verified, the previous results are replaced.

    Verification tasks run asynchronously.

    Example

    mysql> CHECK REPLICA TABLE `testdb`.`testtb` CHANNEL='test' MODE=direct;
Query OK, 0 rows affected (0.05 sec)

    Enable Sync Point

    Sync Point is required for MODE=snapshot. Run the following statements on the primary instance to enable it:

    SET GLOBAL enable_polarx_sync_point = true;
SET GLOBAL enable_sync_point = true;
SET GLOBAL enable_xa_tso = true;
SET GLOBAL enable_auto_commit_tso = true;
-- Interval between sync points, in milliseconds
SET GLOBAL SYNC_POINT_TASK_INTERVAL = 5000;

    After Sync Point is enabled, the Change Data Capture (CDC) node builds Timestamp Oracle (TSO) mappings between the primary and secondary instances in real time during data synchronization. A TSO uniquely identifies a transaction in binary logs. A TSO mapping pairs the primary and secondary TSOs recorded at the same point in time.

    To confirm that Sync Point is working, query the information_schema.rpl_sync_point view on the secondary instance:

    mysql> SELECT * FROM information_schema.rpl_sync_point LIMIT 10;
+-------+---------------------+---------------------+---------------------+
| ID    | PRIMARY_TSO         | SECONDARY_TSO       | CREATE_TIME         |
+-------+---------------------+---------------------+---------------------+
| 31482 | 7211850887478640704 | 7211850889504489536 | 2024-06-27 06:00:41 |
| 31483 | 7211850908445966400 | 7211850910203379776 | 2024-06-27 06:00:46 |
| 31484 | 7211850929417486400 | 7211850931690799168 | 2024-06-27 06:00:51 |
| 31485 | 7211850950393200704 | 7211850952322580544 | 2024-06-27 06:00:56 |
| 31486 | 7211850971368915008 | 7211850973738696768 | 2024-06-27 06:01:01 |
| 31487 | 7211850992327852096 | 7211850994282397760 | 2024-06-27 06:01:06 |
| 31488 | 7211851013303566400 | 7211851015677542464 | 2024-06-27 06:01:11 |
| 31489 | 7211851034275086400 | 7211851036204466240 | 2024-06-27 06:01:16 |
| 31490 | 7211851055250800704 | 7211851057595416640 | 2024-06-27 06:01:21 |
| 31491 | 7211851076218126400 | 7211851078210420800 | 2024-06-27 06:01:26 |
+-------+---------------------+---------------------+---------------------+
10 rows in set (0.04 sec)

    If the view returns rows, Sync Point is active. For more information about TSOs, see Simple Log Service (Binary logging).

    Check verification progress

    Syntax

    CHECK REPLICA TABLE {`db_name`.`table_name`} | {`db_name`} SHOW PROGRESS;

    Example

    mysql> CHECK REPLICA TABLE `test_db`.`test_tb` SHOW PROGRESS;
+----------+---------+-------+----------+---------+
| DATABASE | TABLE   | STAGE | STATUS   | SUMMARY |
+----------+---------+-------+----------+---------+
| test_db  | test_tb | CHECK | FINISHED | SUCCESS |
+----------+---------+-------+----------+---------+
1 row in set (0.04 sec)

    Pause a verification task

    Each table's verification task is split into one or more subtasks based on data size. When you pause a task, in-progress subtasks run to completion, and unstarted subtasks are paused.

    Syntax

    CHECK REPLICA TABLE {`db_name`.`table_name`} | {`db_name`} PAUSE;

    Example

    mysql> CHECK REPLICA TABLE `testdb`.`testtb` PAUSE;
Query OK, 0 rows affected (0.05 sec)

    Resume a verification task

    Resuming a paused task restarts all paused subtasks.

    Syntax

    CHECK REPLICA TABLE {`db_name`.`table_name`} | {`db_name`} CONTINUE;

    Example

    mysql> CHECK REPLICA TABLE `testdb`.`testtb` CONTINUE;
Query OK, 0 rows affected (0.05 sec)

    View verification results

    Syntax

    CHECK REPLICA TABLE {`db_name`.`table_name`} | {`db_name`} SHOW DIFF;

    Example

    mysql> CHECK REPLICA TABLE `test_db`.`test_tb` SHOW DIFF;
+----------+---------+------------+--------+--------------+-------------+--------------+-------------+
| DATABASE | TABLE   | ERROR_TYPE | STATUS | SRC_KEY_NAME | SRC_KEY_VAL | DST_KEY_NAME | DST_KEY_VAL |
+----------+---------+------------+--------+--------------+-------------+--------------+-------------+
| test_db  | test_tb | Miss       | FOUND  | [id]         | [2]         | [id]         | NULL        |
+----------+---------+------------+--------+--------------+-------------+--------------+-------------+
1 row in set (0.00 sec)

    Output fields

    FieldDescription
    DATABASEThe database containing the inconsistent table.
    TABLEThe table with the inconsistency.
    ERROR_TYPEThe type of inconsistency. See Error types.
    STATUSThe detection status of this inconsistency record. FOUND means the inconsistency was detected.
    SRC_KEY_NAMEThe primary key column name(s) of the inconsistent row in the upstream (primary instance).
    SRC_KEY_VALThe primary key value(s) of the inconsistent row in the upstream.
    DST_KEY_NAMEThe primary key column name(s) of the inconsistent row in the downstream (secondary instance).
    DST_KEY_VALThe primary key value(s) of the inconsistent row in the downstream. NULL means no matching row exists in the secondary instance.

    Error types

    ERROR_TYPEMeaningExample
    MissA row exists in the upstream but is missing in the downstream.A row with id=2 is in the primary instance but absent from the secondary.
    OrphanA row exists in the downstream but not in the upstream.The secondary instance contains rows not present in the primary.
    DiffA row exists in both instances but the data differs.The same primary key row has different column values in the primary and secondary.

    Cancel a verification task

    Canceling a task deletes the task and clears all its results.

    Syntax

    CHECK REPLICA TABLE {`db_name`.`table_name`} | {`db_name`} CANCEL;

    Example

    mysql> CHECK REPLICA TABLE `testdb`.`testtb` CANCEL;
Query OK, 0 rows affected (0.05 sec)

    Related topics