sync-diff-inspector : When the range condition is of timestamp type, the time zone cannot be converted correctly

[snowballbear]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   上游：

mysql> show variables like '%zone%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| system_time_zone | Asia/Shanghai |
| time_zone        | SYSTEM        |
+------------------+---------------+
2 rows in set (0.00 sec)
mysql> show create table test1;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1 | CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from test1;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 2023-03-29 00:00:00 |
|    2 | 2023-03-20 00:00:00 |
|    3 | 2023-03-20 20:30:00 |
|    4 | 2023-03-20 22:30:00 |
+------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from test1 where create_time<='2023-03-20 20:30:00';
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    2 | 2023-03-20 00:00:00 |
|    3 | 2023-03-20 20:30:00 |
+------+---------------------+
2 rows in set (0.00 sec)

下游

mysql> show variables like '%zone%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| system_time_zone | Asia/Shanghai |
| time_zone        | SYSTEM        |
+------------------+---------------+
2 rows in set (0.00 sec)
mysql> show create table test1_d;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                  |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1_d | CREATE TABLE `test1_d` (
  `id` int(11) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test1_d;
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    1 | 2023-03-29 00:00:00 |
|    2 | 2023-03-20 00:00:00 |
|    3 | 2023-03-20 20:30:00 |
|    4 | 2023-03-22 20:30:00 |
+------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from test1_d where create_time<='2023-03-20 20:30:00';
+------+---------------------+
| id   | create_time         |
+------+---------------------+
|    2 | 2023-03-20 00:00:00 |
|    3 | 2023-03-20 20:30:00 |
+------+---------------------+
2 rows in set (0.00 sec)

config 文件

[tidb@172-16-120-238 hwtest]$ more syncdiff_config_test.toml
# Diff Configuration.

######################### Global config #########################

# 检查数据的线程数量，上下游数据库的连接数会略大于该值
check-thread-count = 4

# 如果开启，若表存在不一致，则输出用于修复的 SQL 语句。
export-fix-sql = true

# 只对比表结构而不对比数据
check-struct-only = false


######################### Datasource config #########################
[data-sources]
[data-sources.hwtest] # mysql1 是该数据库实例唯一标识的自定义 id，用于下面 task.source-instances/t
ask.target-instance 中
    host = "10.2.8.3"
    port = 24000
    user = "root"
    password = "" # 设置连接上游数据库的密码，可为明文或 Base64 编码。

    #（可选）使用映射规则来匹配上游多个分表，其中 rule1 和 rule2 在下面 Routes 配置栏中定义
    route-rules = ["rule1"]

[data-sources.hwtest_d]
    host = "10.2.8.3"
    port = 24000
    user = "root"
    password = "" # 设置连接下游数据库的密码，可为明文或 Base64 编码。



########################### Routes ###########################
# 如果需要对比大量的不同库名或者表名的表的数据，或者用于校验上游多个分表与下游总表的数据，可以通过
 table-rule 来设置映射关系
# 可以只配置 schema 或者 table 的映射关系，也可以都配置
[routes]
[routes.rule1] # rule1 是该配置的唯一标识的自定义 id，用于上面 data-sources.route-rules 中
schema-pattern = "test"      # 匹配数据源的库名，支持通配符 "*" 和 "?"
table-pattern = "test1"          # 匹配数据源的表名，支持通配符 "*" 和 "?"
target-schema = "test"         # 目标库名
target-table = "test1_d" # 目标表名


######################### Task config #########################
# 配置需要对比的*目标数据库*中的表
[task]
    # output-dir 会保存如下信息
    # 1 sql: 检查出错误后生成的修复 SQL 文件，并且一个 chunk 对应一个文件
    # 2 log: sync-diff.log 保存日志信息
    # 3 summary: summary.txt 保存总结
    # 4 checkpoint: a dir 保存断点续传信息
    output-dir = "./output"

    # 上游数据库，内容是 data-sources 声明的唯一标识 id
    source-instances = ["hwtest"]

    # 下游数据库，内容是 data-sources 声明的唯一标识 id
    target-instance = "hwtest_d"

    # 需要比对的下游数据库的表，每个表需要包含数据库名和表名，两者由 `.` 隔开
    # 使用 ? 来匹配任意一个字符；使用 * 来匹配任意；详细匹配规则参考 golang regexp pkg: https://gi
thub.com/google/re2/wiki/Syntax
    target-check-tables = ["test.test1_d"]

    #（可选）对部分表的额外配置，其中 config1 在下面 Table config 配置栏中定义
    target-configs = ["config1"]

######################### Table config #########################
[table-configs.config1]
target-tables = ["test.test1_d"]
#（可选）指定检查的数据的范围，需要符合 sql 中 where 条件的语法
range = "create_time<='2023-03-20 20:30:00'"

2. What did you expect to see?
   对比范围内数据一致，检查通过，不生成修复 SQL 文件

3. What did you see instead?
   生成有不在对比范围内的修复 SQL

[tidb@172-16-120-238 hwtest]$ more output/fix-on-hwtest_d/test\:test1_d\:0\:0-0\:0.sql
-- table: test.test1_d
-- range in sequence: Full
set @@session.time_zone = "+0:00";
REPLACE INTO `test`.`test1_d`(`id`,`create_time`) VALUES (4,'2023-03-20 14:30:00');

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   v6.5.1

5. which tool are you using?
   sync-diff-inspector

6. what versionof tool are you using (pump -V or tidb-lightning -V or syncer -V)?
   v6.5.1

[BornChanger]

@mayjiang0203 it's a enhancement instead of a bug.