MySQL Bugs: #20341: stored function inserting into one auto

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

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;

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?

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

there is another bug in the master

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.

Fix pushed into 5.1.12.

Please document this as a limitation for 5.0.

Noted in 5.1.12 changelog, and documented as a limitation
in the 5.0 manual.