ADMIN CHECK [TABLE|INDEX]
The ADMIN CHECK [TABLE|INDEX] statement checks for data consistency of tables and indexes.
It does not support the following:
- Checking FOREIGN KEY constraints.
- Checking the PRIMARY KEY index if a clustered primary key is used.
If ADMIN CHECK [TABLE|INDEX] finds any issues, you can resolve them by dropping and recreating the index. If the issue is not resolved, you can report a bug.
Principles
The ADMIN CHECK TABLE statement takes the following steps to check the table:
For each index, it checks if the number of records in the index is the same as that in the table.
For each index, it loops over the values in each row and compares the values with that in the table.
If you use the ADMIN CHECK INDEX statement, it only checks the specified index.
Synopsis
- AdminStmt
- TableNameList
AdminStmt ::=
'ADMIN' ( 'SHOW' ( 'DDL' ( 'JOBS' Int64Num? WhereClauseOptional | 'JOB' 'QUERIES' NumList )? | TableName 'NEXT_ROW_ID' | 'SLOW' AdminShowSlow ) | 'CHECK' ( 'TABLE' TableNameList | 'INDEX' TableName Identifier ( HandleRange ( ',' HandleRange )* )? ) | 'RECOVER' 'INDEX' TableName Identifier | 'CLEANUP' ( 'INDEX' TableName Identifier | 'TABLE' 'LOCK' TableNameList ) | 'CHECKSUM' 'TABLE' TableNameList | 'CANCEL' 'DDL' 'JOBS' NumList | 'RELOAD' ( 'EXPR_PUSHDOWN_BLACKLIST' | 'OPT_RULE_BLACKLIST' | 'BINDINGS' ) | 'PLUGINS' ( 'ENABLE' | 'DISABLE' ) PluginNameList | 'REPAIR' 'TABLE' TableName CreateTableStmt | ( 'FLUSH' | 'CAPTURE' | 'EVOLVE' ) 'BINDINGS' )
TableNameList ::=
TableName ( ',' TableName )*
Examples
To check the consistency of all the data and corresponding indexes in the tbl_name table, use ADMIN CHECK TABLE:
ADMIN CHECK TABLE tbl_name [, tbl_name] ...;
If the consistency check is passed, an empty result is returned. Otherwise, an error message is returned indicating that the data is inconsistent.
ADMIN CHECK INDEX tbl_name idx_name;
The above statement is used to check the consistency of the column data and index data corresponding to the idx_name index in the tbl_name table. If the consistency check is passed, an empty result is returned; otherwise, an error message is returned indicating that the data is inconsistent.
ADMIN CHECK INDEX tbl_name idx_name (lower_val, upper_val) [, (lower_val, upper_val)] ...;
The above statement is used to check the consistency of the column data and index data corresponding to the idx_name index in the tbl_name table, with the data range (to be checked) specified. If the consistency check is passed, an empty result is returned. Otherwise, an error message is returned indicating that the data is inconsistent.
MySQL compatibility
This statement is a TiDB extension to MySQL syntax.