Verifies data consistency between a primary table and its global secondary index (GSI) tables, and repairs any inconsistencies detected in the GSI.
How it works
CHECK GLOBAL INDEX detects inconsistencies by scanning each row in the primary table and comparing it against the corresponding GSI entry. When run with CORRECTION_BASED_ON_PRIMARY, it repairs all detected errors using the primary table as the source of truth.
For large tables, verification can take a long time. To avoid performance impact, run checks during off-peak hours. For tables where the scan itself is too slow, use HINT to enable PURE_ASYNC_DDL_MODE, which submits the operation as a pure asynchronous data definition language (DDL) statement. See Control parameters for DDL execution engine for details.
Correction locks data in the primary table or the index table, and applies repairs in batches. Run corrections during off-peak hours to minimize impact on concurrent workloads.
Syntax
CHECK GLOBAL INDEX gsi_name [ON tbl_name] [extra_cmd]| Parameter | Required | Description |
|---|---|---|
gsi_name | Yes | The name of the GSI to check. |
tbl_name | No | The primary table where the GSI resides. When specified, the statement also validates that the index relationship between the GSI table and the primary table is intact. |
extra_cmd | No | The operation mode. See the table below. |
`extra_cmd` values
| Value | Description |
|---|---|
| *(none)* | Check the GSI only. |
SHOW | Display the result of the most recent check or correction for the specified GSI. |
CORRECTION_BASED_ON_PRIMARY | Repair inconsistencies in the GSI table using the primary table as the source of truth. |
Result columns
All CHECK GLOBAL INDEX statements return a result set with the following columns:
| Column | Description |
|---|---|
GSI_TABLE | The name of the GSI. |
ERROR_TYPE | The error type. See the table below for valid values. |
STATUS | The status of the error: FOUND (detected, not yet repaired) or REPAIRED (successfully repaired). |
PRIMARY_KEY | The primary key value of the affected row. |
DETAILS | The full data payload of the affected row, in JSON format. |
`ERROR_TYPE` values
| Value | Description |
|---|---|
MISSING | A row exists in the primary table but has no corresponding entry in the GSI. |
ORPHAN | An entry exists in the GSI but has no corresponding row in the primary table. |
CONFLICT | The data in the GSI entry differs from the corresponding row in the primary table. |
ERROR_SHARD | A data shard is located in the wrong position. |
SUMMARY | A summary row appended at the end of each result set. Not an error entry. |
A single row can have multiple error types. When this occurs, the result set includes one row per error type for the same primary key.
Examples
The following examples show the complete check-and-repair workflow for a GSI named g_i_check.
Check for inconsistencies
mysql> CHECK GLOBAL INDEX `g_i_check`;If no inconsistencies are found:
+-------------+------------+--------+-------------+-----------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+--------+-------------+-----------------------------+
| `g_i_check` | SUMMARY | -- | -- | OK (7025/7025 rows checked) |
+-------------+------------+--------+-------------+-----------------------------+
1 row in set (1.40 sec)If inconsistencies are found, the result lists each affected row followed by a summary:
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| `g_i_check` | ORPHAN | FOUND | (100722) | {"GSI":{"id":100722,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_binary":"OTkAAAAAAAAAAA==","c_int_32":271}} |
| `g_i_check` | CONFLICT | FOUND | (108710) | {"Primary":{"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255},"GSI":{"c_int_32_un":123456,"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255}} |
| `g_i_check` | MISSING | FOUND | (100090) | {"Primary":{"id":100090,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_blob_tiny":"YeS4reWbvWE=","c_int_32":280}} |
| `g_i_check` | SUMMARY | -- | -- | 3 error found (7025/7025 rows checked) |
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (1.92 sec)Repair inconsistencies
mysql> CHECK GLOBAL INDEX g_i_check CORRECTION_BASED_ON_PRIMARY;+-------------+------------+--------+-------------+------------------------------------------------------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+--------+-------------+------------------------------------------------------------------------+
| `g_i_check` | SUMMARY | -- | -- | Done. Use SQL: { CHECK GLOBAL INDEX `g_i_check` SHOW; } to get result. |
+-------------+------------+--------+-------------+------------------------------------------------------------------------+
1 row in set (1.40 sec)The summary row confirms the repair was submitted. Run SHOW to retrieve the detailed repair report.
View the repair report
mysql> CHECK GLOBAL INDEX `g_i_check` SHOW;+-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GSI_TABLE | ERROR_TYPE | STATUS | PRIMARY_KEY | DETAILS |
+-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| `g_i_check` | MISSING | REPAIRED | (100090) | {"Primary":{"id":100090,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_blob_tiny":"YeS4reWbvWE=","c_int_32":280}} |
| `g_i_check` | CONFLICT | REPAIRED | (108710) | {"Primary":{"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255},"GSI":{"c_int_32_un":123456,"id":108710,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_year":"2000","c_int_32":255}} |
| `g_i_check` | ORPHAN | REPAIRED | (100722) | {"GSI":{"id":100722,"c_timestamp_6":"2000-01-01 00:00:00.000000","c_timestamp_3":"2000-01-01 00:00:00.000","c_timestamp_1":"2000-01-01 00:00:00.0","c_binary":"OTkAAAAAAAAAAA==","c_int_32":271}} |
| `g_i_check` | SUMMARY | -- | -- | 3 error found (7025/7026 rows checked.) Finish time: 2020-01-13 14:41:51.0 |
+-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.02 sec)All three previously FOUND errors now show REPAIRED, confirming the repair succeeded.
What's next
Use global secondary indexes — learn how to create and use GSIs in PolarDB for Xscale.
Control parameters for DDL execution engine — configure
PURE_ASYNC_DDL_MODEand other DDL execution parameters.