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