pingcap / pingcap/tidb

TTL jobs can skip expired rows when clustered primary keys contain ENUM

Open
#70,764 1 comment 0 reactions 0 assignees View on GitHub
affects-25.10 affects-26.3 affects-7.1 affects-7.5 affects-8.1 affects-8.5 severity/major sig/sql-infra type/bug
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)

The following steps use only SQL on an unmodified TiDB built from the current `master` branch. No source change, failpoint, or test hook is required.

```sql
SET GLOBAL tidb_ttl_job_enable = ON;
SET GLOBAL tidb_ttl_scan_batch_size = 1;
SET GLOBAL tidb_ttl_delete_batch_size = 1;

DROP DATABASE IF EXISTS ttl_issue_repro;
CREATE DATABASE ttl_issue_repro;

CREATE TABLE ttl_issue_repro.t_enum (
status ENUM('z','a','b') NOT NULL,
id INT NOT NULL,
expire_at DATETIME NOT NULL,
PRIMARY KEY (status, id) CLUSTERED
) TTL = expire_at + INTERVAL 1 DAY
TTL_ENABLE = 'ON'
TTL_JOB_INTERVAL = '24h';

INSERT INTO ttl_issue_repro.t_enum VALUES
('z', 1, '2000-01-01 00:00:00'),
('a', 2, '2000-01-01 00:00:00'),
('b', 3, '2000-01-01 00:00:00');
```

Wait until the first TTL job finishes:

```sql
SELECT t.TABLE_NAME, s.LAST_JOB_FINISH_TIME, s.LAST_JOB_SUMMARY
FROM mysql.tidb_ttl_table_status AS s
JOIN information_schema.tables AS t ON t.TIDB_TABLE_ID = s.TABLE_ID
WHERE t.TABLE_SCHEMA = 'ttl_issue_repro'
AND t.TABLE_NAME = 't_enum';

SELECT status + 0 AS physical_value, status, id
FROM ttl_issue_repro.t_enum
ORDER BY status, id;
```

The first job was reported as finished with this summary:

```text
{"total_rows":1,"success_rows":1,"error_rows":0,"total_scan_task":1,"scheduled_scan_task":1,"finished_scan_task":1}
```

The table still contained:

```text
physical_value status id
2 a 2
3 b 3
```

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

The first TTL job should scan and delete all three expired rows. Its summary should report three successful rows, and the table should be empty.

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

The TTL job reports `finished` after scanning and deleting only the first physical ENUM value. Rows after that value are skipped until a later TTL job starts from the beginning.

TTL paginates a clustered primary key with SQL cursor predicates. ENUM is physically ordered by its ordinal, but the cursor is currently emitted as a display string. When declaration order differs from lexical order, the cursor no longer represents the physical scan position.

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

The reproduction above was verified with a real TiKV started by TiUP and a clean worktree at `master` commit `db35d47066648fe73abce6318d53fc625df51490`.

```text
Release Version: v9.0.0-beta.2.pre-2176-gdb35d47066
Edition: Community
Git Commit Hash: db35d47066648fe73abce6318d53fc625df51490
Git Branch: codex/fix-ttl-enum-set-pagination
UTC Build Time: 2026-08-31 15:42:59
GoVersion: go1.26.5-X:nodwarf5
Race Enabled: false
Check Table Before Drop: false
Store: tikv
Kernel Type: Classic
```

Contributor guide

Open the contributing guide

Research direction

Start by running the SQL reproduction against TiDB, then trace the TTL scan's clustered-primary-key pagination and compare ENUM physical ordinals with the cursor's display-string values. Done means the first TTL job deletes all three expired rows and reports three successful rows with no remaining table rows.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
55/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.