Bug #20819 INSERT DELAYED LAST_INSERT_ID() breaks statement-based replication

• Submitted: 3 Jul 2006 8:58
• Modified: 26 Oct 2007 2:36
• Reporter: Guilhem Bichot
• Status: Closed
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.0-bk
• OS: Linux (linux)
• Assigned to: Lars Thalmann
• CPU Architecture: Any

[3 Jul 2006 8: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 16:16] Lars Thalmann

Guilhem, this bug does not exist in 5.1 with MIXED binlog format,
right?

[7 Nov 2006 17:36] Guilhem Bichot

Lars: right, because in 5.1 in mixed mode INSERT DELAYED uses row-based binlogging.

[24 Oct 2007 9:11] Lars Thalmann

Fixed in 5.1 using MIXED mode.  Won't be fixed in lower versions.

[26 Oct 2007 2:36] Paul DuBois

Limitation is documented. No changelog entry needed.