If the table contains a timestamp column, it will cause gh-ost to incorrect timestamp value (add 8 hours)

[gangade]

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:

1. Start the gh-ost to alter online table t1 (Include timestamp column).
2. Insert the current date and time into table t1.
3. 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.
4. 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.
5. We parse the binlog to confirm that the timestamp inserted in step 2 is correct.
6. 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.

[dm-2]

gh-ost: 1.0.49

:wave: @gangade you're running a really old version of gh-ost, please can you try with the latest release?

[gangade]

gh-ost: 1.0.49

👋 @gangade you're running a really old version of gh-ost, please can you try with the latest release?

Thanks for the reply! this problem can still be reproduced by upgrading gh-ost to version 1.1.5.

[dm-2]

gh-ost: 1.0.49

👋 @gangade you're running a really old version of gh-ost, please can you try with the latest release?

Thanks for the reply! this problem can still be reproduced by upgrading gh-ost to version 1.1.5.

That's unexpected - we have this problem in master but it shouldn't affect 1.1.5.

Would you mind sharing a test case with full gh-ost log output for 1.1.5 so I can dig into this further? Thanks!

[DBAAJ]

Are there any other students here who have this problem?

[timvaillancourt]

@gangade this may be fixed by PR https://github.com/github/gh-ost/pull/1322 in release 1.1.6. Can you test and confirm?