Description:
mysqlpump with --skip-tz-utc , TIMESTAMP columns should be dumped in the time zones local to the source server , but it dumped in UTC +0 time zone。
How to repeat:
mysql> show variables like 'version';
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| version | 5.7.36-39-log |
+---------------+---------------+
mysql> desc test.test_tb;
+--------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+-------------------+----------------+
| increment_id | int(11) | NO | PRI | NULL | auto_increment |
| stu_id | int(11) | NO | | NULL | |
| stu_name | varchar(20) | YES | | NULL | |
| dt_time | datetime | NO | | NULL | |
| create_time | timestamp | NO | | CURRENT_TIMESTAMP | |
+--------------+-------------+------+-----+-------------------+----------------+
mysql> select * from test.test_tb;
+--------------+--------+----------+---------------------+---------------------+
| increment_id | stu_id | stu_name | dt_time | create_time |
+--------------+--------+----------+---------------------+---------------------+
| 1 | 1001 | fgds | 2020-07-10 09:43:28 | 2020-07-10 09:43:28 |
| 2 | 1002 | fgsw | 2020-10-10 09:43:28 | 2020-10-10 09:43:28 |
| 3 | 1003 | vffg | 2020-10-10 02:00:00 | 2020-10-10 02:00:00 |
| 4 | 1004 | wdsd | 2020-10-31 23:43:28 | 2020-10-31 23:43:28 |
| 5 | 1005 | grdb | 2020-11-01 00:00:00 | 2020-11-01 00:00:00 |
| 6 | 1006 | sdfv | 2020-11-01 02:00:00 | 2020-11-01 02:00:00 |
| 7 | 1007 | fgfg | 2020-11-06 02:00:00 | 2020-11-06 02:00:00 |
| 8 | 1008 | tyth | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
| 9 | 1009 | ewer | 2020-11-10 09:43:28 | 2020-11-10 09:43:28 |
| 10 | 1010 | erre | 2020-11-11 15:17:03 | 2020-11-11 15:17:03 |
+--------------+--------+----------+---------------------+---------------------+
> mysqlpump -uroot -p -B test --skip-tz-utc --extended-insert=1 > skip-tz-utc_test_tb.sql
> cat skip-tz-utc_test_tb.sql
-- Dump created by MySQL pump utility, version: 5.7.36-39, Linux (x86_64)
-- Dump start time: Wed Jan 19 23:22:59 2022
-- Server version: 5.7.36
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE `test`.`test_tb` (
`increment_id` int(11) NOT NULL AUTO_INCREMENT,
`stu_id` int(11) NOT NULL,
`stu_name` varchar(20) DEFAULT NULL,
`dt_time` datetime NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`increment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
;
INSERT INTO `test`.`test_tb` VALUES (1,1001,"fgds","2020-07-10 09:43:28","2020-07-10 13:43:28");
INSERT INTO `test`.`test_tb` VALUES (2,1002,"fgsw","2020-10-10 09:43:28","2020-10-10 13:43:28");
INSERT INTO `test`.`test_tb` VALUES (3,1003,"vffg","2020-10-10 02:00:00","2020-10-10 06:00:00");
INSERT INTO `test`.`test_tb` VALUES (4,1004,"wdsd","2020-10-31 23:43:28","2020-11-01 03:43:28");
INSERT INTO `test`.`test_tb` VALUES (5,1005,"grdb","2020-11-01 00:00:00","2020-11-01 04:00:00");
INSERT INTO `test`.`test_tb` VALUES (6,1006,"sdfv","2020-11-01 02:00:00","2020-11-01 06:00:00");
INSERT INTO `test`.`test_tb` VALUES (7,1007,"fgfg","2020-11-06 02:00:00","2020-11-06 06:00:00");
INSERT INTO `test`.`test_tb` VALUES (8,1008,"tyth","2020-11-10 09:43:28","2020-11-10 13:43:28");
INSERT INTO `test`.`test_tb` VALUES (9,1009,"ewer","2020-11-10 09:43:28","2020-11-10 13:43:28");
INSERT INTO `test`.`test_tb` VALUES (10,1010,"erre","2020-11-11 15:17:03","2020-11-11 19:17:03");
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Wed Jan 19 23:22:59 2022
Suggested fix:
TIMESTAMP columns should be dumped in the time zones local to the source server
