Bug #41140 Failure of replication on triggered insert if innodb_autoinc_lock_mode > 0
Submitted: 30 Nov 2008 23:41 Modified: 28 Aug 2009 16:40
Reporter: Elena Stepanova Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.1.22-rc, 5.1.30 OS:Any
Assigned to: Andrei Elkin CPU Architecture:Any

[30 Nov 2008 23:41] Elena Stepanova
Description:
If innodb_autoinc_lock_mode is set to default 1 (or 2), statement- or mixed-based replication breaks on an insert-like statement performed via a trigger with 'Duplicate entry' error on the auto-increment column.

The problem does not happen if innodb_autoinc_lock_mode is set to 0, so it can be used as a workaround.

I'm not quite sure whether it is a replication problem, or InnoDB, or anything else. Setting the category to 'Replication' for now.

How to repeat:
set session transaction isolation level repeatable read;
use test;
drop table if exists l;
drop table if exists t;
create table t ( i int, f char(8) ) engine = innodb;
create table l ( i int not null auto_increment, primary key (i), f char(8) ) engine = innodb;
delimiter |
create trigger tr after update on t for each row begin insert into l (f) values (new.f); end |
delimiter ; 
insert into t values ( 1, 'init' ), ( 2, 'init' ), ( 2, 'init' );
update t set f = 'update1' where i = 2;
# at this point master and slave both show AUTO_INCREMENT=4 in show create table l
update t set f = 'update2' where i = 2; 

# At this poing replication breaks. 
# show slave status says
# Last_Error: Error 'Duplicate entry '4' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'update t set f = 'update2' where i = 2'

# It is important that the update affects at least 2 rows (or any number above 1), so at least 2 records per transaction are inserted by the trigger.
# Also, I could not get the same effect without a trigger -- for example, with the same two tables t and l and the same contents of table t, performing insert into l (f) select f from t where t.i = 2 does not cause the problem.  

Suggested fix:
Fix the duplicate key problem (the manual declares that the consecutive lock mode is safe for SBR.)
[7 Jan 2009 10:35] Boris Uchitel
I've find that the current value of the autoinc column is not advancing in the slave under the conditions defined in the description.

In my case, when failing over to the slave server, the inserts in the table produced rows with lower autoinc values then expected.
[23 Jun 2009 19:14] Sveta Smirnova
Bug #45677 about same behavior, but with innodb_autoinc_lock_mode=0
[25 Jun 2009 9:25] Magne Mæhre
Jon Olav and I have looked at this one, and we're not able to reproduce this one on neither Linux nor Solaris.  The similar bug #45677 is, on the other hand, reproducible.

I'm unassigning myself, and recommend that the replication team checks both of these.
[29 Jun 2009 12:06] Elena Stepanova
I agree -- I still can reproduce the problem (replication failure) as described on versions 5.1.22-5.1.30, but not on 5.1.31 or higher.

Regarding bug#45677, see also bug#26316 which might be related.
[28 Aug 2009 16:40] Andrei Elkin
Could not reproduce the problem with the current built. There must have been some changes in the code done. Even the preparation step 

show create table l

reports

CREATE TABLE `l` ... ENGINE=InnoDB AUTO_INCREMENT=3 

i.e 3 not the claimed 4.