MySQL Bugs: #19630: stored function inserting into two auto_increment breaks statement-based binlog

Bug #19630	stored function inserting into two auto_increment breaks statement-based binlog
Submitted:	9 May 2006 9:32	Modified:	10 Jul 2008 18:08
Reporter:	Guilhem Bichot	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.0 only	OS:	Linux (linux)
Assigned to:	Lars Thalmann	CPU Architecture:	Any
Tags:	autoincrement, bfsm_2007_05_03, mixed_binlog_format, replication

Description:
The statement-based binlog contains at most one INSERT_ID event per QUERY event, this is not enough information if the QUERY is a stored function call and that function inserts into N>1 auto_increment columns (the QUERY would need N auto_inc values to be stored in the binlog to be sure to replay properly on slave or at recovery from backup&binlog).

How to repeat:
# on master do:
create table i1 (a int primary key auto_increment);
create table i2 (a int primary key auto_increment);
delimiter | ;
create function f (x int) returns int deterministic begin insert into i1 values(null); insert into i2 values(null); return 1; end |
select f(0) |
select * from i1 | select * from i2 | # returns 1 and 1
# on slave there is also 1 and 1, fine.
# Now there are many ways to provoke the bug, all use two inverse operations
# which compensate each other except that their net effect is that
# they advance the auto_increment counter of i2. For example:
# a) if i2 was of type InnoDB, on master do this very real-life case:
# begin; insert into i2 values(null); rollback; I didn't do this.
# b) or on slave (that's what I did)
insert into i2 values(2),(3),(4);delete from i2 where a>=2;
# Now on master:
select f(0) |
select * from i1 | select * from i2 | # returns 1,2 and 1,2
# on slave the same selects return 1,2 and 1,5 !!

# In case a), writing to the binlog even if the transaction rolls back is not a solution
# as later the DBA could mysqldump the slave and reload it, and 
# the auto_increment counter is not in the output of mysqldump.

Btw the same problem happens when the function uses RAND() twice, but that one is documented:
http://dev.mysql.com/doc/refman/5.0/en/stored-procedure-logging.html
I doubt that we can just go ahead and document that "a stored function should not insert NULL into two auto_increment fields to not break binlog" as this would be quite a strong limitation.
The same problem also exists if the function uses LAST_INSERT_ID() twice, which is not documented.

on top of the Insert_id_log_event, there is also sometimes the "stored-in-query" one:
  if (auto_increment_increment != 1)
  {
    *start++= Q_AUTO_INCREMENT;
    int2store(start, auto_increment_increment);
    int2store(start+2, auto_increment_offset);
    start+= 4;
  }
which makes it still more complicated.

preparing a patch implementing the "mixed mode" solution.

See also BUG#20188

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7361

http://lists.mysql.com/commits/7760

Not fixed in 5.0 (agreed with Lars).
pushed into replication team tree 5.1, will be in 5.1.12.
ChangeSet
  guilhem@gbichot3.local|ChangeSet|20060709150047|19215    2006/07/09 17:00:47+02:00 guilhem@gbichot3.local +15 -0
  * Mixed replication mode * :
  1) Fix for BUG#19630 "stored function inserting into two auto_increment breaks
  statement-based binlog":
  a stored function inserting into two such tables may fail to replicate
  (inserting wrong data in the slave's copy of the second table) if the slave's
  second table had an internal auto_increment counter different from master's.
  Because the auto_increment value autogenerated by master for the 2nd table
  does not go into binlog, only the first does, so the slave lacks information.
  To fix this, if running in mixed binlogging mode, if the stored function or
  trigger plans to update two different tables both having auto_increment
  columns, we switch to row-based for the whole function.
  We don't have a simple solution for statement-based binlogging mode, there
  the bug remains and will be documented as a known problem.

Note that the support team would like this to be fixed in 5.0 too (but then it could not be the same fix as in 5.1). Contact: Sinisa.

Noted in 5.1.12 changelog.

When fixing this, please make sure BUG#33029 is fixed too.

The comment from Guilhem Bichot / 11 Jul 2006 says that this bugfix might be pushed to 5.0.x, too. 
Are there any chances this will happen (soon) or will this bug never be fixed in 5.0?

Bug #41754 was marked as duplicate of this one.

To clarify fix: to avoid this bug use RBR or MBR. Bug still repeatable with SBR due to its nature and this seems to be reason why it would not be fixed in 5.0.