Bug #31540 incorrect auto_increment values used for multi-row insert trigger
Submitted: 11 Oct 2007 16:01 Modified: 9 Jan 2008 14:12
Reporter: Matthew Lord Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.1.22 OS:Any
Assigned to: Sunny Bains CPU Architecture:Any
Tags: auto_increment, trigger

[11 Oct 2007 16:01] Matthew Lord
Description:
Duplicate key errors come up when a multi-row insert is done which fires a trigger.  If table "b" is a MyISAM table then everything works fine.
See how to repeat.

How to repeat:
create table a (a int, val char(1)) engine="InnoDB";
create table b (b int auto_increment primary key, val char(1)) engine="InnoDB";

create trigger a_after_insert after insert on a for each row insert into b set val = NEW.val;

insert into a values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'), (123, 'd'), (123, 'e'), (123, 'f'), (123, 'g');
[12 Oct 2007 18:28] Konstantin Osipov
Unassigning from my lead, since this is an InnoDB only.
[15 Oct 2007 14:52] Sunny Bains
Fix is in our v5.1 trunk. It was fixed independently as part our code review prior  to the bug report.
[8 Jan 2008 18:05] Timothy Smith
Docs team:  This fix went into 5.1.23 and 6.0.4 (was included in snapshot 5.1-ss1989).
[9 Jan 2008 14:12] Paul DuBois
Noted in 5.1.23, 6.0.4 changelogs.

Spurious duplicate-key errors could occur for multiple-row inserts
into an InnoDB table that activate a trigger.