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();
+------------+
| 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.
Trackbacks
No Trackbacks
Comments
Display comments as Linear | Threaded
Singaravelan on :