MySQL Bugs: #12090: multiple calls to NOW(), LAST_INSERT_ID() in a routine may break replication

Bug #12090	multiple calls to NOW(), LAST_INSERT_ID() in a routine may break replication
Submitted:	21 Jul 2005 20:31	Modified:	14 Nov 2005 13:04
Reporter:	Guilhem Bichot	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0	OS:	Any (all)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
When we binlog a CALL mysp(), we store, in the Query_log_event containing the CALL, the timestamp. But a routine does not have a unique timestamp, see how-to-repeat.
Thus, there is no notion of "the unique current time" for a routine, so some routines cannot be replayed properly by slave or mysqlbinlog.

How to repeat:
Start mysqld in a debugger, set a breakpoint on mysql_insert(). Do:
MASTER> create table tim (a datetime);
Query OK, 0 rows affected (0.16 sec)

MASTER> delimiter /;
MASTER> create procedure _now() deterministic begin insert into tim values(now()); insert into tim values(now()); end/
Query OK, 0 rows affected (0.06 sec)

MASTER> call _now()/

gdb stops in mysql_insert: continue; then it stops again; wait a few seconds, and continue. Then:

Query OK, 1 row affected (8.34 sec)

MASTER> select * from tim/
+---------------------+
| a                   |
+---------------------+
| 2005-07-21 21:54:12 |
| 2005-07-21 21:54:15 |
+---------------------+

which shows that two values of NOW() where used.
And mysqlbinlog shows that the value which is recorded into the binlog event is 21:54:15 (the timestamp of the last substatement).

Same problem with LAST_INSERT_ID()

Note: since binlogging of stored procedures calls has been changed, the problem remains only for functions and for stored procedures called via functions.

duplicate of BUG#12481 already fixed.