Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: sql查询出现数据索引一致性报错
Bug Report
Clearly and accurately describe the issue you found. Providing any steps to reproduce the problem can help the development team address the issue promptly.
【TiDB Version】v6.2.0
【Impact of the Bug】
The queried data is not within the filter criteria.
【Possible Steps to Reproduce the Issue】
【Observed Unexpected Behavior】
The same SQL query yesterday showed that the filter criteria were not effective, for example, dim_code=x but the result contained dim_code=y. Today, it directly reported an error with the following message:
8133 - data inconsistency in table: SYS_PERMISSION_DIM_VALUE, index: HAUTH_PERMISSION_DIM_VALUE_U1, index-count:184 != record-count:183
【Expected Behavior】
【Related Components and Specific Versions】
【Other Background Information or Screenshots】
Such as cluster topology, system and kernel version, application app information, etc.; if the issue is related to SQL, please provide the SQL statement and related table schema information; if there are critical errors in the node logs, please provide the relevant node log content or files; if some business-sensitive information is inconvenient to provide, please leave contact information, and we will communicate with you privately.
Query SQL statement:
select * from SYS_PERMISSION_DIM_value where dim_code = 'WDKJ_ORG' and employee_group_id = 1509781370026692618 order by dim_value_code, is_editable
Not sure what you mean. Are the SQL results not matching your expectations?
Error 8133
ERROR 8133 (HY000): data inconsistency in table: t, index: k2, index-count:1 != record-count:0
The above error indicates that for the k2 index in table t, the index count is 1, while the record count is 0, showing an inconsistency in the numbers.
Yes, previously the query returned incorrect results. The same SQL query previously had issues where the filter condition was not effective (for example, filtering for employee_group_id=1 but getting results with employee_group_id=2). Now it directly reports an error.
Is the current v6.2 cluster upgraded from a previous version? Since v6.0, the consistency check parameter tidb_enable_mutation_checker is enabled by default. If you encounter an 8133 error, the DML statement will be rolled back.
The current v6.2 cluster was not upgraded, but the data from the previous cluster (which was upgraded from v5.x to v6.2) was restored using BR. I checked and this value is enabled.
Is there a solution to this problem?
That should be an issue with version v5.x. BR backup and restore also won’t check for data inconsistencies. Previous versions had some bugs that could lead to index keys not being deleted in certain situations. The error message above indicates that the number of index data entries is greater than the number of record data entries. You can first clean up the excess index data. It is recommended to enable the tidb_enable_mutation_checker parameter to avoid encountering this type of problem again.
admin cleanup index [table_name][index_name];
Let’s follow the teacher’s instructions above first.
The index has been cleaned up, and there are no more index errors, but the query exception from yesterday has reappeared. The filter conditions are out of control, and the results are as follows:
SQL script:
select * from SYS_PERMISSION_DIM_value where dim_code = 'WDKJ_ORG' and employee_group_id = 1509781370026692618 order by dim_value_code, is_editable;
Check if there are any data index inconsistencies on the table
The teacher above is so strong. Although I haven’t encountered it, I will learn from it.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: monlor
|
username: 
|
username: 
|
username: 
|
username: 
|
username: