If the table contains a timestamp column, it will cause gh-ost to incorrect timestamp value (add 8 hours) #1176
Description
We encountered a case where gh-ost caused the timestamp column to have an incorrect value. During the DDL execution on the table in mysql, the date value we inserted into the timestamp column is wrong, which is 8 hours more than the real value.
Env
gh-ost: 1.0.49
MySQL: Aliyun RDS 5.7.32
| Variable_name | Value |
|---|---|
| system_time_zone | CST |
| time_zone | +08:00 |
Case
The details of this case is as follows:
- Start the gh-ost to alter online table t1 (Include timestamp column).
- Insert the current date and time into table t1.
- During the execution of gh-ost, check the _t1_gho table and find that the newly inserted timestamp value in step 2 has increased by eight hours.
- when the gh-ost is finish, Check the t1 table and confirm that the newly inserted timestamp value in step 2 has increased by eight hours.
- We parse the binlog to confirm that the timestamp inserted in step 2 is correct.
- This problem can still be reproduced by upgrading gh-ost to version 1.1.5.
- Table Defination
CREATE DATABASE `d1` /*!40100 DEFAULT CHARACTER SET utf8 */;
use d1;
CREATE TABLE `t1` (
`id` bigint(16) NOT NULL AUTO_INCREMENT,
`create_time` timestamp NULL DEFAULT NULL,
`update_time` timestamp NULL DEFAULT NULL,
`report_time` timestamp NULL DEFAULT NULL,
`ctime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`dt` datetime NOT NULL
PRIMARY KEY (`id`),
KEY `idx_createTime` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- Gh-ost command
gh-ost --user="" --password='' --host=xxxxx.mysql.rds.aliyuncs.com --port=3306 --database="d1" --table="t1" --alter="add c1 bigint not null" --verbose --execute --allow-on-master --assume-rbr --assume-master-host=xxxxx.mysql.rds.aliyuncs.com:3306 -aliyun-rds=true
- insert sql
insert into d1.t1(create_time,update_time,report_time,dt) values('2022-09-05 18:33:00','2022-09-05 18:33:00','2022-09-05 18:33:00','2022-09-05 18:33:00');`
- select sql
select id,create_time,update_time,report_time,dt from d1.t1 order by id desc limit 5;
+---------+---------------------+---------------------+---------------------+---------------------+
| id | create_time | update_time | report_time | dt |
+---------+---------------------+---------------------+---------------------+---------------------+
| 6580690 | 2022-09-05 18:33:00 | 2022-09-05 18:33:00 | 2022-09-05 18:33:00 | 2022-09-05 10:33:00 |
| 6580689 | 2022-09-05 18:33:00 | 2022-09-05 18:33:00 | 2022-09-05 18:33:00 | 2022-09-05 10:33:00 |
| 6580688 | 2022-09-05 18:33:00 | 2022-09-05 18:33:00 | 2022-09-05 18:33:00 | 2022-09-05 10:33:00 |
| 6580687 | 2022-09-05 18:33:00 | 2022-09-05 18:33:00 | 2022-09-05 18:33:00 | 2022-09-05 10:33:00 |
| 6580686 | 2022-09-05 18:33:00 | 2022-09-05 18:33:00 | 2022-09-05 18:33:00 | 2022-09-05 10:33:00 |
+---------+---------------------+---------------------+---------------------+---------------------+
It can be seen that during the execution of gh-ost, the real value inserted into the timestamp column is 2022-09-05 10:33:00, but after the execution of gh-ost, the value increases by 8 hours and becomes 2022-09-05 18: 33:00.