The index data and main table data of the TiDB table are inconsistent

Translated
1

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

Original topic: TiDB表的索引数据和主表数据不一致

| username: dba-kit

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.3
The index and main table data in TiDB are inconsistent, and the count and select * return different numbers. Through ADMIN CHECK INDEX, it was found that the index is indeed inconsistent.

image
image920×163 12.7 KB

I see there are two commands ADMIN CLEANUP INDEX and ADMIN RECOVER INDEX to repair the index data. Are there any pitfalls?

2
| username: dba-kit | Original post link

Is the issue here referring only to inconsistent data, or could the incrementally generated index data also be affected?

PS: In my scenario, the data with more indexes are all historical data and will not be modified. However, this table still has data being written to it normally. I wonder if I can directly use CLEANUP to fix it?
PPS: I suspect that some issues might have been left over when setting up a new cluster and upgrading from 4.0 to 6.1, using tidb-lightning’s physical mode for multiple imports.

3
| username: zhanggame1 | Original post link

I haven’t operated it before. According to the documentation, the risk is still quite high. I suggest backing up during maintenance time before proceeding. Don’t do it when there is business activity.

The key sentence is: “* If you are using TiDB Enterprise Edition, it is recommended to submit a ticket to contact PingCAP support engineers for handling.” It might require the vendor to handle it.

4
| username: redgame | Original post link

Indexes can be repaired, at most rebuilt.

5
| username: aytrack | Original post link
  1. I see that your current version is 6.5.3. The Lightning import in the house mode was done when upgrading from 4.0 to 6.1. Did you perform similar checks when you were on version 6.1?
  2. Did you perform any import operations during the upgrade from 6.1 to 6.5? When did you upgrade to 6.5?
6
| username: aytrack | Original post link

Have you used breakpoints for multiple lightning imports? After importing, have you used admin check table to check? Sync-diff cannot detect data index inconsistencies.

7
| username: dba-kit | Original post link

I have confirmed that when tidb-lightning fails to import, it is indeed possible for an inconsistent state to occur.

8
| username: dba-kit | Original post link

From 6.1 to 6.5, it was directly upgraded online, so it should not be caused by this upgrade. Additionally, the inconsistent data are all historical data from a long time ago, which also indicates that it should have been caused by errors during the import from 4.0 to 6.1.

9
| username: dba-kit | Original post link

I tried ADMIN CLEANUP/RECOVER INDEX, but it didn’t fix the data.

image
image1872×962 46.7 KB

10
| username: knull | Original post link

How large is the table? If it’s inconsistent, can we directly drop the index and then create the index again?

11
| username: dba-kit | Original post link

The problematic table is an archive table, and it already has 10 billion records. The historical partitions are still on SATA disks, so the cost of rebuilding the index is very high.

12
| username: 有猫万事足 | Original post link

I have also encountered this problem. At that time, there wasn’t much data, and the import using the lightning physical mode failed. In the end, the entire table was rebuilt. Waiting for a better solution.

13
| username: magongyong | Original post link

Bump, we also encountered this issue online.

14
| username: dba-kit | Original post link

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