Bug #20819 INSERT DELAYED LAST_INSERT_ID() breaks statement-based replication
Submitted: 3 Jul 2006 10:58 Modified: 26 Oct 2007 4:36
Reporter: Guilhem Bichot
Status: Closed
Category:Server: Replication Severity:S3 (Non-critical)
Version:5.0-bk OS:Linux (linux)
Assigned to: Lars Thalmann Target Version:

[3 Jul 2006 10:58] Guilhem Bichot 
Description:
INSERT DELAYED VALUES(LAST_INSERT_ID()) inserts a different value on slave from on
master.

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

connection master;
create table t1(b int auto_increment primary key);
create table t2(b int auto_increment primary key);

connection master1;
insert into t1 values (null);
select last_insert_id();
connection master;
insert into t1 values (null);
select last_insert_id();
insert delayed into t2 values(last_insert_id());
sleep 3; # let DELAYED reach the table
select * from t2;
sync_slave_with_master;
select * from t2;

I observe 2 in the master's t2, and 1 in the slave's t2.
[7 Nov 2006 17:16] Lars Thalmann 
Guilhem, this bug does not exist in 5.1 with MIXED binlog format,
right?
[7 Nov 2006 18:36] Guilhem Bichot 
Lars: right, because in 5.1 in mixed mode INSERT DELAYED uses row-based binlogging.
[24 Oct 2007 11:11] Lars Thalmann 
Fixed in 5.1 using MIXED mode.  Won't be fixed in lower versions.
[26 Oct 2007 4:36] Paul DuBois 
Limitation is documented. No changelog entry needed.