The repair SQL generated by sync_diff_inspector is incorrect

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: sync_diff_inspector生成的修复sql不对

| username: TiDBer_yUoxD0vR

[TiDB Usage Environment] Production / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
Using sync_diff_inspector to compare data, the downstream has one more row than the upstream
±------±-----±-----±--------------------+
| id | c1 | c2 | ctamp |
| 30001 | NULL | NULL | 2023-03-30 10:26:51 |

The ctamp field is of timestamp type, defined as: ctamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

The generated repair SQL is:
DELETE FROM dbtest13.t12 WHERE id = 30001 AND c1 is NULL AND c2 is NULL AND ctamp = ‘2023-03-30 02:26:51’ LIMIT 1;

The ctamp condition is in UTC time, which is 8 hours off from the original value. How to solve this?

[Encountered Issue: Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]

| username: 特雷西-迈克-格雷迪 | Original post link

Does the table have a primary key? Without a primary key or unique index, the generated repair file will be incorrect.

| username: TiDBer_yUoxD0vR | Original post link

There is a primary key, and it seems to be a timestamp type. There is a time zone issue, with an 8-hour difference.

testuser112@192.168.133.112:3306 11:47:42 [dbtest13]>select *from t12;
±------±-----±-----±--------------------+
| id | c1 | c2 | ctamp |
±------±-----±-----±--------------------+
| 1 | a | 12 | 2023-03-27 14:33:53 |
| 2 | a | 13 | 2023-03-27 14:34:06 |
| 3 | a | 14 | 2023-03-27 14:34:07 |
| 4 | a | 15 | 2023-03-27 14:34:09 |
| 30001 | NULL | NULL | 2023-03-30 10:26:51 |
±------±-----±-----±--------------------+
5 rows in set (0.00 sec)

testuser112@192.168.133.112:3306 11:47:44 [dbtest13]>show create table t12\G
*************************** 1. row ***************************
Table: t12
Create Table: CREATE TABLE t12 (
id bigint NOT NULL AUTO_INCREMENT,
c1 varchar(2) COLLATE utf8mb4_bin DEFAULT NULL,
c2 int DEFAULT NULL,
ctamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=30002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

| username: 胡杨树旁 | Original post link

Can you check if the database’s time and the operating system’s time can match?

| username: TiDBer_yUoxD0vR | Original post link

The time and time zone are the same.

| username: Billmay表妹 | Original post link

Here is a practical example you can check out: 专栏 - sync-diff-inspector 使用实践 | TiDB 社区

| username: tidb菜鸟一只 | Original post link

SHOW VARIABLES LIKE ‘%zone%’;
Also, check the time zone on the machine where sync_diff_inspector is executed.