MySQL Bugs: #69709: Wrong behaviour described in Replication and System Functions for now() function

Bug #69709	Wrong behaviour described in Replication and System Functions for now() function
Submitted:	10 Jul 2013 9:17	Modified:	13 Nov 2013 14:22
Reporter:	Claudio Fumagalli	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5.32, 5.6.13	OS:	Linux
Assigned to:	Jon Stephens	CPU Architecture:	Any
Tags:	documentation, replication, system function

Description:
Result of example on now() behaviour described in "Replication and System Functions" page of documentation is not correct. 

I have executed the example and obtained that result of now() will reflect local server timezone whereas documentation suggest a different behaviour.

How to repeat:
I have the following configuration:

Master:
innodb_version	1.1.8
version	5.5.23-log
system_time_zone	EDT
time_zone	SYSTEM

Slave:
innodb_version	5.5.32
version	5.5.32-log
system_time_zone	UTC
time_zone	SYSTEM

/* On MASTER */

mysql> CREATE TABLE mytable (mycol TEXT);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO mytable VALUES ( NOW() );
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mytable;
+---------------------+
| mycol               |
+---------------------+
| 2013-07-10 04:08:14 |
+---------------------+
1 row in set (0.00 sec)

/* On SLAVE */

mysql>  SELECT * FROM mytable;
+---------------------+
| mycol               |
+---------------------+
| 2013-07-10 08:08:14 |
+---------------------+

IMHO timezone=SYSTEM with different system timezones on master and slave is to blame, 

  SELECT UNIX_TIMESTAMP(mycol) FROM mytable;

will probably produce the same result on both ...

I agree that is a configuration to blame, but documentation states:
"This means that the value as returned by the call to this function on the master is replicated to the slave. This can lead to a possibly unexpected result when replicating between MySQL servers in different time zones." 

And show an example in which server with different timezones return the same results for 

SELECT * FROM mytable;

But I have obtained a different behaviuor.
It is only a metter of documentation not code.

OK, another issue with cross-timezone replication: you need to explicitly set the mysqld server time_zone variable (afair it is either part of the replication events, or timestamps in there only get converted to UTC if the timezone is explicitly known)

When just using time_zone=SYSTEM conversions are not really going to happen as master and slave think they are in the same time zone while they are not ...

Thank you for the report.

I agree that user manual at http://dev.mysql.com/doc/refman/5.0/en/replication-features-functions.html does not specify all exceptions, but it says "This *can* lead", so this is a matter of the interpretation. Anyway, bug is verified for further processing by the Documentation Team.

The Manual, "For NOW(), the binary log includes the timestamp. This means that the value as returned by the call to this function on the master is replicated to the slave. ..." I think this is pretty clear. The value returned by now on the master is what the slave gets.

"... This can lead to a possibly unexpected result when replicating between MySQL servers in different time zones." The fact that the value is the same is what would be unexpected if we assumed that it wasn't the value being passed.

So this is not a bug.