MySQL-kafka: wrong value for data type timestamp #957

asiroliu · 2022-04-20T05:37:01Z

Description

MySQL-kafka: wrong value for data type timestamp

Steps to reproduce the issue

check src MySQL time_zone

mysql> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| SYSTEM             | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

shell> date
2022年 04月 20日 星期三 13:27:43 CST

insert data on src MySQL

mysql> CREATE DATABASE IF NOT EXISTS `action_db`;
mysql> create table timestamp_columns( id int(11) not null primary key, c_timestamp '
 'timestamp DEFAULT CURRENT_TIMESTAMP)ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> insert into timestamp_columns values (0,'1970-01-01 08:00:01');
mysql> insert into timestamp_columns values (1,'2038-01-19 11:14:07');
mysql> insert into timestamp_columns values (2,'2015-08-12 14:57:05');

create dtle job

{
  "job_id": "data_types_default_full_timestamp_columns",
  "is_password_encrypted": false,
  "task_step_name": "all",
  "failover": true,
  "retry": 2,
  "src_task": {
    "task_name": "src",
    "node_id": "1981dac8-c247-d804-f2b0-2e27d74c40ad",
    "mysql_src_task_config": {
      "gtid": "",
      "binlog_relay": false
    },
    "drop_table_if_exists": true,
    "skip_create_db_table": false,
    "repl_chan_buffer_size": 120,
    "chunk_size": 2000,
    "group_max_size": 1,
    "group_timeout": 100,
    "connection_config": {
      "database_type": "MySQL",
      "host": "172.100.9.1",
      "port": 3306,
      "user": "test_src",
      "password": "test_src"
    },
    "replicate_do_db": [
      {
        "table_schema": "action_db",
        "tables": [
          {
            "table_name": "timestamp_columns"
          }
        ]
      }
    ]
  },
  "dest_task": {
    "task_name": "dest",
    "node_id": "3ffe60b0-8a48-32c1-28d6-473c89792fcf",
    "parallel_workers": 1,
    "kafka_topic": "dtle",
    "kafka_broker_addrs": [
      "172.100.9.21:9092"
    ]
  }
}

get kafka message, and compare with dbz

5. check src MySQL binlog

Describe the results you expected

对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
DTLE发送kafka消息时应直接发送UTC时间，而不应做时区转换，因为DTLE不知道消费端的时区。

Output of `./dtle version`:**

9.9.9.9-master-a269c29

The text was updated successfully, but these errors were encountered:

asiroliu · 2022-04-25T06:21:26Z

测试：

将每个服务器设置为不同的时区：
src dtle

timedatectl set-timezone UTC

dest dest

timedatectl set-timezone Europe/London

src mysql

timedatectl set-timezone Europe/Monaco

dest mysql

timedatectl set-timezone America/Los_Angeles

# 开启目标端general log
set global general_log = ON;
set global general_log_file = '/tmp/general.log';

源端插入数据

create table timestamp_columns( id int(11) not null primary key, c_timestamp timestamp)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into timestamp_columns values (0,'1970-01-01 08:00:01');
insert into timestamp_columns values (1,'2037-01-19 11:14:07');
insert into timestamp_columns values (2,'2015-08-12 14:57:05');
insert into timestamp_columns values (3,now());
insert into timestamp_columns values (4,CURRENT_TIMESTAMP);
insert into timestamp_columns values (5,NULL);

创建dtle job
检查目标端mysql general log

2022-04-25T05:54:08.799929Z        13 Query     START TRANSACTION
2022-04-25T05:54:08.800110Z        13 Query     set @@session.foreign_key_checks = 0
2022-04-25T05:54:08.800526Z        13 Query     replace into `test`.`timestamp_columns` values ('0','1970-01-01 08:00:01'),('1','2037-01-19 11:14:07'),('2','2015-08-12 14:57:05'),('3','2022-04-25 07:53:26'),('4','2022-04-25 07:53:26'),('5','2022-04-25 07:53:27')
2022-04-25T05:54:08.801155Z        13 Query     COMMIT

使用第三方MySQL clinet访问数据

6. 增量插入数据

insert into timestamp_columns values (10,'1970-01-01 08:00:01');
insert into timestamp_columns values (11,'2037-01-19 11:14:07');
insert into timestamp_columns values (12,'2015-08-12 14:57:05');
insert into timestamp_columns values (13,now());
insert into timestamp_columns values (14,CURRENT_TIMESTAMP);
insert into timestamp_columns values (15,NULL);

检查目标端mysql general log

2022-04-25T06:22:58.883775Z        12 Query     START TRANSACTION
2022-04-25T06:22:58.884325Z        12 Prepare   replace into `test`.`timestamp_columns`
(`id`, `c_timestamp`) values
(?, ?)
2022-04-25T06:22:58.884467Z        12 Execute   replace into `test`.`timestamp_columns`
(`id`, `c_timestamp`) values
(10, '1970-01-01 07:00:01')
2022-04-25T06:22:58.884955Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values ('aaa-migration', '^^·¡\'\Ä[^Q\ì¡3^BB¬d  ^A', 14, null)
2022-04-25T06:22:58.885213Z        12 Query     COMMIT
2022-04-25T06:22:58.885758Z        12 Query     START TRANSACTION
2022-04-25T06:22:58.885924Z        12 Execute   replace into `test`.`timestamp_columns`
(`id`, `c_timestamp`) values
(11, '2037-01-19 10:14:07')
2022-04-25T06:22:58.886147Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values ('aaa-migration', '^^·¡\'\Ä[^Q\ì¡3^BB¬d  ^A', 15, null)
2022-04-25T06:22:58.886282Z        12 Query     COMMIT
2022-04-25T06:22:58.886852Z        12 Query     START TRANSACTION
2022-04-25T06:22:58.887017Z        12 Execute   replace into `test`.`timestamp_columns`
(`id`, `c_timestamp`) values
(12, '2015-08-12 12:57:05')
2022-04-25T06:22:58.887236Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values ('aaa-migration', '^^·¡\'\Ä[^Q\ì¡3^BB¬d  ^A', 16, null)
2022-04-25T06:22:58.887372Z        12 Query     COMMIT
2022-04-25T06:22:58.887729Z        12 Query     START TRANSACTION
2022-04-25T06:22:58.887893Z        12 Execute   replace into `test`.`timestamp_columns`
(`id`, `c_timestamp`) values
(13, '2022-04-25 06:22:58')
2022-04-25T06:22:58.888081Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values ('aaa-migration', '^^·¡\'\Ä[^Q\ì¡3^BB¬d  ^A', 17, null)
2022-04-25T06:22:58.888213Z        12 Query     COMMIT
2022-04-25T06:22:58.888601Z        12 Query     START TRANSACTION
2022-04-25T06:22:58.888759Z        12 Execute   replace into `test`.`timestamp_columns`
(`id`, `c_timestamp`) values
(14, '2022-04-25 06:22:58')
2022-04-25T06:22:58.888967Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values ('aaa-migration', '^^·¡\'\Ä[^Q\ì¡3^BB¬d  ^A', 18, null)
2022-04-25T06:22:58.889096Z        12 Query     COMMIT
2022-04-25T06:22:59.572726Z        12 Query     START TRANSACTION
2022-04-25T06:22:59.573120Z        12 Execute   replace into `test`.`timestamp_columns`
(`id`, `c_timestamp`) values
(15, '2022-04-25 06:22:59')
2022-04-25T06:22:59.573557Z        12 Execute   replace into dtle.gtid_executed_v4 (job_name,source_uuid,gtid,gtid_set) values ('aaa-migration', '^^·¡\'\Ä[^Q\ì¡3^BB¬d  ^A', 19, null)
2022-04-25T06:22:59.573821Z        12 Query     COMMIT

使用第三方MySQL clinet访问数据

LordofAvernus · 2022-04-25T07:31:32Z

MySQL-MySQL ：

将源端用固定的time_zone 读取，目标端也用同一个time_zone写入

MySQL-Kafka

将源端用固定的utc -0 的time_zone 读取

默认使用utc时区读写，考虑增加一个配置来决定读写时区

asiroliu · 2022-04-26T05:45:13Z

读取TIMESTAMP使用默认使用MySQL server的时区
https://dev.mysql.com/doc/refman/5.7/en/datetime.html

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis.

ghost · 2022-11-22T09:35:21Z

MySQL 行为

$ timedatectl | grep "Time zone"
                       Time zone: Etc/UTC (UTC, +0000)

mysql> create table a.a957 (id int primary key auto_increment, val1 timestamp, val2 datetime);

mysql> select @@time_zone;
| SYSTEM      |

mysql> insert into a.a957 values (0, now(), now());

mysql> set time_zone = '+08:00';

mysql> insert into a.a957 values (0, now(), now());

mysql> select * from a.a957;
|  1 | 2022-11-22 17:17:47 | 2022-11-22 09:17:47 |
|  2 | 2022-11-22 17:18:51 | 2022-11-22 17:18:51 |

mysql> set time_zone = 'system';

mysql> select * from a.a957;
|  1 | 2022-11-22 09:17:47 | 2022-11-22 09:17:47 |
|  2 | 2022-11-22 09:18:51 | 2022-11-22 17:18:51 |

asiroliu · 2022-11-24T09:40:16Z

fixed
version:

9.9.9.9-master-7a98826

asiroliu added the kafka bugs about kafka label Apr 21, 2022

ghost added this to the 4.22.12.0 milestone Nov 22, 2022

ghost pushed a commit that referenced this issue Nov 23, 2022

use UTC timestamp #957

e409705

ghost pushed a commit that referenced this issue Nov 23, 2022

use UTC timestamp #957

0c0c5e0

asiroliu closed this as completed Nov 24, 2022

ghost pushed a commit that referenced this issue Nov 28, 2022

use UTC timestamp #957

4ede1ed

asiroliu added the VERIFIED verified issue label Apr 11, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

MySQL-kafka: wrong value for data type timestamp #957

MySQL-kafka: wrong value for data type timestamp #957

asiroliu commented Apr 20, 2022

asiroliu commented Apr 25, 2022 •

edited

Loading

LordofAvernus commented Apr 25, 2022 •

edited

Loading

asiroliu commented Apr 26, 2022 •

edited by ghost

Loading

ghost commented Nov 22, 2022 •

edited by ghost

Loading

asiroliu commented Nov 24, 2022

MySQL-kafka: wrong value for data type timestamp #957

MySQL-kafka: wrong value for data type timestamp #957

Comments

asiroliu commented Apr 20, 2022

Description

Steps to reproduce the issue

Describe the results you expected

Output of ./dtle version:**

asiroliu commented Apr 25, 2022 • edited Loading

LordofAvernus commented Apr 25, 2022 • edited Loading

MySQL-MySQL ：

MySQL-Kafka

asiroliu commented Apr 26, 2022 • edited by ghost Loading

ghost commented Nov 22, 2022 • edited by ghost Loading

asiroliu commented Nov 24, 2022

Output of `./dtle version`:**

asiroliu commented Apr 25, 2022 •

edited

Loading

LordofAvernus commented Apr 25, 2022 •

edited

Loading

asiroliu commented Apr 26, 2022 •

edited by ghost

Loading

ghost commented Nov 22, 2022 •

edited by ghost

Loading