Description:
The binlog does not record the CREATE PROCEDURE or CALL statements. However each of the INSERT statements executed from within the procedure and all their required INSERT_ID and LAST_INSERT_ID values were recorded. The stored procedure need not even exist on the slave for the two servers to remain in sync. It would appear that in recent versions of MySQL the LAST_INSERT_ID() function is indeed replication safe. This is true even when LAST_INSERT_ID() is executed within a stored procedure multiple times.
This is contrary to what the documentation states in http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
"A stored procedure that uses LAST_INSERT_ID() does not replicate properly. When a statement uses a stored function that inserts into an AUTO_INCREMENT column, the generated AUTO_INCREMENT value is not written into the binary log, so a different value can in some cases be inserted on the slave."
I believe the case that was causing last_insert_id() to not replicate correctly has been corrected as of 5.0.26 with Bug #21726 unless I am missing some other case failure case.
Please correct the replication-features.html page to reflect the current state of affairs.
How to repeat:
setup replication
create table table1 (field1 int auto_increment primary key, field2 varchar(10));
create table table2 (field1 int auto_increment primary key, field2_t1id int , field3 varchar(10));
create table table3 (field1 int auto_increment primary key, field2_t2id int , field3 varchar(10));
DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `repl_test`
(IN first varchar(10), IN middle varchar(10), IN last varchar(10))
BEGIN
insert into table1 (field2) VALUES(first);
insert into table2 (field2_t1id, field3) VALUES(last_insert_id(), middle);
insert into table3 (field2_t2id, field3) VALUES(last_insert_id(), last);
END $
DELIMITER ;
I then called this procedure using CALL repl_test('Foo', 'Bar', 'Bass');
Suggested fix:
I believe the case that was causing last_insert_id() to not replicate correctly has been corrected as of 5.0.26 with Bug #21726 unless I am missing some other case failure case.
Please correct the replication-features.html page to reflect the current state of affairs.
