Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: dm同步TIMESTAMP类型的字段时比原表少12个小时
[TiDB Usage Environment] Test Environment
[TiDB Version] 6.1.0
[DM Version] 5.4.0
[Encountered Problem]
The create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP field, when synchronized to TiDB through DM, is found to be 12 hours less than the original table’s time. By extracting the TIMESTAMP field value from the binlog and parsing it directly using the FROM_UNIXTIME() function, the time is consistent and does not differ by 12 hours.
Time Zone: 
Has anyone encountered the same issue?
Hello, can you provide the time_zone configuration for upstream and downstream?
Also, what is the binlog configuration like? Is it in row format?
Did the issue occur during the full data phase or the incremental phase? If it’s during the incremental phase, can you use mysqlbinlog to parse a small segment to see what the actual synchronization to the downstream looks like?
The upstream MySQL time_zone: 
The downstream TiDB time_zone: 
The binlog is in row format.
The issue occurs during the incremental phase, while the full phase is normal.
Binlog:
This is the test data, and the fields within the box are problematic. If you parse this value directly in MySQL and TiDB, the time is consistent.
I don’t know why TiDB’s system_time_zone is America/New_York,
the result of the timedatectl command:
TiDB data:
MySQL data:
The create_time and update_time differ by 12 hours.
I’ll try to see if I can reproduce it on my end. Normally, if the time_zone is not set to system, there shouldn’t be any issues, and it won’t use system_time_zone. The system_time_zone is the time zone set on your server.
Additionally, could you please check if there is any set time zone or other similar information above this binlog data?
Binlog has a timezone setting, but the value is the same as the SQL statement!
Check the time zone of the DM server, then parse the timestamp you found in the binlog on the DM server to see what time it is. I suspect that the time difference of 12 hours is caused by the time on your DM server.
The time zone for DM has also been checked and is normal. The issue was resolved by manually specifying the time zone during DM configuration.
Manually specifying the time zone during DM configuration resolved this issue. The DM version is 5.4.0, and the production environment’s DM version is 6.1.0, where this issue was not observed.
It seems to be a time zone issue, but the upstream and downstream time zones are normal, so it shouldn’t be like this. I’ll also test it in my environment; it might be a bug.
Check if the operating system time zone of the production environment is the same as this one.
If the binlog log format is Statement, you will encounter this issue.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: tuyi锅子
|
username: 
|
username: 
|
username: 
|
username: