Troubleshoot Inconsistency Between Data and Indexes

TiDB checks consistency between data and indexes when it executes transactions or theADMIN CHECK [TABLE|INDEX]statement. If the check finds that a record key-value and the corresponding index key-value are inconsistent, that is, a key-value pair storing row data and the corresponding key-value pair storing its index are inconsistent (for example, more indexes or missing indexes), TiDB reports a data inconsistency error and prints the related errors in error logs.

This document describes the meanings of data inconsistency errors and provides some methods to bypass the consistency check. If a data consistency error occurs, you canget supportfrom PingCAP or the community.

This document describes the meanings of data inconsistency errors and provides some methods to bypass the consistency check. If a data consistency error occurs, you cancontact TiDB Cloud Support.

Error explanation

When inconsistency between data and indexes occurs, you can check TiDB error messages to know which item is inconsistent between row data and index data, or check the related error logs for further investigation.

Errors reported during transaction execution

This section lists the data inconsistency errors reported when TiDB executes transactions and explains the meanings of these errors with examples.

Error 8133

ERROR 8133 (HY000): data inconsistency in table: t, index: k2, index-count:1 != record-count:0

This error indicates that for thek2index in tablet, the number of indexes in the table is 1 and the number of row records is 0. The number is inconsistent.

Error 8138

ERROR 8138 (HY000): writing inconsistent data in table: t, expected-values:{KindString green} != record-values:{KindString GREEN}

This error indicates that the transaction was attempting to write an incorrect row value. For the data to be written, the encoded row data does not match the original data before encoding.

Error 8139

ERROR 8139 (HY000): writing inconsistent data in table: t, index: i1, index-handle:4 != record-handle:3, index: tables.mutation{key:kv.Key{0x74, 0x80, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x49, 0x5f, 0x69, 0x80, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x1, 0x1, 0x68, 0x65, 0x6c, 0x6c, 0x6f, 0x0, 0x0, 0x0, 0xfc, 0x1, 0x68, 0x65, 0x6c, 0x6c, 0x6f, 0x0, 0x0, 0x0, 0xfc, 0x3, 0x80, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x4}, flags:0x0, value:[]uint8{0x30}, indexID:1}, record: tables.mutation{key:kv.Key{0x74, 0x80, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x49, 0x5f, 0x72, 0x80, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x3}, flags:0xd, value:[]uint8{0x80, 0x0, 0x2, 0x0, 0x0, 0x0, 0x1, 0x2, 0x5, 0x0, 0xa, 0x0, 0x68, 0x65, 0x6c, 0x6c, 0x6f, 0x68, 0x65, 0x6c, 0x6c, 0x6f}, indexID:0}

This error indicates that the handle (that is, the key of the row data) of the data to be written is inconsistent. For indexi1in tablet, the row to be written by the transaction has a handle of 4 in the index key-value pair and a handle of 3 in the row record key-value pair. The data of this row will not be written.

Error 8140

错误8140 (HY000): t写不一致的数据able: t, index: i2, col: c1, indexed-value:{KindString hellp} != record-value:{KindString hello}

This error indicates that the data in a row to be written by the transaction does not match the data in the index. For indexi2in tablet, a row to be written by the transaction has datahellpin the index key-value pair and datahelloin the record key-value pair. The data of this row will not be written.

Error 8141

ERROR 8141 (HY000): assertion failed: key: 7480000000000000405f72013300000000000000f8, assertion: NotExist, start_ts: 430590532931813377, existing start ts: 430590532931551233, existing commit ts: 430590532931551234

This error indicates that the assertion failed when a transaction was being committed. Assuming that data and indexes are consistent, TiDB asserted that the key7480000000000000405f720133000000000000000000f8did not exist. When the transaction was being committed, TiDB found the key did exist, written by the transaction with thestart ts430590532931551233. TiDB will print the Multi-Version Concurrency Control (MVCC) history of this key to logs.

错误报告管理检查

This section lists the data inconsistency errors that might occur in TiDB when you execute theADMIN CHECK [TABLE|INDEX]statement, and explains the meanings of these errors with examples.

Error 8003

ERROR 8003 (HY000): table count 3 != index(idx) count 2

This error indicates that the table on which theADMIN CHECKstatement is executed has 3 row key-value pairs but only 2 index key-value pairs.

Error 8134

ERROR 8134 (HY000): data inconsistency in table: t, index: c2, col: c2, handle: "2", index-values:"KindInt64 13" != record-values:"KindInt64 12", compare err:

This error indicates that for indexc2in tablet, the value of columnc2has the following inconsistency:

• In the index key-value pair of the row whose handle is2, the value of columnc2is13.
• In the row record key-value pair, the value of columnc2is12.

Error 8223

ERROR 8223 (HY000): data inconsistency in table: t2, index: i1, handle: {hello, hello}, index-values:"" != record-values:"handle: {hello, hello}, values: [KindString hello KindString hello]"

This error indicates thatindex-valuesare null andrecord-valuesare not null, meaning that there is no corresponding index for the row.

Solutions

If you encounter a data inconsistency error,get supportfrom PingCAP for troubleshooting immediately instead of dealing with the error by yourself. If your application needs to skip such errors urgently, you can use the following methods to bypass the check.

If you encounter a data inconsistency error,contact TiDB Cloud Supportfor troubleshooting immediately instead of dealing with the error by yourself. If your application needs to skip such errors urgently, you can use the following methods to bypass the check.

Rewrite SQL

If the data inconsistency error occurs in a particular SQL statement only, you can bypass this error by rewriting the SQL statement to another equivalent form using different execution operators.

Disable error checks

For the following errors reported in transaction execution, you can bypass the corresponding checks:

• To bypass the checks of errors 8138, 8139, and 8140, configureset @@tidb_enable_mutation_checker=0.
• To bypass the checks of error 8141, configureset @@tidb_txn_assertion_level=OFF.

For other errors reported in transaction execution and all errors reported during the execution of theADMIN CHECK [TABLE|INDEX]statement, you cannot bypass the corresponding check, because the data is already inconsistent.