MySQL Bugs: #72308: Bad replication of manually inserted TIMESTAMP between different timezones

Bug #72308	Bad replication of manually inserted TIMESTAMP between different timezones
Submitted:	10 Apr 2014 15:56	Modified:	23 Jun 2014 16:59
Reporter:	Giancarlo Nebiolo	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6.11	OS:	Linux
Assigned to:	David Moss	CPU Architecture:	Any
Tags:	mixed, replication, timestamp, timezones

Description:
When inserting manually a TIMESTAMP value in a table with TIMESTAMP columns using binlog format MIXED, the insertion is replicated using statement format. But if the slave has a different time zone this introduce an inconsistency, because the TIMESTAMP value contained in the replicated insert statement is transformed considering the slave time zone, in consequence the TIMESTAMP value inserted in the slave's table differs from the TIMESTAMP value in the master's table.

How to repeat:
To reproduce the problem, both the master and the slave must have different system time zones (in my scenario the time zone of the master is set to "Caracas, La Paz" from the country Venezuela and in the slave the time zone is set to "Bogota" from the country Colombia):
 
--Run these commands on the master Server:

show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | VET    |   
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

CREATE TABLE time_test (time_column timestamp NOT NULL, description varchar(100));
Query OK, 0 rows affected (0.27 sec)

SET SESSION binlog_format=MIXED;
Query OK, 0 rows affected (0.00 sec)

INSERT INTO time_test VALUES (NOW(),'Timestamp using NOW()and MIXED format');
Query OK, 1 row affected (0.00 sec)

INSERT INTO time_test VALUES ('2014-04-10 09:33:04','Manually inserted Timestamp using MIXED format');
Query OK, 1 row affected (0.00 sec)

SET SESSION binlog_format=ROW;
Query OK, 0 rows affected (0.00 sec)

INSERT INTO time_test VALUES (NOW(),'Timestamp using NOW() and ROW format');
Query OK, 1 row affected (0.00 sec)

INSERT INTO time_test VALUES ('2014-04-10 09:33:04','Manually inserted Timestamp using ROW format');
Query OK, 1 row affected (0.00 sec)

SET SESSION time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM time_test;
+---------------------+------------------------------------------------+
| time_column         | description                                    |
+---------------------+------------------------------------------------+
| 2014-04-10 15:44:49 | Timestamp using NOW()and MIXED format          |
| 2014-04-10 14:03:04 | Manually inserted Timestamp using MIXED format |
| 2014-04-10 15:44:49 | Timestamp using NOW() and ROW format           |
| 2014-04-10 14:03:04 | Manually inserted Timestamp using ROW format   |
+---------------------+------------------------------------------------+
4 rows in set (0.00 sec)

--Run these statements on the slave server:

show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | COT    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

SET SESSION time_zone='UTC';
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM time_test;
+---------------------+------------------------------------------------+
| time_column         | description                                    |
+---------------------+------------------------------------------------+
| 2014-04-10 15:44:49 | Timestamp using NOW()and MIXED format          |
| 2014-04-10 14:33:04 | Manually inserted Timestamp using MIXED format |
| 2014-04-10 15:44:49 | Timestamp using NOW() and ROW format           |
| 2014-04-10 14:03:04 | Manually inserted Timestamp using ROW format   |
+---------------------+------------------------------------------------+
4 rows in set (0.00 sec)

 In the master and slave server the second row differs. 

Suggested fix:
Replicate manually inserted timestamp values using ROW binlog format.

Adjusting the tags

When using MySQL timezone SYSTEM the master and slave assume that they are in the same timezone ... you need to explicitly set the mysql time_zone when replicating between servers in different time zones ...

Thanks for the tip Hartmut, I changed the value of the variable 'time_zone' of each server to the same value of the 'system_time_zone' variable and it worked. But I find troublesome that fact, if both servers have different values of the 'system_time_zone' both servers should detect that the value 'SYSTEM' in the 'time_zone' variable is not the same between them or at least that behavior must be documented in the MySQL reference manual to avoid that an inexperienced user fall in these types of problems. I had read the manual and didn't see anything nor in the section concerning the issues with replication and the time zones (http://dev.mysql.com/doc/refman/5.6/en/replication-features-timezone.html) nor  in the "MySQL Server Time Zone Support" section (https://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html), If you find that I am wrong please correct me.

Hmm ... I thought it was documented in the "Replication and Time Zones" section, but it indeed still isn't ...

So maybe the bug category should be changed to "Documentation" to reflect this ...

Done, I changed the category from 'MySQL Server: Binlog(replication)' to 'MySQL Server: Documentation'

Thank you for the feedback.

Verified as described. While the page says that "The same system time zone should be set for both master and slave." it does not mention that time zone SYSTEM does not mean the same time zone for both servers.

Thanks for your feedback all. I have updated the time zone content to include your changes. Commit number 39127.

The updated content is available here:
http://dev.mysql.com/doc/refman/5.6/en/replication-features-functions.html
http://dev.mysql.com/doc/refman/5.6/en/replication-features-timezone.html

and also in the 5.7 refman.