Does anybody really know what time it is?

Oct 9, 2008 / By Sheeri Cabral

Tags:

This is a post about SYSDATE() and NOW() and CURRENT_TIMESTAMP() functions in MySQL.

Firstly, note is that of these three, only CURRENT_TIMESTAMP() is part of the SQL Standard. NOW() happens to be an alias for CURRENT_TIMESTAMP() in MySQL.

Secondly, note that replication does not work well with non-deterministic functions. And “hey, what time is it?” is non-deterministic. Ask it twice, with a second apart between asking, and both times you get different results (with at least second precision).

You can start to see the problem here….but there’s more….

Now, MySQL makes NOW() and CURRENT_TIMESTAMP() deterministic functions in replication by setting the timestamp. If you look in the binary logs, you’ll see statements start like this:

# at 98
#081009 10:03:38 server id 44  end_log_pos 162  Query   thread_id=222001179     exec_time=874   error_code=0
SET TIMESTAMP=1223571818/*!*/;

So MySQL sets the value of TIMESTAMP, which means that CURRENT_TIMESTAMP() is replication-safe, which also means that NOW() is.

SYSDATE() is not replication-safe. We found this out for a client the hard way — we realized that none of the times matched while using mk-table-checksum when checking if the data between a master and slave was in sync. In fact, the times were exactly 7 hours different — the difference between Pacific Time and GMT (the machines were in Pacific Time).

As it turned out, their queries used SYSDATE(), which isn’t replication-safe. However, there is a solution easier than changing tons of code: the sysdate-is-now option to MySQL. For this particular case, we decided it was beneficial to restore the data on the database and set sysdate-is-now on the master and slave.

This solved the problem….but there is a functional difference between NOW() and SYSDATE(). Using sysdate-is-now makes both functions act the same.

Here’s how NOW() works:

mysql> SELECT NOW(), SLEEP(5), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(5) | NOW()               |
+---------------------+----------+---------------------+
| 2008-10-09 10:42:53 |        0 | 2008-10-09 10:42:53 | 
+---------------------+----------+---------------------+
1 row in set (5.00 sec)

See that? MySQL got the TIMESTAMP value for the statement at the beginning, and then used it for all TIMESTAMP values in that statement. SYSDATE(), on the other hand, behaves differently, getting the timestamp afresh each time:

mysql> SELECT SYSDATE(), SLEEP(5), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(5) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2008-10-09 10:43:08 |        0 | 2008-10-09 10:43:13 | 
+---------------------+----------+---------------------+
1 row in set (5.00 sec)

You lose that functionality with sysdate-is-now.

6 Responses to “Does anybody really know what time it is?”

  • Hi!

    “…Note that replication does not work well with deterministic functions…”

    slip of the keyboard…should be *non-deterministic* functions.

    In MySQL 5.1, it should be ok for replication when using mixed or row-based binary logging. In this case, the resultant value of the non-deterministic function call is logged instead of the fact that the call was made. So, the slave does not get to execute the call but gets passed the value resulting from the master’s call.

    regards,

    Roland

  • Sheeri Cabral says:

    whoops! Fixing the post now. Thanx!

  • Scott says:

    I suppose you could exploit that fact to keep a record of how far your slaves tend to lag behind, if you were interested in such statistics.

  • Mats Kindahl says:

    Note that it is also possible to write replication-safe code by assigning non-deterministic values to user-defined variables. In other words, the following code replication-safe:

    SET @foo = SYSDATE();
    INSERT INTO t1 VALUES (@foo);

  • lamarche says:

    Just to make sure that utc_timestamp() is replication safe, I did the following test:

    ### On master

    mysql> create table test as select now(), sysdate(), utc_timestamp();
    Query OK, 1 row affected (0.04 sec)
    Records: 1 Duplicates: 0 Warnings: 0

    ### I then restarted the slave and saw that utc_timestamp is replication safe:

    mysql> select * from test;
    +———————+———————+———————+
    | now() | sysdate() | utc_timestamp() |
    +———————+———————+———————+
    | 2008-10-20 16:12:28 | 2008-10-20 16:12:28 | 2008-10-20 20:12:28 |
    +———————+———————+———————+
    1 row in set (0.00 sec)

  • haimin life trace » Blog Archive » mysql 时间知多少 says:

    […] 该文只是做一个记录 详细信息请refer Does anybody really know what time it is? […]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>