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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.059 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.