MySQL Bugs: #12480: NOW() is not constant in a trigger

Bug #12480	NOW() is not constant in a trigger
Submitted:	10 Aug 2005 6:09	Modified:	18 Aug 2005 20:49
Reporter:	Michael Widenius	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0	OS:
Assigned to:	Michael Widenius	CPU Architecture:	Any

Description:
According to ANSI SQL NOW() in a trigger and all functions the triggers calls needs to evaluate to the start time of the statement that caused the trigger to invoke.

The current behavior is that NOW() (and any indirect usage like TIMESTAMP columns) is re-evaluated in the trigger statements and it's functions which breaks both the standard behavior and MySQL statement based replication.

How to repeat:
Full test case will follow shortly

here's a test case that uses the new sleep() function.

create table t1 (a timestamp default '2005-05-05 01:01:01',
                 b timestamp default '2005-05-05 01:01:01');
delimiter //;
create function t_slow_now() returns timestamp
begin
  do sleep(2);
  return now();
end;
//

insert into t1 set a = now(), b = t_slow_now();//

create trigger t_before before insert on t1
for each row begin
  set new.b = t_slow_now();
end
//

delimiter ;//

insert into t1 set a = now();

select * from t1;

drop trigger t_before;
drop function t_slow_now;
drop table t1;

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

Additional info:

Fix will be in 5.0.12

This change was made 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 or trigger. This prevents the breaking of statements-based replication. (Bug #12480, Bug #12481)</para></listitem>

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Pushed into 5.0.56