TIDB使用sync_diff_inspector工具数据校验问题

[TidbH]

为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。

• 【TiDB 版本】：v3.0.4
• 【问题描述】：使用sync_diff_inspector工具校验与mysql库数据一致性出现如下告警，

[2019/12/19 14:10:45.653 +08:00] [WARN] [common.go:646] [“exec sql slow”] [sql=“REPLACE INTO sync_diff_inspector.chunk VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);”] [args=“[15,"target","tidb1","t1","(((ID \u003e ?)) AND ((ID \u003c= ?)) AND TRUE)","","{\"id\":15,\"bounds\":[{\"column\":\"ID\",\"lower\":\"16241\",\"upper\":\"2147483647\",\"has-lower\":true,\"has-upper\":true}],\"where\":\"(((ID \\u003e ?)) AND ((ID \\u003c= ?)) AND TRUE)\",\"args\":[\"16241\",\"2147483647\"],\"state\":\"checking\"}","checking","2019-12-19T14:10:45.517331379+08:00"]”] [take=136.414552ms]

这个告警代表啥意思？ REPLACE INTO sync_diff_inspector . chunk VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);” 这个是写入检查点吗，还是？在哪里可以查看到sync_diff_inspector表信息。

注：校验后查看fix.sql 并没有出现不一致数据。

[QBin (Bin)]

该 Warning 是由于下游 TiDB 更新 sync_diff_inspector.chunk的时候超过 100 ms 就会有提示。下游 TiDB 是会建立一个 sync_diff_inspector 的库用于记录 sync_diff_inspector 工具校验的过程数据。

[TidbH]

sync_diff_inspector库的chunk表的时间和summary表的时间为啥会偏差8小时？详见：

[TiDB]xxxx 10:17:35 sync_diff_inspector> select * from chunk order by chunk_id desc limit 5;
±---------±------------±---------±----------±------------------------------------------±---------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±--------------------+
| chunk_id | instance_id | schema | table | range | checksum | chunk_str | state | update_time |
±---------±------------±---------±----------±------------------------------------------±---------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±--------------------+
| 410 | target | xxxx | xxxx | ((id > ?) AND TRUE) | | {“id”:410,“bounds”:[{“column”:“id”,“lower”:“796139”,“upper”:“”,“has-lower”:true,“has-upper”:false}],“where”:“((id \u003e ?) AND TRUE)”,“args”:[“796139”],“state”:“success”} | success | 2019-12-20 02:17:11 |
| 409 | target | xxxx | xxxx | (((id > ?)) AND ((id <= ?)) AND TRUE) | | {“id”:409,“bounds”:[{“column”:“id”,“lower”:“794670”,“upper”:“796139”,“has-lower”:true,“has-upper”:true}],“where”:“(((id \u003e ?)) AND ((id \u003c= ?)) AND TRUE)”,“args”:[“794670”,“796139”],“state”:“success”} | success | 2019-12-20 02:17:11 |
| 408 | target | xxxx | xxxx | (((id > ?)) AND ((id <= ?)) AND TRUE) | | {“id”:408,“bounds”:[{“column”:“id”,“lower”:“794091”,“upper”:“794670”,“has-lower”:true,“has-upper”:true}],“where”:“(((id \u003e ?)) AND ((id \u003c= ?)) AND TRUE)”,“args”:[“794091”,“794670”],“state”:“success”} | success | 2019-12-20 02:17:13 |
| 407 | target | xxxx | xxxx | (((id > ?)) AND ((id <= ?)) AND TRUE) | | {“id”:407,“bounds”:[{“column”:“id”,“lower”:“793485”,“upper”:“794091”,“has-lower”:true,“has-upper”:true}],“where”:“(((id \u003e ?)) AND ((id \u003c= ?)) AND TRUE)”,“args”:[“793485”,“794091”],“state”:“success”} | success | 2019-12-20 02:17:14 |
| 406 | target | xxxx | xxxx | (((id > ?)) AND ((id <= ?)) AND TRUE) | | {“id”:406,“bounds”:[{“column”:“id”,“lower”:“792043”,“upper”:“793485”,“has-lower”:true,“has-upper”:true}],“where”:“(((id \u003e ?)) AND ((id \u003c= ?)) AND TRUE)”,“args”:[“792043”,“793485”],“state”:“success”} | success | 2019-12-20 02:17:10 |
±---------±------------±---------±----------±------------------------------------------±---------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±--------------------+
5 rows in set (0.03 sec)

[TiDB]xxxx 10:36:35 sync_diff_inspector> select * from summary order by update_time desc limit 5;
±---------±--------------------------±----------±------------------±-----------------±-----------------±--------±---------------------------------±--------------------+
| schema | table | chunk_num | check_success_num | check_failed_num | check_ignore_num | state | config_hash | update_time |
±---------±--------------------------±----------±------------------±-----------------±-----------------±--------±---------------------------------±--------------------+
| xxxx | xxx | 1 | 1 | 0 | 0 | success | f64c7defe2d609bf4e1a28bdbbeaa135 | 2019-12-20 10:18:52 |
| xxxx | xxx | 1 | 1 | 0 | 0 | success | 65e721e92114856cd04626ead5299aa7 | 2019-12-20 10:18:51 |
| xxxx | sns_score_exchange_config | 1 | 1 | 0 | 0 | success | 82be08544371ef9f1d1bb28e2fb9e6b0 | 2019-12-20 10:18:50 |
| xxxx | xxx | 1 | 1 | 0 | 0 | success | 42c86161f277e2930171bc5e9e3ea444 | 2019-12-20 10:18:49 |
| xxxx | xxx | 1 | 1 | 0 | 0 | success | 2e04addc4f02672f1fc0fafab11de4a6 | 2019-12-20 10:18:49 |
±---------±--------------------------±----------±------------------±-----------------±-----------------±--------±---------------------------------±--------------------+
5 rows in set (0.01 sec)

[GangShen]

请问，上下游有时区偏差吗

[TidbH]

没有。如果有时区问题，哪不应该一个表正常一个表不正常吧。

[WentaoJin]

可以查看下数据库得时区或者服务器得时区是否有问题