More than one row with the same primary key when inject fault

[yanghy233]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Deploy 5 tikv nodes, 3 pd nodes and 1 tidb node in K8S.

1. running transaction workload.
2. one thread execute: alter table table1 coalesce partition 1;
3. inject IOChaos.
4. Recovery before the transaction workload finish.
5. admin check table but failed.

more details can get from the following files.

admin_check_inconsistency_8223.zip

in zip:

• schema_and_init_data: initial schema, state and data.
• transaction_workload: execute concurrently.
• ddl_sequence: DDL threads execute during transaction workload.
• fault_injection: inject network partition.
• logs: tidb and tikv logs.

After all workload done, table1 schema:

Mysql > show create table table1;
CREATE TABLE `table1` (
  `pkId` int DEFAULT NULL,
  `pkAttr0` int NOT NULL,
  `commonAttr0_0` varchar(10) DEFAULT NULL,
  `commonAttr1_0` double(10,2) DEFAULT NULL,
  `commonAttr2_0` double(10,2) DEFAULT NULL,
  `commonAttr3_0` decimal(10,2) DEFAULT NULL,
  `commonAttr4_0` double(10,2) DEFAULT NULL,
  PRIMARY KEY (`pkAttr0`) /*T![clustered_index] NONCLUSTERED */,
  KEY `table1index_pk` (`pkAttr0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY KEY (`pkAttr0`) PARTITIONS 1

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

After all workloads:

mysql> admin check table table1;
-- ok

mysql > select count(*) from table1;
+----------+
| count(*) |
+----------+
|       60 |
+----------+
1 row in set (0.01 sec)

mysql> select * from table1 order by `pkId`;
-- ok (60 rows)

3. What did you see instead (Required)

After all workloads:

mysql> admin check table table1;
-- ERROR 8223 (HY000): data inconsistency in table: table1, index: PRIMARY, handle: 3, index-values:"" != record-values:"handle: 3, values: [KindInt64 26]"

mysql > select count(*) from table1;
+----------+
| count(*) |
+----------+
|       60 |
+----------+
1 row in set (0.01 sec)


mysql> select * from table1 order by `pkId`;
+------+---------+---------------+---------------+---------------+---------------+---------------+
| pkId | pkAttr0 | commonAttr0_0 | commonAttr1_0 | commonAttr2_0 | commonAttr3_0 | commonAttr4_0 |
+------+---------+---------------+---------------+---------------+---------------+---------------+
|    0 |       3 | JgRq          |         95920 |         43560 |      88248.00 |         98193 |
|    1 |       9 | j12t          |         10690 |         42086 |      62601.00 |         43560 |
|    2 |      26 | u             |         10690 |         43560 |       7706.00 |         48231 |
|    2 |      26 | ZEp5z         |         10690 |         43560 |      82107.00 |         31888 |
|    3 |      29 | FLHo          |         43560 |         98193 |      53770.00 |         83204 |
|    4 |      39 | 4TNLwS        |         43560 |         16265 |      36410.00 |         93912 |
|    5 |      45 | K             |         10690 |         48231 |       7706.00 |         16265 |
|    6 |      62 | kKWnE         |         10690 |         16265 |       4874.00 |         95920 |
|    7 |      65 | 6             |         16265 |         95920 |      13677.00 |         31888 |
|    8 |      75 | SHDC6P        |         95920 |         95920 |      57359.00 |         98193 |
|    9 |      81 | vqxl4         |         16265 |         31888 |      14758.00 |         48231 |
|   10 |      96 | 1pOcqj        |         31888 |         31888 |      82377.00 |         93912 |
|   11 |     105 | azyMT         |         83204 |         83204 |      49523.00 |         83204 |
|   12 |     113 | b5Th          |         95920 |         95920 |      51293.00 |         95920 |
|   13 |     122 | WrsIMS        |         98193 |         98193 |      42308.00 |         98193 |
|   14 |     133 | e             |         31888 |         31888 |      52938.00 |         31888 |
|   15 |     141 | k             |         98193 |         98193 |      58418.00 |         98193 |
|   16 |     149 | T3ZnOT5       |         16265 |         16265 |      35129.00 |         16265 |
|   17 |     158 | bpWuQpg       |         10690 |         10690 |      52375.00 |         10690 |
|   18 |     170 | k             |         98193 |         98193 |      58418.00 |         98193 |
|   19 |     178 | AEMrNCH       |         43560 |         43560 |      13677.00 |         43560 |
|   20 |     183 | h5C           |         42086 |         42086 |      53083.00 |         42086 |
|   21 |     193 | jYs           |         95920 |         95920 |      58335.00 |         95920 |
|   22 |     206 | WRC           |         93912 |         93912 |      39486.00 |         93912 |
|   23 |     214 | xkZNejE       |         48231 |         48231 |      89387.00 |         48231 |
|   24 |     219 | 6LVP          |         16265 |         16265 |       8165.00 |         16265 |
|   25 |     229 | UOV3OBh       |         42086 |         42086 |      38101.00 |         42086 |
|   26 |     234 | AEMrNCH       |         43560 |         43560 |      13677.00 |         43560 |
|   27 |     246 | T3ZnOT5       |         16265 |         16265 |      35129.00 |         16265 |
|   28 |     254 | rGJ           |         16265 |         16265 |      77037.00 |         16265 |
|   29 |     269 | vglM          |         10690 |         10690 |      82645.00 |         10690 |
|   30 |     270 | uMta          |         43560 |         43560 |      79459.00 |         43560 |
|   31 |     282 | VsShWkG       |         48231 |         48231 |      38951.00 |         48231 |
|   32 |     290 | 61d           |         10690 |         10690 |       7912.00 |         10690 |
|   33 |     305 | ocLZ          |         83204 |         83204 |      72398.00 |         83204 |
|   34 |     311 | XVaPs         |         16265 |         16265 |      44323.00 |         16265 |
|   35 |     319 | j12t          |         93912 |         93912 |      57359.00 |         93912 |
|   36 |     326 | 1pOcqj        |         16265 |         16265 |       2915.00 |         16265 |
|   37 |     340 | X3            |         10690 |         10690 |      43968.00 |         10690 |
|   38 |     347 | Z6Dpd         |         43560 |         43560 |      48483.00 |         43560 |
|   39 |     355 | xkZNejE       |         48231 |         48231 |      89387.00 |         48231 |
|   40 |     366 | 6LVP          |         16265 |         16265 |       8165.00 |         16265 |
|   41 |     376 | FuKPO         |         48231 |         48231 |      23780.00 |         48231 |
|   42 |     378 | jYs           |         95920 |         95920 |      58335.00 |         95920 |
|   43 |     388 | v             |         93912 |         93912 |      81249.00 |         93912 |
|   44 |     397 | vqxl4         |         16265 |         16265 |      86415.00 |         16265 |
|   45 |     406 | o             |         43560 |         43560 |      67216.00 |         43560 |
|   46 |     414 | JgRq          |         83204 |         83204 |      24076.00 |         83204 |
|   47 |     424 | uMta          |         43560 |         43560 |      79459.00 |         43560 |
|   48 |     434 | 3LBYk         |         43560 |         43560 |       5475.00 |         43560 |
|   49 |     442 | i1uaT         |         48231 |         48231 |      54205.00 |         48231 |
|   50 |     450 | c55ZOh        |         16265 |         16265 |      52765.00 |         16265 |
|   51 |     460 | sbpu          |         31888 |         31888 |      77462.00 |         31888 |
|   52 |     470 | xR53s         |         43560 |         43560 |      88248.00 |         43560 |
|   53 |     478 | xzpfOp        |         93912 |         93912 |      98252.00 |         93912 |
|   54 |     486 | xzpfOp        |         93912 |         93912 |      98252.00 |         93912 |
|   55 |     498 | 5MrFFKP       |         93912 |         93912 |       6147.00 |         93912 |
|   56 |     506 | jYs           |         95920 |         95920 |      58335.00 |         95920 |
|   57 |     521 | MTWHV         |         98193 |         98193 |      28175.00 |         98193 |
|   58 |     528 | zU            |         98193 |         98193 |      99337.00 |         98193 |
|   59 |     535 | q5pLE         |         95920 |         95920 |      75259.00 |         95920 |
+------+---------+---------------+---------------+---------------+---------------+---------------+
61 rows in set (0.01 sec)

Count(*) returns 60 rows;

But select * returns 61 rows;

And we find that more than one row of columns with the same primary key (pkAttr0).

such as: pkAttr0 = 26

4. What is your TiDB version? (Required)

TiDB v8.5.1

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()
   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.5.1
Edition: Community
Git Commit Hash: fea86c8e35ad4a86a5e1160701f99493c2ee547c
Git Branch: HEAD
UTC Build Time: 2025-01-16 07:38:34
GoVersion: go1.23.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[yanghy233]

@Defined2014 This case is similar to #59672, but it still occurred on TiDB v8.5.1.
Therefore, I will close the latter issue and keep this one.

[yanghy233]

/label affects-8.5

[Defined2014]

Executing coalesce partition will result in an error. Should I execute add partition?

mysql> alter table table1 coalesce partition 1;
ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead

[yanghy233]

Executing coalesce partition will result in an error. Should I execute add partition?

mysql> alter table table1 coalesce partition 1;
ERROR 1508 (HY000): Cannot remove all partitions, use DROP TABLE instead

Sorry, perhaps the explanation above was a bit unclear. Here is the original schema of table1: (in zip file)

create table `table1`
(
    `pkId`          integer,
    `pkAttr0`       integer,
    `commonAttr0_0` varchar(10),
    `commonAttr1_0` double(10, 2),
    `commonAttr2_0` double(10, 2),
    `commonAttr3_0` decimal(10, 2),
    `commonAttr4_0` double(10, 2),
    primary key (`pkAttr0`) NONCLUSTERED
) ROW_FORMAT = REDUNDANT PARTITION BY KEY (`pkAttr0`) PARTITIONS 2;

Before the change, the number of partitions was 2.
After running the workload, the number of partitions became 1.
Therefore, the execution of COALESCE PARTITION in that thread was successful.
But finally return two rows with the same primary key.