Query SQL Error: Data Inconsistency in Table

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 查询sql报错data inconsistency in table

| username: wluckdog

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5
[Reproduction Path]
select * from tx_build order by update_time limit 10

8133 - data inconsistency in table: tx_build, index: idx_build_03, index-count:10 != record-count:0
Time: 0.021s

admin check index tx_build tx_build_03

8003 - table count 4467708 != index(idx_build_03) count 4469445
Time: 0.362s

[Encountered Problem: Phenomenon and Impact]
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]

| username: 裤衩儿飞上天 | Original post link

Data Index Consistency Error | PingCAP Documentation Center

| username: tidb菜鸟一只 | Original post link

The table and index are inconsistent, and it can’t be handled.

| username: 考试没答案 | Original post link

Delete the index. Rebuild? Is it feasible?
For v6.5.0, rebuilding the index is particularly fast.

| username: Kongdom | Original post link

I encountered the same issue, but it might have been caused by cross-version restoration. Did you perform a similar operation? The error code I got is 8223.

| username: tidb菜鸟一只 | Original post link

Rebuilding the index is possible, but if you don’t know what operation caused this issue, the same problem will occur again next time.

| username: 考试没答案 | Original post link

Is it possible that the online DDL is causing it due to being asynchronous??? Has your company purchased TiDB enterprise services?

| username: 胡杨树旁 | Original post link

Isn’t TiDB an open-source product? Why are there both a community version and an enterprise version?

| username: Kongdom | Original post link

The functionality is basically the same, but the enterprise edition includes additional services and auditing features.

| username: 考试没答案 | Original post link

Enterprise services and enterprise editions are different. Community open source can also be charged.

| username: wluckdog | Original post link

We did not import data; the business data is being inserted gradually.

| username: wluckdog | Original post link

I have seen this in the official documentation, so I came to the forum to see if there is a solution.

| username: wluckdog | Original post link

Yes, when a problem occurs, we rebuild the index. I came to the forum to see if there are any other solutions besides deleting the index. If it’s a large table, rebuilding the index can be very slow, possibly taking more than 10 hours. If that’s the case, are there any other solutions?

| username: 考试没答案 | Original post link

Could you tell me how large your data volume is?

| username: 考试没答案 | Original post link

Has the version been upgraded to V6.5.0?

| username: wluckdog | Original post link

Yes, the version is already 6.5. The amount of data in the table is currently not large, but the database has a relatively large amount of data, over a terabyte.

| username: 小王同学Plus | Original post link

The official data repair commands provided are: admin cleanup and admin recover.

  • admin cleanup: Suitable for situations where index data is more than data row data, it will delete the excess index data.
  • admin recover: Suitable for situations where data row data is more than index row data, it will fill in the missing index data.

admin cleanup

admin cleanup index [table_name][index_name];

Theoretically, when deleting, both data and index should be deleted simultaneously. However, in some cases, the index key might not be deleted, leaving behind an index key. When a data row is rewritten, both data and index are written, and the index key corresponds to the same handle id as the leftover key:

admin cleanup does not cover this situation. You can confirm if this situation exists by using the following queries:

select count(*) as cnt, id+1 from table_name use index(index_name) group by id+1 having cnt > 1

select count(*) as cnt, _tidb_rowid from table_name use index(index_name) group by _tidb_rowid having cnt > 1

If this phenomenon occurs, you need to rebuild the index to resolve it.

admin recover

admin recover index [table_name][index_name];
  • admin clean and recover are currently processed in a single thread, with a batch size of 20,000 per operation. There are no parameters to adjust concurrency or batch size. The background operation is similar to a table full scan, and the larger the table, the longer it will take.

Others

In some cases, if the table is particularly large, using admin cleanup can be time-consuming. You can try the following method to bypass this issue:

  • Business errors caused by data index inconsistency, with a controllable impact range.
  • Create a new index index_new, and the columns of the new index can cover index_old, meeting the index prefix condition.
    • Add a new composite index index_new. For example, if the problematic index_old includes columns (col1, col2), create a new index_new as (col1, col2).
    • Allow the business to continue using index_old to access business data (but in some cases, it may exit due to data index inconsistency errors).
  • Monitor the progress of adding the index_new. After completion, you can delete index_old.
  • Observe the execution plan of related SQL to see if it can hit index_new. If not, you can use SQL binding to fix the execution plan.

This method is only applicable to the admin cleanup situation and needs to be evaluated based on the current business access situation. It is a direction you can consider.

| username: Kongdom | Original post link

Got it, I’ll give it a try with this command line as well.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.