Bug #15101 sysdate() digregards SET TIMESTAMP
Submitted: 21 Nov 2005 15:27 Modified: 12 Apr 2006 20:30
Reporter: Kristian Koehntopp Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.0.15/5.0.17 bk OS:Any (all)
Assigned to: Andrei Elkin CPU Architecture:Any

[21 Nov 2005 15:27] Kristian Koehntopp
Since 5.0.x, the behavior of sysdate() has been changed, presumeably to fix a problem with stored procedures. The net effect of this is that system() no longer obeys SET TIMESTAMP in replication.

I am with a customer who is in the process of migrating from 4.0 to 5.0. Right now we are setting up a replication from his old 4.0 server to 5.0, and that replication fails with duplicate key errors. This is because his primary keys are based on (bookmarkerid, timestamp) values ( 63, sysdate()) in the instance of the failing statement. The statement is current on the 4.0 master and executed okay on all five 4.0 slaves, but does not function on the 5.0 slave. The reason is obvious in the HOW TO REPEAT section.

The customer has a large body of code and is consistently using sysdate() everywhere in his Java and PHP code. The migration to 5.0 cannot be done without a sysdate() behaving as before, as the code change would be extremely large otherwise.

How to repeat:
mysql> set timestamp=1;
Query OK, 0 rows affected (0.00 sec)

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

Suggested fix:
Have sysdate() behave properly as before and create another time function that does not break the existing codebase and behaves as sysdate() 5.0 does right now.
[28 Jan 2006 13:37] Andrei Elkin
Have reproduced with 5.0.19.  Manual specifies the only difference between sysdate() and now() regards to stored procedure. I suspect there was a bug introduced for
sysdate() handling in sp. If now() respects what `set timestamp' tells so must do sysdate(). Any other claims?
[24 Feb 2006 15:41] Elliot Murphy
Probably related to bug#12480
[24 Feb 2006 15:43] Elliot Murphy
See also bug#12481
[11 Mar 2006 13:04] Andrei Elkin
fixed in 5.0.20 and 5.1.8-beta.

For documentation.

SYSDATE has two flavors 
   default: to be indeterministic oracle-like to return system timestamps
               different even within a query (starting from the fix for BUG#12562);
               It is thereafter replication-unsafe.
    and      alias to NOW() (this bug fix) as in 4.1 to alias new features of NOW for
               BUG#12480,BUG#12481 in 5.0 as well. 

Replication-safe aliasing of SYSDAT() to NOW() is actived via a new --sysdate-is-now server startup option.
[12 Apr 2006 20:30] Paul DuBois
The 5.0.13 change to SYSDATE() to cause it to differ from NOW()
has implications for binary logging, stored routines, replication.
Requires changes to:
- NOW() and SYSDATE() function descriptions
- Server options (--sysdate-is-now is new)
- Replication known problems
- Stored routine logging
- Changelog sections (5.0.20, 5.1.8)
- Upgrading-to-5.0 notes