Replication of the NOW() function (also, time travel)

November 29, 2012

Author

Justin Swanhart

Insight for DBAs

MySQL

Share this Post:

Notice the result of the NOW() function in the following query. The query was run on a real database server and I didn’t change the clock of the server or change anything in the database configuration settings.

mysql&gt; SELECT NOW(),SYSDATE();<br>+---------------------+---------------------+<br>| NOW()               | SYSDATE()           |<br>+---------------------+---------------------+<br>| 1999-01-01 00:00:00 | 2012-11-29 05:50:03 |<br>+---------------------+---------------------+<br>1 row in set (0.00 sec)

1	mysql> SELECT NOW(),SYSDATE();<br>+---------------------+---------------------+<br>\| NOW() \| SYSDATE() \|<br>+---------------------+---------------------+<br>\| 1999-01-01 00:00:00 \| 2012-11-29 05:50:03 \|<br>+---------------------+---------------------+<br>1 row in set (0.00 sec)

You may proceed to party like it is 1999.

How can the NOW() function return a value in the past?

The “secret” is the TIMESTAMP variable, which is a special MySQL variable that can be set by the MySQL client. MySQL adds special events in the binary log which set the TIMESTAMP and INSERT_ID (which is used for AUTO_INCREMENT replication) to the correct values to ensure that statements replicate properly.

Here is the SQL to produce the above output:

<br>SET TIMESTAMP=UNIX_TIMESTAMP('1999-01-01');<br>SELECT NOW(),SYSDATE();<br>

1	<br>SET TIMESTAMP=UNIX_TIMESTAMP('1999-01-01');<br>SELECT NOW(),SYSDATE();<br>

Notice that SYSDATE returns the correct date. This means the SYSDATE() function is not replication safe by default. If you want to use the SYSDATE function in your app in DML statements, make sure you use the –sysdate-as-now option to mysqld.