MySQL Bugs: #12481: Using NOW() in a stored function breaks statement based replication

Bug #12481	Using NOW() in a stored function breaks statement based replication
Submitted:	10 Aug 2005 6:31	Modified:	18 Aug 2005 20:47
Reporter:	Michael Widenius	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0	OS:
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
Using NOW() in a stored function breaks statement based replication.
(Stored procedures will replicate correctly in 5.0 GA)

How to repeat:
In one connection do:
select get_lock("test",2);

In another do:

delimiter ;;
drop function if exists f1;;
drop table if exists t1;;
create function f1() returns varchar(32)
begin
  -- Wait one second to change time
  set @a=get_lock("test",2);
  return (select now());
end;;

create table t1 (a datetime,b datetime);;
insert into t1 values ("2003-03-03","2003-03-03"),(f1(),f1()),(now(),now());;
select a=b from t1;;
select * from t1;;

Suggested fix:
Make NOW() a constant for the duration of a stored function (like it will be in triggers and in functions invoked by triggers)
When we have row level replication, add a new function WALLCLOCK() that works like NOW() but is re-evaluated for each invocation. When WALLCLOCK() is used, this will force the statement to be replicated by row instead of by statement.

The advantages of this approach is:

- Easy to understand and document
- Predictable behavior (works the same in statements, functions and triggers)
- Will allow statement based replication to work with most stored functions
- NOW() will work for almost all cases, except for people that want to do time things in stored functions (not likely to be used in a production environment)
- WALLCLOCK() will allow one to easily get a different time per row while
  doing an update.

Disadvantages:
- Not compliant with standard SQL

Facts from other DMS:

SQL SERVER
- SQL server doesn't allow one to use NOW() in a function
  (so we are fine here)
- TRIGGERS will work as in MySQL

Oracle
- SYSDATE() is re-evaluated for each statement row in a function
  (different, but by using wallclock() would allow one solve this easily)
- SYSDATE() is re-evaluated for each statement row in a trigger
  Against ANSI standard.
  (different, but by using wallclock() would allow one solve this easily)

Other possible ways to solve this:

2)

Only let NOW() be a constant in functions that are marked DETERMINISTIC

Advantages:
- Would solve the replication problem
- Logical (as one shouldn't use a non determinstic function call in a
  function that is marked DETERMINISTIC

Disadvantages
- Harder to document and understand

3)

Add a status flag that allows the user to specify if NOW() should be
constant in a function or not.

Disadvantages
- Hard to document and remember (for the user)
- Will be hard to agree on what should be default value of this status flag
- One can break replication by marking a function that uses NOW() DETERMINISTIC
  when the flag is not set

verified against mysql 5.0.12-beta on suse linux 9.3

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Fix will be in 5.0.12

Documented in 5.0.12 changelog:

<listitem><para><literal>NOW()</literal>, <literal>CURRENT_TIME</literal> and values generated by timestamp columns are now constant for the duration of a stored function. This prevents the breaking of statements-based replication. (Bug #12481)</para></listitem>