Encountering Error 8141 When Deleting Data in TiDB

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

Original topic: Tidb删除数据遇到8141问题

| username: 泰迪比爱好者

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] V7.1.1
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
When deleting data, I encountered the 8141 issue. The delete statement is as follows:
delete from t_en where eid=‘12334adfasfd’ and id=‘adfadfsere34132’, TiDB returned the result


The official explanation for the 8141 issue is as follows:

However, I am a bit confused by this explanation. Since I am deleting data, shouldn’t the delete operation be executed regardless of whether the data exists or not?

| username: caiyfc | Original post link

I understand that this error is caused by data and index inconsistency. First, use ADMIN CHECK TABLE to check. If the index is inconsistent, rebuild the index and then continue deleting the data.

| username: 泰迪比爱好者 | Original post link

How do you operate the ADMIN CHECK TABLE?

| username: TiDBer_5cwU0ltE | Original post link

It feels like a way of handling MVCC.

| username: caiyfc | Original post link

ADMIN CHECK TABLE tbl_name;
ADMIN CHECK [TABLE|INDEX] | PingCAP Documentation Center

| username: redgame | Original post link

You can use the check table command or other tools to check if the data is consistent.

| username: zhang_2023 | Original post link

I encountered the same issue and don’t know how to solve it. Is it a bug?

| username: 小于同学 | Original post link

What is configured for the transaction isolation level?

| username: Miracle | Original post link

This bug has been around for a long time.

| username: xfworld | Original post link

These are all known issues. First, fix the index data. Once consistent, proceed with the deletion.

Another way is to delete the index first… :see_no_evil:

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

Deleting an index: First, find the data through the index, and then proceed with the deletion. It’s not just about the deletion, right?

| username: DBAER | Original post link

Learned.

| username: 这里介绍不了我 | Original post link

Learned.

| username: porpoiselxj | Original post link

Index deletion and reconstruction

| username: TiDBer_rvITcue9 | Original post link

Try deleting the index first.

| username: Soysauce520 | Original post link

Have you confirmed that it is an issue of inconsistency between the index and the data?

| username: TiDBer_yyy | Original post link

Same question, when will it be triggered?
Dropping an index is a dangerous operation. What should be done if admin check table cannot resolve it?

| username: 随缘天空 | Original post link

I feel that TiDB has quite a few bugs and often reports some inexplicable issues. Instead of frequently updating versions, it would be better to reduce the frequency and try to minimize bugs.

| username: GreenGuan | Original post link

Known issue: index and data inconsistency. It is recommended to first add a composite index (including the problematic column index) to ensure the continuity of your business. Then delete the problematic index and rebuild it. If the issue persists, consider upgrading to fix the bug.

| username: kelvin | Original post link

This feels like the correct solution.