TiDB Conditional Delete Data Failure

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

Original topic: tidb delete加条件删数据失败

| username: xie123

[TiDB Usage Environment] Production Environment / Test / Poc
Production
[TiDB Version]
v5.4.1
[Reproduction Path] What operations were performed when the issue occurred

  1. select * from table_name where condition_field2=xxx and condition_field1=‘xx’;
  2. DELETE from table_name where condition_field2=xxx and condition_field1=‘xx’;
    Query OK, 0 rows affected (0.23 sec)
    Deletion was not effective
  3. Removing one condition also did not work
    select * from table_name where condition_field2=xxx;
    DELETE from table_name where condition_field2=xxx
    Query OK, 0 rows affected (0.15 sec)
  4. Removing all where conditions. Deleting with limit 10 was successful
    DELETE from table_name limit 10;
    Query OK, 10 rows affected (0.00 sec)
  5. Repeatedly deleting without where condition, deleting 100 rows.
    DELETE from table_name limit 100;
    Query OK, 31 rows affected (0.59 sec)
    In the end, only 31 rows were deleted. 1726 rows matching the query condition remained undeleted.

[Encountered Issue: Phenomenon and Impact]
Phenomenon:
Data cannot be deleted with conditions in delete statement, but the same conditions in select statement show data. No related failure warnings were found in the logs.
Impact:
In production, data is deleted based on these conditions. Failure to delete data without warnings affects the system in unknown ways.
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: xie123 | Original post link

The remaining 1726 rows of data cannot be deleted even without the WHERE condition in the delete statement.

| username: 像风一样的男子 | Original post link

Will it still be like this if the table is dropped and rebuilt?

| username: forever | Original post link

Is it the same when executed in the command window?

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

Sounds unbelievable.

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

There should be an index on the table, right? Check the consistency between the index and the table…
ADMIN CHECK INDEX tbl_name idx_name;

| username: Kongdom | Original post link

:thinking: Has any operation been performed on the cluster? For example, backup and restore, loss repair, etc.

| username: dba远航 | Original post link

Is there anything special about the conditional data?

| username: Jellybean | Original post link

Has any operation been performed on this table historically? Have you checked the consistency between the table’s indexes and data?

| username: 哈喽沃德 | Original post link

Is there a row lock?

| username: kelvin | Original post link

It does sound incredible. You can try rebuilding the same table, backing up the data, then deleting and rebuilding the original table.

| username: zhanggame1 | Original post link

It feels a bit outrageous. If it is indeed a serious bug, could you provide the complete SQL statements and table structure?

| username: 数据库真NB | Original post link

  1. Create a new table with the same structure as the original one and try deleting it to see if it works.
  2. Create a new table with the same structure as the original one (excluding primary keys and indexes) and try deleting it to see if it works.

    Test to see where the problem is.
| username: zhang_2023 | Original post link

Could the metadata be messed up?

| username: WinterLiu | Original post link

This is absolutely ridiculous, it’s like absurdity opening the door for absurdity, it’s absurd to the extreme.

| username: 像风一样的男子 | Original post link

How about deleting and rebuilding the index?

| username: 哈喽沃德 | Original post link

This issue is indeed strange. Are there any logs to check?

| username: Jiawei | Original post link

Deleting doesn’t work. How about backing up and trying if truncating works?

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

select * from INFORMATION_SCHEMA.CLUSTER_TIDB_TRX;

| username: zhaokede | Original post link

Try rebuilding the index and then try again.