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.
1 2 3 4 5 6 7 | mysql> SELECT NOW(),SYSDATE(); +---------------------+---------------------+ | NOW() | SYSDATE() | +---------------------+---------------------+ | 1999-01-01 00:00:00 | 2012-11-29 05:50:03 | +---------------------+---------------------+ 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:
1 2 | SET TIMESTAMP=UNIX_TIMESTAMP('1999-01-01'); SELECT NOW(),SYSDATE(); |
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.
SYSDATE() is a different function than NOW(), which is an alias to CURRENT_TIMESTAMP(). See http://www.pythian.com/news/1295/does-anybody-really-know-what-time-it-is/
In particular, look at the difference between:
SELECT NOW(), SLEEP(2), NOW();
and
SELECT SYDATE(), SLEEP(2), SYSDATE();
Yes Sheeri, that was my point. NOW() and SYSDATE() are not the same. NOW() honors the TIMESTAMP variable whereas SYSDATE by default does not. By default SYSDATE() always returns the current time (not deterministric). NOW() returns a deterministic time taken at the start of the statement.
Also, your last sentence gives a direction with no reason why (and a typo, the option is sysdate-is-now, not sysdate-as-now):
“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.”
You show how SYSDATE() function is different, and then say if people want to use it they should use –sysdate-is-now, which makes SYSDATE() an alias to NOW()…..which means you lose the non-deterministic nature of SYSDATE. But wouldn’t it also mean SYSDATE() would use the TIMESTAMP variable, just like NOW? And you never say your reasoning, which presumably is to make sure it’s replication-safe?
So I’m not sure why you’re recommending “sysdate-is-now” if people want to use SYSDATE….the whole point of using SYSDATE is that it’s *not* NOW.
If you want SYSDATE() to replicate properly you have to make it behave like NOW().
Justin – I agree. But then SYSDATE acts like NOW. Not like SYSDATE.
The reason to use SYSDATE is if you really want the functionality of NOW but the developers have used SYSDATE instead. Someone reading your article might think that they’re getting the behavior you describe with SYSDATE if they use SYSDATE and set –sysdate-is-now. Except that’s not what they’d get. They’d get SYSDATE being an alias to NOW and acting exactly like NOW.
As you describe it (which is accurate), SYSDATE is non-deterministic and not safe for replication. And it cannot be safe for replication. If you use –sysdate-is-now, SYSDATE turns into NOW; that’s how it’s safe for replication.
I have to agree with Sheeri here, one who does know know what the variable change is doing could assume that its really still sysdata just its now replication safe. Which is wrong, there are many places SYSDATE is used and the fact is it non-deterministic is actually a good thing. For example if you want to know the time-stamp the code was added to the master and when it was replicated SYSDATE mixed with now would give you this as a developer. However if a DBA goes in and follows your advice the application is not behaving exactly how the developer intended. To expand the developer would run some analytics on this and think everything is OK even if average slave drift is 3 seconds, and causing race conditions in his/her application.
The correct response here would not be using the variable but reviewing if SYSDATE vs NOW should really be used in a given case. In larger companies arbitrary changes like this could have reports,compliance, and logical issues with the applications. As the DBA and Developer are no longer the same person.
Don’t get me wrong its a useful setting for some stuff however it should be used with the utmost care as it has the ability to change logic and data so profoundly (for good and bad).