Binlog replication uses timestamp values from the machine-local timezone as oppose to UTC

[ymakedaq]

I Test Copying , Found This Problem ,
Table Cloum Type Is:

| sf_modify_time | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

I'll try to reproduce this and record it here.

[shuhaowu]

What's the source and target server's timezones?

[pushrax]

This is a known bug when not using UTC, see this test. I have a patch that requires upstream changes in go-mysql.

From 0f34553aabe1183670c892cb27f2b113e2c26bee Mon Sep 17 00:00:00 2001
From: Justin Li <git@justinli.net>
Date: Wed, 6 Dec 2017 16:04:58 -0500
Subject: [PATCH] Fix replication of rows with a nonnull TIMESTAMP column on
 non-UTC machines

---
 binlog_streamer.go                                        | 4 ++++
 test/types_integration_test.go                            | 6 +++---
 vendor/github.com/siddontang/go-mysql/replication/time.go | 9 +++++++--
 3 files changed, 14 insertions(+), 5 deletions(-)

diff --git a/binlog_streamer.go b/binlog_streamer.go
index 9a26483..0951909 100644
--- a/binlog_streamer.go
+++ b/binlog_streamer.go
@@ -353,3 +353,7 @@ func idsOnServer(db *sql.DB) ([]uint32, error) {
 
 	return server_ids, nil
 }
+
+func init() {
+	replication.TimeStringLocation = time.UTC
+}
diff --git a/test/types_integration_test.go b/test/types_integration_test.go
index c3699d0..fc2d91c 100644
--- a/test/types_integration_test.go
+++ b/test/types_integration_test.go
@@ -20,7 +20,7 @@ func addTypesToTable(db *sql.DB, dbName, tableName string) {
 		"ADD date_col DATE," +
 		"ADD time_col TIME," +
 		"ADD dt_col DATETIME," +
-		"ADD ts_col TIMESTAMP," + // TODO broken on master
+		"ADD ts_col TIMESTAMP," +
 		"ADD varchar_col VARCHAR(128)," +
 		"ADD enum_col ENUM('foo', 'bar')," +
 		"ADD set_col SET('foo', 'bar', 'baz')," +
@@ -46,8 +46,8 @@ func setupMultiTypeTable(f *testhelpers.TestFerry) {
 
 	for i := 0; i < 100; i++ {
 		query := "INSERT INTO gftest.table1 " +
-			"(id, data, tiny_col, float_col, double_col, decimal_col, year_col, date_col, time_col, dt_col, varchar_col, enum_col, set_col, utfmb4_col, blob_col)" +
-			"VALUES (NULL, ?, ?, 3.14, 2.72, 42.42, NOW(), NOW(), NOW(), NOW(), ?, ?, 'foo,baz', ?, ?)"
+			"(id, data, tiny_col, float_col, double_col, decimal_col, year_col, date_col, time_col, dt_col, ts_col, varchar_col, enum_col, set_col, utfmb4_col, blob_col)" +
+			"VALUES (NULL, ?, ?, 3.14, 2.72, 42.42, NOW(), NOW(), NOW(), NOW(), NOW(), ?, ?, 'foo,baz', ?, ?)"
 
 		enumVal := "foo"
 		if i%2 == 0 {
diff --git a/vendor/github.com/siddontang/go-mysql/replication/time.go b/vendor/github.com/siddontang/go-mysql/replication/time.go
index 8869106..9d495be 100644
--- a/vendor/github.com/siddontang/go-mysql/replication/time.go
+++ b/vendor/github.com/siddontang/go-mysql/replication/time.go
@@ -7,7 +7,8 @@ import (
 )
 
 var (
-	fracTimeFormat []string
+	TimeStringLocation *time.Location
+	fracTimeFormat     []string
 )
 
 // fracTime is a help structure wrapping Golang Time.
@@ -19,7 +20,11 @@ type fracTime struct {
 }
 
 func (t fracTime) String() string {
-	return t.Format(fracTimeFormat[t.Dec])
+	tt := t.Time
+	if TimeStringLocation != nil {
+		tt = tt.In(TimeStringLocation)
+	}
+	return tt.Format(fracTimeFormat[t.Dec])
 }
 
 func formatZeroTime(frac int, dec int) string {

[shuhaowu]

This is tracked upstream at go-mysql-org/go-mysql#63 and I pushed the commit above as a PR to upstream at go-mysql-org/go-mysql#295. Once that's merged, I'll update this repository with that fix, pending the merge of #45.

[shuhaowu]

The fix for this has not been merged yet.