CHECK GLOBAL INDEX

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]

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.

• 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)
  

  Table 1. Column descriptions