MySQL Bugs: #9100: Replication: suspicious-looking binlog entries if function present

Bug #9100	Replication: suspicious-looking binlog entries if function present
Submitted:	10 Mar 2005 19:30	Modified:	10 Jun 2005 17:17
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug-log	OS:	Linux (SUSE 9.2)
Assigned to:	Guilhem Bichot	CPU Architecture:	Any

Description:
When writing to a binlog, MySQL writes INSERT
statements which, if actually reproduced,
would cause the addition of an extra row.
This is only true if an INSERT refers to a
function which does an INSERT.

How to repeat:
Start the server with --log-bin=tmplog

mysql> delimiter //
mysql> create function f41 () returns int begin insert into t41a values (1); return 5; end//
Query OK, 0 rows affected (0.01 sec)

mysql> create table t41 (s1 int)//
Query OK, 0 rows affected (0.01 sec)

mysql> create table t41a (s1 int)//
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t41 values (f41())//
Query OK, 1 row affected (0.01 sec)

Quit. Look at the tmplog file with the utility
mysqlbinlog tmplog.000001. It shows:
insert into t41a values (1);
insert into t41 values (f41());

... If those two statements were executed, there
would be three rows: one for "insert into t41a", one
for "insert into t41", and one for "insert into
t41a caused by invocation of f41". This cannot be
tested with replication yet because of bug#2610.

Yes, this issue will be addressed when "replication of stored procedures (CREATE PROCEDURE and CALL with right privileges)" (WL#2146) is completed. We will always disable binlogging inside the function call.

Being worked on now, as well as companion BUG#2610.

Too hard and imperfect with statement-level binary logging of 5.0. Will be fixed in 5.1 with record-level binary logging. To avoid slave stops, I'll just disable binlogging of these suspicious entries in 5.0.

I disabled binlogging of the routine's substatements in 5.0.5. Note that SELECT myfunc() will not be binlogged even if myfunc() updates data (will be documented); but INSERT INTO t VALUES(myfunc()) will be binlogged (what decides is if the caller gets binlogged; the function changes nothing to binlogging). In 5.1 we will have record-level binary logging which will remove this to-be-documented caveat.

Will talk with Paul about documentation of this bugfix.

Noted in 5.0.5 changelog.
Further more general documentation
is pending, relating to Bug #2610.

Changelog entry has been moved to 5.0.6.