MySQL Bugs: #3262: Replication Fails using cal

Bug #3262	Replication Fails using cal
Submitted:	22 Mar 2004 12:27	Modified:	23 Mar 2004 4:11
Reporter:	Jim Taylor	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	4.0318	OS:	FreeBSD (FreeBSD)
Assigned to:		CPU Architecture:	Any

Description:
Master server is in Eastern time zone, Slave server is in Central time zone (one hour behind Master).  INSERT using Now() function puts different date/time in Slave record than in Master record.  Because records do not match, subsequent UPDATE commands on master do not execute on slave (if date is used as criterion).

MySQL documentation says, "When the slave SQL thread executes an event read from the master, it modifies its own time to the event's timestamp."  That doesn't seem to be true--at least as regards the "Now()" function.

How to repeat:
Set up master & slave with different system times.  

Execute "INSERT INTO OrderHeader (OrderNumber, OrderDate) VALUES (123, Now())" 

This creates a record for order 123 on Master with an OrderDate of 2004-03-15 5:30:00 -- but on Slave, order 123 is created with an OrderDate of 2004-03-15 4:30:00.  

Because of this one-hour time difference, future updates then are applied inconsistentaly.  For example, "UPDATE OrderHeader set Order Status = 'Closed' ... where OrderDate="2004-03-15 5:30:00" will update order 123 on the master, but not on the slave (since order 123 has a different date on the slave).

Suggested fix:
Have Master replace function calls like "Now()" with actual values in the SQL passed to slave

I guess OrderDate of type DATETIME. NOW() returns a timestamp value which is independent from the timezone (it's the number of seconds since a fix date and time in 1970). When it is inserted into a DATETIME column, this number of seconds is converted into a date and time in the server's timezone. The same timestamp is propagated to the slave, but as the slave has a different timezone, you get two different values on master and slave in the DATETIME column. Both values represent the same date and time, but expressed in different timezones, that's why they are visually different.
That's why the slave, as it simply replays the same query than the one issued on the master, does not find a matching row.
If your OrderDate column was of type TIMESTAMP, you would see the same values  on master and slave and replication would work.
You should make OrderDate a column of type TIMESTAMP and change your queries accordingly.
In a future MySQL version (in the 5.0 branch) we will propagate timezone information between the master and slave so your original table structure and queries would replication fine then.

Thank you for the quick response.  I don't think making OrderDate a TIMESTAMP is a solution, though.  OrderDate needs to be a fixed value (the time the order was placed).  If it were a TIMESTAMP, it would change each time the record was updated and we would lose the fact of when the order was originally placed. 

And your proposed long-term solution (propagating time zone information) also doesn't sound like it would fix the problem.  Even if the Master and Slave were in the same time zone, unless their clocks were absolutely synchronized, Now() would produce different values on the two systems.

It seems like the real problem is that Now() (and potentially other functions as well?) are executed independently on the Master and the Slave--and may therfore produce different results.  Wouldn't it make more sense to have the Replication process send the values to the slave rather than repeating the function calls?

Hi, Jim!

Feature that will allow disable this auto-set behaviour for TIMESTAMP type is also in our short-term todo...

Actually short-term solution suggested by Guilhem won't work because altough internal representation of values stored in TIMESTAMP column in this scenario will be the same on the master and on the slave (and they will represent the same moment of time), those value still will have different external representation which will be aligned to master/slave time zone.

I think that the short-term solution for you is to set the same time zone for MySQL slave (only for MySQL server not for the whole system) as it set for master. You can do this using --timezone option of mysqld_safe script (see http://www.mysql.com/doc/en/mysqld_safe.html for more info).

By the way since we already passing current datetime for each query that is executed on master to slave and exactly this datetime value is used for reexecuting query on the slave, there is no need for strict synchronization of
clocks on master and slave. (and this means that NOW() and other such functions are not executed independently on master and slave :)) 
So long-term solution proposed by Guilhem will work.