row level checksum verification failed on timestamp column caused by time zone mistmatch

[3AceShowHand]

What did you do?

1. Deploy TiDB cluster and TiCDC cluster.
2. create a changefeed with row level checksum enabled.
3. run the following SQL

set global tidb_enable_row_level_checksum=true;

set @@global.time_zone = '-05:00'

create table t (a int primary key, b timestamp);

insert into t values (1, '2023-02-09 13:00:00');

What did you expect to see?

The changefeed should verify the checksum correctly, and changefeed works normally.

What did you see instead?

The changefeed failed, and the reported error indicates that checksum mismatch.

Versions of the cluster

Upstream TiDB cluster version (execute SELECT tidb_version(); in a MySQL client):

(paste TiDB cluster version here)

Upstream TiKV version (execute tikv-server --version):

(paste TiKV version here)

TiCDC version (execute cdc version):

master

[3AceShowHand]

The error comes from timestamp handling logic.

1. The TiDB receives the timestamp 2023-02-09 13:00:00, and calculates the checksum based on it, let's assume the resulting checksum is a.
2. The TiDB converts the timestamp 2023-02-09 13:00:00 to UTC, which is 2023-02-09 18:00:00, and stores it into KV.
3. TiCDC receives the raw kv value, and decodes the timestamp value by setting the time_zone as utf-8, which results in 2023-02-10 02:00:00.
4. TiCDC calculates the checksum based on 2023-02-10 02:00:00, resulting in b. since a != b, so the checksum mismatch and error happen.

Solution

1. The TiDB should convert the user input timestamp to UTC, and then calculate the checksum value.
2. The TiCDC should also calculate the checksum based on UTC.

[fubinzh]

/severity major