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:
Category:MySQL Server Severity:S1 (Critical)
Version:5.0 OS:
Assigned to: Michael Widenius CPU Architecture:Any

[10 Aug 2005 6:09] Michael Widenius
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
[12 Aug 2005 17:16] Jim Winstead
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
  do sleep(2);
  return now();

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();

delimiter ;//

insert into t1 set a = now();

select * from t1;

drop trigger t_before;
drop function t_slow_now;
drop table t1;
[15 Aug 2005 17:32] Michael Widenius
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

Additional info:

Fix will be in 5.0.12
[17 Aug 2005 23:28] Jim Winstead
This change was made in 5.0.12.
[18 Aug 2005 20:49] Mike Hillyer
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>
[11 Jan 2008 12:18] Bugs System
Pushed into 6.0.5-alpha
[11 Jan 2008 12:21] Bugs System
Pushed into 5.1.23-rc
[11 Jan 2008 12:22] Bugs System
Pushed into 5.0.56