TiDB Count Inaccuracy

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

Original topic: tidb count 不准

| username: Chen1234

[TiDB Usage Environment] Production Environment
[TiDB Version] V5.4.2
[Encountered Problem]
The count statistics are inaccurate.
The “isdelete” field in the “queueok” table is of int type and has only three values (0, 1, null).
Directly counting the table data gives 813,602,244, while querying by “isdelete” value separately gives results of 654,550,915, 332,686,701, and 96,173.
The sum of these three values exceeds the total count by over 100 million.


image

| username: MrSylar | Original post link

Is there an index on isdelete? If so, execute admin check table quequok;

| username: Chen1234 | Original post link

There is no index on isdelete, and I encountered an error when executing check table.

mysql> admin check table queueok;
ERROR 8003 (HY000): queueok err:[admin:8223]index:&admin.RecordData{Handle:1152921504804366654, Values:types.Datum{types.Datum{k:0x5, decimal:0x0, length:0x0, i:0, collation:“utf8mb4_bin”, b:uint8{0x51, 0x4f, 0x32, 0x30, 0x32, 0x30, 0x30, 0x38, 0x32, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x30, 0x37, 0x39, 0x35, 0x35, 0x34, 0x34, 0x31, 0x35}, x:interface {}(nil)}}} != record:&admin.RecordData{Handle:28308, Values:types.Datum{types.Datum{k:0x5, decimal:0x0, length:0x0, i:0, co

| username: MrSylar | Original post link

Find the official support, as AskTUG cannot provide a guaranteed solution for such output.

| username: redgame | Original post link

There’s also this phenomenon…

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

The 8003 ADMIN CHECK TABLE command returns this error when it encounters inconsistencies between row data and indexes, which often occurs when checking for data corruption in the table. Do you have other indexes on this table?

| username: Chen1234 | Original post link

This table has 7 indexes, and an error occurs when checking the indexes.

| username: Sean007 | Original post link

This question is interesting. Can you use EXPLAIN ANALYZE to check the execution plans of the two statistical methods?

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

Uh, the data and indexes on this table are inconsistent. Can we rebuild all the indexes? The key issue is that the problem is likely to reoccur even after rebuilding. I guess we need to get the official team to fix it.

| username: Chen1234 | Original post link

The values in the primary key are all inaccurate. Rebuilding it may not be useful; you can only try rebuilding the table.

| username: Chen1234 | Original post link

The two statistical methods use different indexes. Currently, it seems that the discrepancy between the data volume in the index and the data volume in the table is causing the issue.

| username: zhanggame1 | Original post link

Try backing up this table with Dumpling and re-importing it.

| username: Chen1234 | Original post link

Re-importing and exporting with Dumpling is feasible, and the total count matches the result of the query with fixed conditions.

| username: zhanggame1 | Original post link

Dumpling does not export indexes; the indexes are definitely rebuilt without any issues.

| username: dba-kit | Original post link

Did you import this table using the physical import method of tidb-lightning? Try counting only the last month and see if it’s the same.

| username: 我是咖啡哥 | Original post link

There might be an issue with the index. Try adding a hint for a full table scan when querying with isdelete to see the result. I encountered this once before, and rebuilding the index solved it.

| username: 人如其名 | Original post link

In what scenario did you encounter this? Please share the reproduction scenario.

| username: 我是咖啡哥 | Original post link

This is really hard to reproduce. If it could be reproduced, I would have openly reported the bug :joy:

| username: Chen1234 | Original post link

Rebuilding the index still doesn’t work; the only way is to export and import logically. The exported file also needs to change insert to replace.

| username: Chen1234 | Original post link

As long as you scan through the secondary index, there will be issues.