data inconsistency in table after upgrade cluster v7.5.0 to nightly with dist task add index

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. deploy a 7.5.0 cluster
   2. set global tidb_ddl_enable_fast_reorg=1; set global tidb_enable_dist_task=1
   3. do add index
   4. duraing add index, upgrade the cluster to nightly (c76fe3f)
   5. check the add index ddl jobs

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

add index success and admin check index success

3. What did you see instead (Required)

Error 8223 (HY000): data inconsistency in table: sbtest1, index: idxc, handle: 3638162, index-values:"" != record-values:"handle: 3
638162, values: [KindString 57802534654-92849495096-76700988535-10378989958-21593928040-21624571867-11827492765-27178917465-96771938022-517
64534826 KindString 78352395649-41653856744-14528110070-99012720503-38484627267]"

for more information, you can find from here

4. What is your TiDB version? (Required)

v7.5.0 -> nightly (c76fe3f)

[tangenta]

tidb/pkg/ddl/backfilling_dist_scheduler.go

Line 283 in 671e980

recordRegionMetas, err := regionCache.LoadRegionsInKeyRange(tikv.NewBackofferWithVars(context.Background(), 20000, nil), startKey, endKey)

When PD restarts, LoadRegionsInKeyRange() may return regions with "holes". The start key and end key of two adjacent subtasks are not equal. As a result, some row records not being read.

TiDB logs:

-- tidb 3
[2024/02/21 21:17:37.814 +08:00] [INFO] [backfilling.go:387] ["split table range from PD"] [category=ddl] [physicalTableID=232] ["start key"=7480000000000000e85f728000000000000001] ["end key"=7480000000000000e85f7280000000001350c3]

-- tidb 1
[2024/02/21 21:17:37.935 +08:00] [INFO] [backfilling.go:387] ["split table range from PD"] [category=ddl] [physicalTableID=232] ["start key"=7480000000000000e85f728000000000404e21] ["end key"=7480000000000000e85f7280000000004e5a7500]

-- tidb 2
[2024/02/21 21:17:38.037 +08:00] [INFO] [backfilling.go:387] ["split table range from PD"] [category=ddl] [physicalTableID=232] ["start key"=7480000000000000e85f728000000000202936] ["end key"=7480000000000000e85f72800000000039e153]

MySQL [sbtest1]> select tidb_decode_key('7480000000000000e85f728000000000000001');
+-----------------------------------------------------------+
| tidb_decode_key('7480000000000000e85f728000000000000001') |
+-----------------------------------------------------------+
| {"id":1,"table_id":"232"}                                 |
+-----------------------------------------------------------+
1 row in set (0.01 sec)

MySQL [sbtest1]> select tidb_decode_key('7480000000000000e85f7280000000001350c3');
+-----------------------------------------------------------+
| tidb_decode_key('7480000000000000e85f7280000000001350c3') |
+-----------------------------------------------------------+
| {"id":1265859,"table_id":"232"}                           |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [sbtest1]> select tidb_decode_key('7480000000000000e85f728000000000202936');
+-----------------------------------------------------------+
| tidb_decode_key('7480000000000000e85f728000000000202936') |
+-----------------------------------------------------------+
| {"id":2107702,"table_id":"232"}                           |
+-----------------------------------------------------------+
1 row in set (0.01 sec)

MySQL [sbtest1]> select tidb_decode_key('7480000000000000e85f72800000000039e153');
+-----------------------------------------------------------+
| tidb_decode_key('7480000000000000e85f72800000000039e153') |
+-----------------------------------------------------------+
| {"id":3793235,"table_id":"232"}                           |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [sbtest1]> select tidb_decode_key('7480000000000000e85f728000000000404e21');
+-----------------------------------------------------------+
| tidb_decode_key('7480000000000000e85f728000000000404e21') |
+-----------------------------------------------------------+
| {"id":4214305,"table_id":"232"}                           |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [sbtest1]> select tidb_decode_key('7480000000000000e85f7280000000004e5a7500');
+-------------------------------------------------------------+
| tidb_decode_key('7480000000000000e85f7280000000004e5a7500') |
+-------------------------------------------------------------+
| 7480000000000000e85f7280000000004e5a7500                    |
+-------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

MySQL [sbtest1]> select tidb_decode_key('7480000000000000e85f7280000000004e5a75');
+-----------------------------------------------------------+
| tidb_decode_key('7480000000000000e85f7280000000004e5a75') |
+-----------------------------------------------------------+
| {"id":5134965,"table_id":"232"}                           |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

MySQL [sbtest1]> select count(1) from sbtest2 where (id >= 1 and id < 1265859) or (id >= 2107702 and id < 3793235) or (id >= 4214305 and id <= 5134965);
+----------+
| count(1) |
+----------+
|  3767390 |
+----------+
1 row in set (0.22 sec)

+--------+---------+-------------------------+-------------------------------------+--------------+-----------+-----------------+-----------+---------------------+---------------------+---------------------+--------+
| JOB_ID | DB_NAME | TABLE_NAME              | JOB_TYPE                            | SCHEMA_STATE | SCHEMA_ID | TABLE_ID        | ROW_COUNT | CREATE_TIME         | START_TIME          | END_TIME            | STATE  |
+--------+---------+-------------------------+-------------------------------------+--------------+-----------+-----------------+-----------+---------------------+---------------------+---------------------+--------+
|    256 | sbtest1 | sbtest2                 | add index /* ingest */              | public       |       102 |             232 |   3767390 | 2024-02-21 21:16:26 | 2024-02-21 21:17:29 | 2024-02-21 21:17:48 | synced |
+--------+---------+-------------------------+-------------------------------------+--------------+-----------+-----------------+-----------+---------------------+---------------------+---------------------+--------+

[Benjamin2037]

Will this problem affect add index since 6.5?