Bug #20339 stored procedure using LAST_INSERT_ID() does not replicate statement-based

• Submitted: 8 Jun 2006 12:15
• Modified: 29 Sep 2006 2:32
• Reporter: Guilhem Bichot
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.1-bk
• OS: Linux (linux)
• Assigned to: Guilhem Bichot
• CPU Architecture: Any

[8 Jun 2006 12:15] Guilhem Bichot

Description:
Didn't test with 5.0 (but likely to fail there too).
Testcase inspired from rpl_insert_id.test.

How to repeat:
-- source include/master-slave.inc

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

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

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

delimiter |;
create procedure lid()
begin
  declare res int;
  insert into t2 (last_id) values (last_insert_id());
  insert into t1 (last_id) values (last_insert_id());
end|
delimiter ;|
call lid();
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;

Results I see:
+ call lid();
+ select * from t1;
+ last_id
+ 1
+ select * from t2;
+ id    last_id
+ 1     0
+ select * from t1;
+ last_id
+ 0
+ select * from t2;
+ id    last_id
+ 1     0
So t1 is not replicated correctly. In the master's binlog there is:
# at 673
#060608 14:12:24 server id 1  end_log_pos 701   Intvar
SET LAST_INSERT_ID=0;
# at 701
#060608 14:12:24 server id 1  end_log_pos 729   Intvar
SET INSERT_ID=1;
# at 729
#060608 14:12:24 server id 1  end_log_pos 850   Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1149768744;
SET @@session.time_zone='SYSTEM';
insert into t2 (last_id) values (last_insert_id());
# at 850
#060608 14:12:24 server id 1  end_log_pos 878   Intvar
SET LAST_INSERT_ID=0;
# at 878
#060608 14:12:24 server id 1  end_log_pos 906   Intvar
SET INSERT_ID=1;
# at 906
#060608 14:12:24 server id 1  end_log_pos 1027  Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1149768744;
insert into t1 (last_id) values (last_insert_id());

So no wonder: the LAST_INSERT_ID above says 0 while on master LAST_INSERT_ID() returned 1 in fact when inserting into t1 (due to the previous insert into t2).

[11 Jul 2006 8:53] Guilhem Bichot

FYI I have fixed this in 5.1.12 (but maybe you will want to fix it in 5.0):
ChangeSet
  [e-mail]|ChangeSet|20060709155219|25206    2006/07/09 17:52:19+02:00 [e-mail] +30 -0
  WL#3146 "less locking in auto_increment":
  this is a cleanup patch for our current auto_increment handling:
  new names for auto_increment variables in THD, new methods to manipulate them
  (see sql_class.h), some move into handler::, causing less backup/restore
  work when executing substatements. 
  This makes the logic hopefully clearer, less work is is needed in
  mysql_insert().
  By cleaning up, using different variables for different purposes (instead
  of one for 3 things...), we fix those bugs, which someone may want to fix
  in 5.0 too:
  BUG#20339 "stored procedure using LAST_INSERT_ID() does not replicate
  statement-based"

[21 Aug 2006 4:16] Lars Thalmann

Pushed into 5.1.12.

Document in manual that this is a limitation for 5.0.

[29 Sep 2006 2:32] Paul DuBois

Noted in 5.1.12 changelog.

A stored procedure that used LAST_INSERT_ID() did not replicate
properly using statement-based binary logging. 

Also noted as a replication limitation in the 5.0 manual.