ADD UNIQUE INDEX on a multi-valued index can raise a false duplicate (or corrupt the DDL job) when added with another unique index under concurrent DML
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
### 1. Minimal reproduce step (Required)
When `ALTER TABLE ... ADD UNIQUE INDEX` adds a **multi-valued index (MVI)** in the same
multi-schema job as another unique index, and a concurrent DML writes an index entry during write
reorganization, the backfill duplicate check can misclassify which index a generated key belongs
to. The visible result is a **false duplicate-key error** that rolls back a valid online DDL.
Setup (the globals just widen the online-DDL window so the race is easy to hit on a release build;
they are not the trigger):
```sql
SET GLOBAL tidb_enable_dist_task = OFF;
SET GLOBAL tidb_ddl_enable_fast_reorg = OFF;
SET GLOBAL tidb_ddl_reorg_worker_cnt = 1;
CREATE DATABASE ai_mvi; USE ai_mvi;
CREATE TABLE t(a INT PRIMARY KEY, b INT, j JSON);
SPLIT TABLE t BETWEEN (0) AND (100000) REGIONS 50;
SET SESSION cte_max_recursion_depth = 200000;
INSERT INTO t
WITH RECURSIVE seq(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 100000)
SELECT n, n, CONCAT('[', n, ',', n+1000000, ']') FROM seq;
```
Session A — add an MVI unique index together with a multi-column unique index:
```sql
ALTER TABLE t
ADD UNIQUE INDEX u_mvi((CAST(j AS SIGNED ARRAY))),
ADD UNIQUE INDEX u_ab(a, b);
```
Session B — while Session A is in write reorganization, repeatedly update high (not-yet-backfilled)
rows, changing only the `u_ab` key (the MVI value is untouched):
```sql
UPDATE t SET b = b + 7 WHERE a = 99000; -- fire on a spread of high `a` values in a loop
```
On a build with failpoints, `PUT /fail/github.com/pingcap/tidb/pkg/ddl/mockBackfillSlow=return(true)`
makes it deterministic; on a release build it reproduces within a few seconds of racing.
### 2. What did you expect to see? (Required)
The online `ADD UNIQUE INDEX` should succeed. There is no logical duplicate — the index entry the
backfill finds was written by the concurrent DML for the **same row**, and must be classified as
"already written, skip it". Every single-index control succeeds:
| add | concurrent DML | result |
| --- | --- | --- |
| `u_mvi` only | `UPDATE ... SET b=b+7, j='[..]' WHERE a=X` | succeeds, `ADMIN CHECK` passes |
| `u_mvi` + one-column `u_b(b)` | `UPDATE ... SET j='[..]' WHERE a=X` | succeeds |
### 3. What did you see instead (Required)
The `ADD UNIQUE INDEX` fails and rolls back with a false duplicate:
```text
ERROR 1062 (23000): Duplicate entry '98800' for key 't.u_mvi'
```
There is no real duplicate: after the rollback, `ADMIN CHECK TABLE t` passes and the row is intact.
The failure only appears when the MVI unique index is added **alongside a second unique index whose
metadata has a different shape** (e.g. the two-column `u_ab`). A related variant (concurrent DML
that changes the MVI value itself) can leave the DDL job stuck in write reorganization and surface
`invalid encoded key` on cancel — i.e. the same misclassification can also corrupt the DDL job, not
only raise a false duplicate.
### 4. What is your TiDB version? (Required)
```
Release Version: v9.0.0-beta.2.pre-1774-g81ec977cb8
Git Commit Hash: 81ec977cb8bf97e0c9805dfc0be8ffcbd4b0bbeb
Edition: Community
Store: tikv
```
Likely root cause — flattened-key owner recovered by ordinal modulo index count, which MVI breaks
In `pkg/ddl/index.go`, `addIndexTxnWorker.batchCheckUniqueKey` builds `w.batchCheckKeys` by
iterating `idxRecords`; for a multi-valued index, `GenIndexKVIter` can emit **several** keys for one
record, all appended to the flattened `batchCheckKeys` (with the correct `recordIdx`). The second
loop then recovers the owning index by ordinal:
```go
for i, key := range w.batchCheckKeys {
...
idx := w.indexes[i%len(w.indexes)] // <-- i is the flattened key position, not the record ordinal
val, found := batchVals[string(key)]
if found && w.distinctCheckFlags[i] {
w.checkHandleExists(idx.Meta(), key, val, idxRecords[w.recordIdx[i]].handle)
}
...
}
```
`i % len(w.indexes)` only identifies the right index if every record emits exactly one key (the
in-code comment states this assumption: "keep `idxRecords[i]` belonging to `indexes[i%len(indexes)]`").
Once an MVI emits 2+ keys, the flattened positions shift and `idx` points at the wrong index from the
second MVI key onward. `checkHandleExists` then decodes the found entry's handle with the **wrong
index metadata** (e.g. the two-column `u_ab` shape applied to a one-column MVI key). A mis-decoded
handle compares unequal to the row's real handle → false duplicate; a decode that fails outright →
`invalid encoded key`. In principle a mis-decode that coincidentally compares *equal* could also skip
a genuine duplicate, so the uniqueness check on the online-added MVI index is not sound here.
Fix direction: carry the owning index ordinal for each flattened generated key (store it alongside
`recordIdx` / `distinctCheckFlags`) and use that stored owner in the duplicate classification, instead
of deriving ownership from `flattenedKeyOrdinal % len(indexes)` once `GenIndexKVIter` may emit more
than one key per record.
Contributor guide
Research direction
Start in pkg/ddl/index.go at addIndexTxnWorker.batchCheckUniqueKey and trace how GenIndexKVIter populates batchCheckKeys, recordIdx, and distinctCheckFlags. Verify the owner of each flattened key is preserved for duplicate classification rather than inferred from its ordinal, then exercise the reported concurrent MVI and multi-index DDL cases and confirm valid DDL succeeds without false duplicates or invalid encoded keys.
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
- Quiet
- Clarity
- Clearly specified
- Newbie friendliness
- 48/100