Closed lilinghai closed 1 year ago
mysql> select * from TIKV_REGION_STATUS where table_id=354\G
*************************** 1. row ***************************
REGION_ID: 18173
START_KEY: 7480000000000001FF625F728000000000FF2553370000000000FA
END_KEY: 7480000000000001FF625F728000000000FF5B6D220000000000FA
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 21
EPOCH_VERSION: 182
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 955
APPROXIMATE_KEYS: 1766022
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 2. row ***************************
REGION_ID: 15232
START_KEY: 7480000000000001FF625F728000000000FF5B6D220000000000FA
END_KEY: 7480000000000001FF625F728000000000FF67A4B60000000000FA
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 15
EPOCH_VERSION: 182
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 216
APPROXIMATE_KEYS: 399024
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 3. row ***************************
REGION_ID: 15281
START_KEY: 7480000000000001FF625F728000000000FF67A4B60000000000FA
END_KEY: 7480000000000001FF625F728000000000FF95F1F70000000000FA
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 21
EPOCH_VERSION: 182
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 819
APPROXIMATE_KEYS: 1512530
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 4. row ***************************
REGION_ID: 15391
START_KEY: 7480000000000001FF625F728000000000FFAFE9570000000000FA
END_KEY: 7480000000000001FF625F728000000000FFD971BD0000000000FA
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 15
EPOCH_VERSION: 184
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 734
APPROXIMATE_KEYS: 1356052
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 5. row ***************************
REGION_ID: 15415
START_KEY: 7480000000000001FF625F728000000000FFD971BD0000000000FA
END_KEY: 7480000000000001FF625F728000000000FFFBBDFA0000000000FA
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 21
EPOCH_VERSION: 185
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 607
APPROXIMATE_KEYS: 1121827
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 6. row ***************************
REGION_ID: 15439
START_KEY: 7480000000000001FF625F728000000000FFFBBDFA0000000000FA
END_KEY: 7480000000000001FF625F728000000001FF2003290000000000FA
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 33
EPOCH_VERSION: 186
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 642
APPROXIMATE_KEYS: 1186597
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 7. row ***************************
REGION_ID: 15111
START_KEY: 7480000000000001FF625F720000000000FA
END_KEY: 7480000000000001FF625F728000000000FF2553370000000000FA
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 15
EPOCH_VERSION: 180
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 660
APPROXIMATE_KEYS: 1218906
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 8. row ***************************
REGION_ID: 15323
START_KEY: 7480000000000001FF625F728000000000FF95F1F70000000000FA
END_KEY: 7480000000000001FF625F728000000000FFAFE9570000000000FA
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 19
EPOCH_VERSION: 183
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 459
APPROXIMATE_KEYS: 848942
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 9. row ***************************
REGION_ID: 16296
START_KEY: 7480000000000001FF625F728000000001FF2003290000000000FA
END_KEY: 7480000000000001FF625F728000000001FF3D831F0000000000FA
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 15
EPOCH_VERSION: 188
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 522
APPROXIMATE_KEYS: 965636
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 10. row ***************************
REGION_ID: 15461
START_KEY: 7480000000000001FF625F728000000001FF3D831F0000000000FA
END_KEY: 7480000000000001FF625F728000000001FF59DC760000000000FA
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 21
EPOCH_VERSION: 188
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 501
APPROXIMATE_KEYS: 927888
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
*************************** 11. row ***************************
REGION_ID: 15066
START_KEY: 7480000000000001FF625F728000000001FF59DC760000000000FA
END_KEY: 7480000000000001FF6300000000000000F8
TABLE_ID: 354
DB_NAME: db12
TABLE_NAME: other_handle7
IS_INDEX: 0
INDEX_ID: NULL
INDEX_NAME: NULL
IS_PARTITION: 0
PARTITION_ID: NULL
PARTITION_NAME: NULL
EPOCH_CONF_VER: 13
EPOCH_VERSION: 187
WRITTEN_BYTES: 0
READ_BYTES: 0
APPROXIMATE_SIZE: 431
APPROXIMATE_KEYS: 796576
REPLICATIONSTATUS_STATE: NULL
REPLICATIONSTATUS_STATEID: NULL
Info
mysql> select /*+ read_from_storage(tiflash[db12.other_handle7]) */ count(a) from db12.other_handle7 as of timestamp '2023/08/28 04:15:21'
-> where a not in
-> (select /*+ read_from_storage(tikv[db12.other_handle7]) */ a from db12.other_handle7 as of timestamp '2023/08/28 04:15:21');
+----------+
| count(a) |
+----------+
| 447586 |
+----------+
1 row in set (6.30 sec)
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) */ count(a) from db12.other_handle7 as of timestamp '2023/08/28 04:15:21'
-> where a not in
-> (select /*+ read_from_storage(tiflash[db12.other_handle7]) */ a from db12.other_handle7 as of timestamp '2023/08/28 04:15:21');
+----------+
| count(a) |
+----------+
| 0 |
+----------+
1 row in set (6.28 sec)
mysql> select /*+ read_from_storage(tiflash[db12.other_handle7]) */ a from db12.other_handle7 as of timestamp '2023/08/28 04:15:21'
-> where a not in
-> (select /*+ read_from_storage(tikv[db12.other_handle7]) */ a from db12.other_handle7 as of timestamp '2023/08/28 04:15:21')
-> limit 100;
+---------+
| a |
+---------+
| 2775586 |
| 2775587 |
| 2775588 |
| 2775589 |
| 2775590 |
| 2775591 |
| 2775592 |
| 2775593 |
| 2775594 |
| 2775595 |
| 2775596 |
| 2775597 |
| 2775598 |
| 2775599 |
| 2775600 |
| 2775601 |
| 2775602 |
| 2775603 |
| 2775604 |
| 2775605 |
| 2775606 |
| 2775607 |
| 2775608 |
TiFlash :) selraw * from db_352.t_354 where a = 2775586\G
SELRAW *
FROM db_352.t_354
WHERE a = 2775586
Row 1:
──────
a: 2775586
b: -1133996674
c: X23nGOjnv9aczIZIqN9XnRkp7I6XUfTrdJFIENykkVY4WAn4HU1tdbcQaJoix1MWrghBqcJqicyu6K9wn73gF5gpgXIUKsMfApR6
d: uT5ZLCpqeZQ7Q0hY3xnRHSeZPHwAr12YdVW62C9l9gu2FgQpBzbf7kQvlkW1D28LvYR6CeU6ieYajrZYefdhBw2i4j3C4C5XORYZ
e: 2113500200
g: 3994562896107375000
h: 2477.2477
i: 6109-07-18
j: 7972-10-25 18:46:46
_tidb_rowid: 2776586
./mok --table-id 354 --row-value 2775586
built key: 7480000000000001FF625F728000000000FF2A5A220000000000FA
The key is in region 18173.
However, this region has no snapshot, it does not exist when the query begins. Actually we need to look into 15232
[2023/08/28 04:15:59.338 +08:00] [INFO] [apply.rs:1690] ["execute admin command"] [command="cmd_type: BatchSplit splits { requests { split_key: 7480000000000001FF625F728000000000FF5B6D220000000000FA new_region_id: 18173 new_peer_ids: 18174 new_peer_ids: 18175 new_peer_ids: 18176 new_peer_ids: 18177 new_peer_ids: 18178 } right_derive: true }"] [index=11] [term=6] [peer_id=15237] [region_id=15232]
[2023/08/28 04:15:59.338 +08:00] [INFO] [command.rs:236] ["observe admin command"] [region_epoch="conf_ver: 15 version: 181"] [command="cmd_type: BatchSplit splits { requests { split_key: 7480000000000001FF625F728000000000FF5B6D220000000000FA new_region_id: 18173 new_peer_ids: 18174 new_peer_ids: 18175 new_peer_ids: 18176 new_peer_ids: 18177 new_peer_ids: 18178 } right_derive: true }"] [index=11] [term=6] [peer_id=15237] [region_id=15232]
The region 15232 has
[2023/08/28 03:15:26.889 +08:00] [INFO] [region.rs:624] ["register deleting data in range"] [end_key=7A7480000000000000FFBA5F720300000000FF0014825C014E3031FF734A457858FF7944FF3945455A6C32FF6DFF41523234434F34FFFF6E35374942657544FFFF72487767385034FF46FF753953334563FF5A6AFF4670655046FF6B5747FF536E6D6BFF68695864FF635369FF4F30324756FF7436FF584F4F7A5831FF48FF564366425A5564FFFF4C36696C39346F65FFFF69596961000000FF00FB000000000000F9] [start_key=7A7480000000000000FFBA5F720300000000FF0013A6D7015A7158FF7647466679FF5047FF436762646759FF48FF52676437784773FFFF476A71636A6A5643FFFF4531636F717A6CFF77FF305641533335FF4E77FF5441385153FF4D4672FF33714A70FF35636D56FF754C30FF716967484BFF3647FF366D70745530FF31FF52616847593555FFFF633541505079436CFFFF716E4241000000FF00FB000000000000F9] [region_id=11578]
[2023/08/28 03:15:26.889 +08:00] [INFO] [region.rs:658] ["delete data in range because of stale"] [end_key=7A7480000000000000FFBA5F720300000000FF0014825C014E3031FF734A457858FF7944FF3945455A6C32FF6DFF41523234434F34FFFF6E35374942657544FFFF72487767385034FF46FF753953334563FF5A6AFF4670655046FF6B5747FF536E6D6BFF68695864FF635369FF4F30324756FF7436FF584F4F7A5831FF48FF564366425A5564FFFF4C36696C39346F65FFFF69596961000000FF00FB000000000000F9] [start_key=7A7480000000000000FFBA5F720300000000FF0013A6D7015A7158FF7647466679FF5047FF436762646759FF48FF52676437784773FFFF476A71636A6A5643FFFF4531636F717A6CFF77FF305641533335FF4E77FF5441385153FF4D4672FF33714A70FF35636D56FF754C30FF716967484BFF3647FF366D70745530FF31FF52616847593555FFFF633541505079436CFFFF716E4241000000FF00FB000000000000F9] [region_id=11578]
[2023/08/28 04:15:59.338 +08:00] [INFO] [apply.rs:1690] ["execute admin command"] [command="cmd_type: BatchSplit splits { requests { split_key: 7480000000000001FF625F728000000000FF5B6D220000000000FA new_region_id: 18173 new_peer_ids: 18174 new_peer_ids: 18175 new_peer_ids: 18176 new_peer_ids: 18177 new_peer_ids: 18178 } right_derive: true }"] [index=11] [term=6] [peer_id=15237] [region_id=15232]
[2023/08/28 04:15:59.338 +08:00] [INFO] [apply.rs:2550] ["split region"] [keys="key 7480000000000001FF625F728000000000FF5B6D220000000000FA"] [region="id: 15232 start_key: 7480000000000001FF625F728000000000FF2553370000000000FA end_key: 7480000000000001FF625F728000000000FF67A4B60000000000FA region_epoch { conf_ver: 15 version: 181 } peers { id: 15233 store_id: 1 } peers { id: 15234 store_id: 12 } peers { id: 15235 store_id: 18 } peers { id: 15237 store_id: 28 role: Learner } peers { id: 15239 store_id: 257 role: Learner }"] [peer_id=15237] [region_id=15232]
[2023/08/28 04:15:59.338 +08:00] [INFO] [command.rs:236] ["observe admin command"] [region_epoch="conf_ver: 15 version: 181"] [command="cmd_type: BatchSplit splits { requests { split_key: 7480000000000001FF625F728000000000FF5B6D220000000000FA new_region_id: 18173 new_peer_ids: 18174 new_peer_ids: 18175 new_peer_ids: 18176 new_peer_ids: 18177 new_peer_ids: 18178 } right_derive: true }"] [index=11] [term=6] [peer_id=15237] [region_id=15232]
See
[2023/08/28 04:15:21.673 +08:00] [INFO] [verify.go:163] ["verify successfully"] [tikv="[12100000]"] [tiflash="[12100000]"]
We check previous timestamps, they do not match either
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:19';
+----------+
| count(*) |
+----------+
| 12099999 |
+----------+
1 row in set (0.92 sec)
mysql> select /*+ read_from_storage(tiflash[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:19';
+----------+
| count(*) |
+----------+
| 12100000 |
+----------+
1 row in set (0.19 sec)
mysql> select /*+ read_from_storage(tiflash[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:18';
+----------+
| count(*) |
+----------+
| 12100000 |
+----------+
1 row in set (0.17 sec)
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:18';
+----------+
| count(*) |
+----------+
| 11934445 |
+----------+
1 row in set (0.91 sec)
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:00';
+----------+
| count(*) |
+----------+
| 11714715 |
+----------+
1 row in set (0.84 sec)
mysql> select /*+ read_from_storage(tiflash[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:00';
+----------+
| count(*) |
+----------+
| 12100000 |
+----------+
1 row in set (0.19 sec)
mysql> select /*+ read_from_storage(tiflash[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:14:00';
+----------+
| count(*) |
+----------+
| 12100000 |
+----------+
1 row in set (0.18 sec)
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:14:00';
+----------+
| count(*) |
+----------+
| 11870175 |
+----------+
1 row in set (0.84 sec)
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:10:00';
+----------+
| count(*) |
+----------+
| 11795809 |
+----------+
1 row in set (0.80 sec)
mysql> select /*+ read_from_storage(tiflash[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:10:00';
+----------+
| count(*) |
+----------+
| 12100000 |
+----------+
1 row in set (0.19 sec)
Moreover, we have noticed the result of stale read does not remain consistent in TiKV
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:19';
+----------+
| count(*) |
+----------+
| 11795809 |
+----------+
1 row in set (0.77 sec)
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:19';
+----------+
| count(*) |
+----------+
| 11934445 |
+----------+
1 row in set (0.81 sec)
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:19';
+----------+
| count(*) |
+----------+
| 12035730 |
+----------+
1 row in set (0.71 sec)
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:19';
+----------+
| count(*) |
+----------+
| 11961366 |
+----------+
1 row in set (0.78 sec)
This is due to index scan not match on TiKV's side. This is not a TiFlash problem for now
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) ignore_index(db12.other_handle7,PRIMARY) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:21';
+----------+
| count(*) |
+----------+
| 12100000 |
+----------+
1 row in set (0.71 sec)
mysql> select /*+ read_from_storage(tikv[db12.other_handle7]) use_index(db12.other_handle7,PRIMARY) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:21';
+----------+
| count(*) |
+----------+
| 11714716 |
+----------+
1 row in set (0.81 sec)
mysql> select /*+ read_from_storage(tiflash[db12.other_handle7]) */ count(*) from db12.other_handle7 as of timestamp '2023/08/28 04:15:21';
+----------+
| count(*) |
+----------+
| 12100000 |
+----------+
1 row in set (0.17 sec)
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
3. What did you see instead (Required)
4. What is your TiFlash version? (Required)