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

[10 Mar 2005 19:30] Peter Gulutzan
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.
[10 Mar 2005 20:45] Guilhem Bichot
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.
[17 Apr 2005 8:57] Guilhem Bichot
Being worked on now, as well as companion BUG#2610.
[19 Apr 2005 9:28] Guilhem Bichot
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.
[20 Apr 2005 21:50] Guilhem Bichot
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.
[10 May 2005 14:00] Guilhem Bichot
Will talk with Paul about documentation of this bugfix.
[10 Jun 2005 17:17] Paul DuBois
Noted in 5.0.5 changelog.
Further more general documentation
is pending, relating to Bug #2610.
[14 Jun 2005 19:42] Paul DuBois
Changelog entry has been moved to 5.0.6.