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

[9 May 2006 9:32] Guilhem Bichot
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.
[9 May 2006 12:19] Guilhem Bichot
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.
[9 May 2006 20:57] Guilhem Bichot
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.
[2 Jun 2006 15:14] Guilhem Bichot
preparing a patch implementing the "mixed mode" solution.
[3 Jun 2006 9:50] Lars Thalmann
See also BUG#20188
[7 Jun 2006 15:27] Bugs System
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
[16 Jun 2006 13:27] Guilhem Bichot
http://lists.mysql.com/commits/7760
[11 Jul 2006 8:47] Guilhem Bichot
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.
[11 Jul 2006 8:51] Guilhem Bichot
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.
[28 Jul 2006 4:40] Paul DuBois
Noted in 5.1.12 changelog.
[23 Jan 2008 17:42] Lars Thalmann
When fixing this, please make sure BUG#33029 is fixed too.
[29 May 2008 6:26] Daniel Jaenecke
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?
[24 Mar 2009 12:06] Sveta Smirnova
Bug #41754 was marked as duplicate of this one.
[24 Mar 2009 18:25] Sveta Smirnova
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.