You can execute the CHECK GLOBAL INDEX statement to check whether data is consistent between primary tables and index tables, and modify inconsistent data.
Syntax
CHECK GLOBAL INDEX gsi_name [ON tbl_name] [extra_cmd]| Parameter | Description |
|---|---|
gsi_name |
The name of the global secondary index (GSI) that needs to be verified. |
tbl_name |
Optional. The primary table where the GSI resides. If you enter the specific name of a primary table, the system checks whether the index relationship between the GSI table and the primary table is valid. |
extra_cmd |
The reserved extra instruction. Valid values:
|
Note
- Some system resources are occupied when data in the GSI table is verified or corrected. This occurs especially when data in the primary table or the index table is locked and corrected in batches during the correction operation. We recommend that you perform these operations during off-peak hours. For more information about how to use GSIs, see Use global secondary indexes.
- It may take a long time to verify the GSIs of large tables. You can use HINT to specify PURE_ASYNC_DDL_MODE to execute data definition language (DDL) statements in pure asynchronous mode. For more information, see Control parameters for DDL execution engine.
Examples
- You can execute the following statement for verification:
mysql> CHECK GLOBAL INDEX `g_i_check`;- If no errors are reported during the verification, the following results are returned:
+-------------+------------+--------+-------------+-----------------------------+ | 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 errors are reported during the verification, the following results are returned:
+-------------+------------+--------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)Note If data has multiple types of errors, multiple values of ERROR_TYPE are returned for the same row of data.
- If no errors are reported during the verification, the following results are returned:
- You can execute the following statement for correction:
mysql> CHECK GLOBAL INDEX g_i_check CORRECTION_BASED_ON_PRIMARY;The following results are returned:
+-------------+------------+--------+-------------+------------------------------------------------------------------------+ | 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) - You can execute the following statement to view the report of the latest verification
or correction:
mysql> CHECK GLOBAL INDEX `g_i_check` SHOW;The following results are returned:
+-------------+------------+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)
| Column name | Description |
|---|---|
| GSI_TABLE | The name of the GSI. |
| ERROR_TYPE | The error type. Valid values:
|
| STATUS | The status. Valid values:
|
| PRIMARY_KEY | The primary key. |
| DETAILS | The details of the error. |