MySQL Bugs: #71016: Timestamp replication across timezones results in different times

Bug #71016	Timestamp replication across timezones results in different times
Submitted:	26 Nov 2013 17:42	Modified:	30 Jan 2014 17:09
Reporter:	Paul Molodowitch	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.6	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	server replication timestamp timezone

Description:
If replication is set up across servers in different SYSTEM timezones, and a timestamp field is set to an EXPLICIT value, then the timestamp is replicated as being that time, interpreted in the slave server's local time.

Ie, say my master is on a server whose system time is UTC+1, and my slave is on a server whose system time is UTC+3. I then issue a command which sets a timestamp field to the EXPLICIT VALUE '1999-08-14 20:04:05', then it will get in the master as '1999-08-14 20:04:05' in UTC+1, or '1999-08-14 19:04:05' in UTC. [GOOD!] However, when it replicates, it will be set on the slave as '1999-08-14 20:04:05'... in the slave's UTC+3 zone... or '1999-08-14 17:04:05' in UTC [BAD!]

The field has been replicated as a different value / absolute time. Note that this will NOT happen if the timestamp is set using 'ON UPDATE CURRENT_TIMESTAMP' or 'DEFAULT CURRENT_TIMESTAMP'.

How to repeat:
1) Find two servers, and set them to have different SYSTEM/OS timezones. Important! The bug will not be reproduced if the servers are in the same system timezone, but with different default-time-zone settings. This means the servers must be located on two physically separate hosts or different VMs. (Though I haven't tested VMs...)

2) Set up master / slave statement-based replication.

3) Add a table that has a field of type TIMESTAMP.

4) On the master, set that timestamp field in some row to an EXPLICIT value - ie:

UPDATE `my_table` SET `timestamp_field`='1999-08-14 20:04:05' WHERE `id_my_table`=1;

5) Check the resulting value in the master and the server - they will DISPLAY as the exact same time - meaning they are stored as the same time IN LOCAL TIME, but are different absolute/UTC times.

6) (Optional) For comparison, make sure that the timestamp field has either 'ON UPDATE CURRENT_TIMESTAMP' or 'DEFAULT CURRENT_TIMESTAMP' set. Then, on a different row in the table, update the field using one of these. If you compare the values for that row, they will DISPLAY as different, but the underlying absolute / UTC time will be the same.

Suggested fix:
Looking into the bin logs with mysqlbinlog, it seems as though the timestamps are stored without any timezone information. Either these need to be stored in UTC time (not local time), or else timezone needs to included with them...

I'm attaching small zipped master / slave databases which can be used to demonstrate the problem.  Note that though these example databases were set up with MySQL 5.5 on windows, the problem was originally noticed/replicated on MySQL 5.6.

Zipped master database server, to demonstrate the problem (was originally hosted on a system with UTC-8)

Attachment: MySQL_5.5_timestampTimezoneBug_masterServer.zip (application/zip, text), 668.40 KiB.

Zipped slave database server, to demonstrate the problem (was originally hosted on a system with UTC-7)

Attachment: MySQL_5.5_timestampTimezoneBug_slaveServer.zip (application/zip, text), 592.94 KiB.

You need to set mysqld timezones explicitly, not use SYSTEM, in this case ...

Not sure if this is documented properly, but if it isn't this is a replication bug at best IMHO

Confirmed that it works if both servers have explicit default-time-zone set... thanks!  This provides a solid workaround, and so lowers the severity for us.  (Btw - the link to show the severity definitions is broken, at least on chrome...)

However, while documentation is better than nothing, I still think this should be considered a bug and fixed, not simply documented. Consider what the consequences are (ie, data corruption and divergence), and whether you think that this would ever be the desired behavior (particularly considering that some of the timestamps - ie, the on-update/default ones - would be following one set of rules, and the rest another set).

Definitely agreed, however, that this is a replication bug (I tagged the category as such).

Thank you for the report.

Actually warning about timestamps exist in the user manual at http://dev.mysql.com/doc/refman/5.6/en/replication-features-timezone.html

So technically this is not even documentation bug.

But at the same time http://dev.mysql.com/doc/refman/5.6/en/replication-features-timezone.html is not up to date, because now we have information about timestamps in binary logs and in case if explicit timezone was set, it is safe to use different timestamps on master and slave.

The result that the submitter says that he's obtaining in Step 5--the same column showing the same literal value on both master and slave--is exactly the opposite of what we'd expect. If this happens consistently, it's a bug.

Setting Category/Status to Server:Replication/Open. Removing myself as assignee.

OK, Jon, lets it be verified server bug.

Note, this behavior can be observed only if default-timezone set to SYSTEM.