You can execute the CHECK GLOBAL INDEX statement to check whether data is consistent between base tables and index tables. Then, you can 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 that needs to be verified. |
tbl_name |
Optional. The name of the base table for which the global secondary index is created. If you enter the name of the base table, the system checks whether the index relationship between the global secondary index table and the base table is valid. |
extra_cmd |
The reserved extra instruction. Valid values:
|
Note
- System resources are occupied when data in the global secondary index table is verified or corrected. In most cases, this occurs in scenarios in which data in the base table or index table is locked and corrected in batches during the correction operations. We recommend that you perform these operations during off-peak hours. For more information about how to use global secondary indexes, see GSI.
- It may take a long period of time to verify the global secondary indexes of large tables. As a solution, you can use HINT to specify PURE_ASYNC_DDL_MODE. This way, DDL statements are executed in pure asynchronous mode.
Examples
- You can execute the following statement for verification:
CHECK GLOBAL INDEX `g_i_check`;- If no errors are reported during the verification, the following result is 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 result is 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 result is returned:
- You can execute the following statement for correction:
CHECK GLOBAL INDEX g_i_check CORRECTION_BASED_ON_PRIMARY;The following result is 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:
CHECK GLOBAL INDEX `g_i_check` SHOW;The following result is 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 | Description |
|---|---|
| GSI_TABLE | The name of the global secondary index. |
| ERROR_TYPE | The error type. Valid values:
|
| STATUS | The state. Valid values:
|
| PRIMARY_KEY | The primary key of the table. |
| DETAILS | The details of the error. |