MySQL Bugs: #15101: sysdate() digregards SET TIMESTAMP

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:	None
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

Description:
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.

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?

Probably related to bug#12480

See also bug#12481

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.

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