Skip to content

sysdate() weirdness

The behaviour of SYSDATE() has changed between MySQL 4.1 and 5.0. The manual indicates:
Within a stored routine or trigger, SYSDATE() returns the time at which it executes, not the time at which the routine or triggering statement began to execute. This differs from the behavior for NOW().
Obviously, this was implemented internally by having SYSDATE() ignore SET TIMESTAMP, as can be easily tested.

mysql> set timestamp=1;
Query OK, 0 rows affected (0.04 sec)

mysql> select now(), sysdate();
| now() | sysdate() |
| 1970-01-01 01:00:01 | 2005-11-23 21:21:59 |
1 row in set (0.02 sec)

This differs from the behaviour of MySQL 4.1: mysql> SELECT VERSION();
| 4.1.15-max |
1 row in set (0.00 sec)

mysql> select NOW(), SYSDATE();
| NOW() | SYSDATE() |
| 1970-01-01 01:00:01 | 1970-01-01 01:00:01 |
1 row in set (0.00 sec)
Unfortunately, this change has unwanted side effects, such as for example making SYSDATE() unuseable in replication. SET TIMESTAMP is used within the binlog to set the time for each statement so that NOW() and SYSDATE() give the same time on the slave as on the master, instead of the time of execution of the statement on the slave - since SYSDATE() ignores timestamps in 5.0, it will not replicate correctly. This bug is subtle and cost me more than half a day to find.

So if you are using 5.0 with replication, check your code for the use of SYSDATE() and watch out for Bug #15101.


No Trackbacks


Display comments as Linear | Threaded

Singaravelan on :

i am using mysql 5.0. I want to change my system date of server machine, from the clients machine is it possible to change the system date by using stored procedure or tigger plz help me

Add Comment

E-Mail addresses will not be displayed and will only be used for E-Mail notifications.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.

BBCode format allowed