Bug #20341 stored function inserting into one auto_increment puts bad data in slave
Submitted: 8 Jun 2006 13:31 Modified: 29 Sep 2006 2:40
Reporter: Guilhem Bichot Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0-bk OS:Linux (linux)
Assigned to: Guilhem Bichot CPU Architecture:Any
Tags: bfsm_2007_05_03

[8 Jun 2006 13:31] Guilhem Bichot
Description:
didn't test in 5.0.
Contrary to BUG#19630, this time the bug is in the slave (or in the master if you consider you are piping the output of mysqlbinlog, containing SET INSERT_ID statements, into the master, to restore from backup).
When replicating a stored function call which has inserted into an autoincrement column on master,  the slave first sets thd->next_insert_id to the value inserted on master (read from Intvar_log_event, the "SET INSERT_ID" event), then executes the function call: this calls reset_sub_statement_state(), which does next_insert_id=0. This has the effect of forgetting about the value inserted on master.
Same problem if executing SET INSERT_ID when replaying a binlog with mysqlbinlog.
Note that the fix isn't simply to remove "next_insert_id=0" from reset_sub_statement_state(), because when the substatement is going to update a table which was not updated by the caller, it is certainly a good idea to not use the next_insert_id of the table updated by the caller, and to phone the table updated by the substatement to ask it for a new id.

How to repeat:
inspired by rpl_insert_id.test:
-- source include/master-slave.inc

connection master;
--disable_warnings
drop table if exists t1, t2;
--enable_warnings

create table t1 (
  last_id int
);
create table t2 (
  id int not null auto_increment,
  last_id int,
  primary key (id)
);

delimiter |;
create function insid() returns int
begin
  insert into t2 (last_id) values (0);
  return 0;
end|
delimiter ;|
# here we do something on master to bump the internal autoinc counter of the table;
# there are other ways: ALTER TABLE AUTO_INCREMENT=#, or SET INSERT_ID, or
# start a transaction which inserts and roll it back (use InnoDB for that).
set sql_log_bin=0;
insert into t2 values(1,0),(2,0),(3,0);
delete from t2;
set sql_log_bin=1;
select insid();
select * from t1;
select * from t2;

sync_slave_with_master;
select * from t1;
select * from t2;
connection master;

drop table t1, t2;
sync_slave_with_master;
[23 Jun 2006 4:12] Chris DiMartino
We are experiencing the same issue in 5.0.21-pro-gpl-log.  This is a severity S2 for us, as it breaks our redundancy setup by breaking the replication to the slaves!

Any ideas on a date for a fix?
[26 Jun 2006 20:56] 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/8265
[29 Jun 2006 12:54] Guilhem Bichot
there is another bug in the master
[11 Jul 2006 9:08] Guilhem Bichot
scenario for the bug was:
when a statement used a stored function which inserted into an auto_increment column, the used auto_increment value was not written into the binary log,
so a different value could in some cases be inserted on the slave.
[21 Aug 2006 4:07] Lars Thalmann
Fix pushed into 5.1.12.

Please document this as a limitation for 5.0.
[29 Sep 2006 2:40] Paul Dubois
Noted in 5.1.12 changelog, and documented as a limitation
in the 5.0 manual.