pingcap / pingcap/tidb

Generated-column TIMESTAMP index becomes inconsistent across session time zones

Open
#66,753 5 comments 0 reactions 1 assignee Claimed by @Daedalus-Icarus View on GitHub
affects-7.5 affects-8.1 affects-8.5 component/ddl severity/critical
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)

```sql
DROP TABLE IF EXISTS t_tz_idx;

CREATE TABLE t_tz_idx (
id INT PRIMARY KEY,
v INT NOT NULL,
updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
updated_hour TIMESTAMP GENERATED ALWAYS AS (
TIMESTAMP(DATE_FORMAT(updated_at, '%Y-%m-%d %H:00:00'))
) VIRTUAL NOT NULL,
KEY idx_v1 (updated_hour, id)
);

-- Build initial data/index in UTC
SET @@session.time_zone = '+00:00';
INSERT INTO t_tz_idx(id, v, updated_at) VALUES (1, 1, '2026-03-05 20:20:57.000');

-- Rebuild index (this does not fix the issue)
ALTER TABLE t_tz_idx DROP INDEX idx_v1;
ALTER TABLE t_tz_idx ADD INDEX idx_v2 (updated_hour, id);

-- Cross-timezone writes to the same row
SET @@session.tidb_txn_assertion_level = 'STRICT';
SET @@session.time_zone = '+00:00';
UPDATE t_tz_idx SET v = v + 1 WHERE id = 1;

SET @@session.time_zone = '-08:00';
UPDATE t_tz_idx SET v = v + 1 WHERE id = 1;

-- Check consistency from UTC
SET @@session.time_zone = '+00:00';
SET @@tidb_enable_fast_table_check = 0;
ADMIN CHECK TABLE t_tz_idx;
ADMIN CHECK INDEX t_tz_idx idx_v2;
```

Observed on the above repro:

```sql
ERROR 8134 (HY000): data inconsistency in table: t_tz_idx, index: idx_v2, col: updated_hour,
index-values:"KindMysqlTime 2026-03-05 20:00:00" != record-values:"KindMysqlTime 2026-03-06 21:00:00"
```

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

After dropping/recreating the index, `ADMIN CHECK TABLE` / `ADMIN CHECK INDEX` should stay consistent.

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

Even after rebuilding (`idx_v2`), the index becomes inconsistent again after mixed session time_zone writes.
`ADMIN CHECK TABLE` and `ADMIN CHECK INDEX` fail with 8134 on generated column `updated_hour`.

In production (v7.5.6), we also observed:

```text
[tikv:8141] assertion failed: key: ..., assertion: Exist
```

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

TiDB v7.5.6

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.