pingcap / pingcap/tidb

All internal sessions are not protected by MDL (and online DDL protocol), so TTL delete can cause data/index inconsistency

Open
#71,055 2 comments 0 reactions 1 assignee Claimed by @YangKeao View on GitHub
affects-25.10 affects-26.3 affects-7.1 affects-7.5 affects-8.1 affects-8.5 component/ddl found-by-ai severity/critical sig/sql-infra
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

1. Use a failpoint-enabled TiDB build. Add a test pause in `ttlTableSession.ExecuteSQLWithCheck` after a TTL `DELETE` executes and before it commits. This is only to hold the ordinary TTL statement across the DDL metadata-lock transition.

2. Create a TTL table containing 50,000 expired rows and 100,000 live rows:

```sql
CREATE DATABASE ttl_mdl_bug;
CREATE TABLE ttl_mdl_bug.t (
id INT PRIMARY KEY,
a INT NOT NULL,
created_at DATETIME NOT NULL,
pad VARCHAR(64)
) TTL = created_at + INTERVAL 1 HOUR TTL_ENABLE='OFF';

SET SESSION cte_max_recursion_depth=200010;
INSERT INTO ttl_mdl_bug.t
WITH RECURSIVE seq(n) AS (
SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n<50000
)
SELECT n,n,NOW()-INTERVAL 2 HOUR,REPEAT('x',32) FROM seq;

INSERT INTO ttl_mdl_bug.t
WITH RECURSIVE seq(n) AS (
SELECT 100001 UNION ALL SELECT n+1 FROM seq WHERE n<200000
)
SELECT n,n,NOW(),REPEAT('x',32) FROM seq;

SET GLOBAL tidb_ttl_delete_batch_size=10240;
SET GLOBAL tidb_ttl_delete_worker_count=1;
```

3. Arm the pause and enable TTL. Wait until a TTL delete has executed but has not committed:

```sql
ALTER TABLE ttl_mdl_bug.t TTL_ENABLE='ON';
```

4. In another session, add an index while the TTL transaction is paused:

```sql
ALTER TABLE ttl_mdl_bug.t ADD INDEX idx_new(a);
```

5. Resume the TTL delete, wait for the TTL job to settle, and compare the record and index paths:

```sql
SELECT COUNT(*) FROM ttl_mdl_bug.t IGNORE INDEX(idx_new);
SELECT COUNT(*) FROM ttl_mdl_bug.t FORCE INDEX(idx_new);
SET @@tidb_enable_fast_table_check=0;
ADMIN CHECK INDEX ttl_mdl_bug.t idx_new;
ADMIN CHECK TABLE ttl_mdl_bug.t;
```

### 2. What did you expect to see? (Required)

Metadata locking should include the internal session that runs the TTL `DELETE`. `ADD INDEX` should wait for that old-schema statement to commit, or the statement should be aborted/retried after the index enters `writeOnly`. A successful `ADD INDEX` must have exactly one index entry for every remaining row.

### 3. What did you see instead (Required)

`ADD INDEX` completes in about three seconds while the old-schema TTL delete is still paused. When the delete resumes, it removes records without maintaining the new index. The final table has 100,000 records but 110,240 index entries: exactly one in-flight TTL batch remains as orphan index entries. `ADMIN CHECK INDEX` reports error 8003 and `ADMIN CHECK TABLE` reports error 8223; an index lookup can expose a phantom handle/error. The MDL census sees client sessions but misses TTL's registered restricted internal session. The natural timing window is narrow (about 97 ms in the measured setup), but the failpoint reproduction is deterministic.

### 4. What is your TiDB version? (Required)

```text
Release Version: v9.0.0-beta.2.pre
Edition: Community
Git Commit Hash: 1dcb34947971e6265e5a2c3610146e0c44978866
Git Branch: master
Race Enabled: false
Store: tikv
```

The TiDB binary was built with failpoints from the commit above. PD and TiKV were v8.5.7.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.