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
• Status: Closed
• 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.