Bug #14304 auto_increment field incorrect set from within stored procedure (insert select)
Submitted: 25 Oct 2005 19:24 Modified: 8 Dec 2005 5:27
Reporter: Sokolov Sergey Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0.15/BK source 5.0 OS:Linux (DEBIAN 3.1, FREEBSD 4.11)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[25 Oct 2005 19:24] Sokolov Sergey 
Description:
Adding by:
http://bugs.mysql.com/bug.php?id=11890

bag script ( error: "DUPLICATE ENTRY '7' FOR KEY 1") 

How to repeat:
USE test;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
  ID  INT UNSIGNED  NOT NULL  AUTO_INCREMENT,
  Text  VARCHAR(255)  NOT NULL,
  PRIMARY KEY (ID)
)
ENGINE = MyISAM;

DROP TABLE IF EXISTS t2;

CREATE TABLE t2 (
  ID  INT UNSIGNED  NOT NULL  AUTO_INCREMENT,
  Text  VARCHAR(255)  NOT NULL,
  PRIMARY KEY (ID)
)
ENGINE = MyISAM;

INSERT INTO
  t1 (Text)
VALUES
 ('Test1'), ('Test2'), ('Test3');

INSERT INTO
       t2 (  ID, Text )
VALUES ( 7, 'test' );

DROP PROCEDURE IF EXISTS testproc;

DELIMITER //

CREATE PROCEDURE testproc ()
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN

  INSERT INTO t1 (Text)
  SELECT Text
  FROM t1;

  INSERT INTO t2 (Text)
  SELECT Text
  FROM t1;

END; //

DELIMITER ;

CALL testproc ();
[16 Nov 2005 15:41] Robert Locklear 
I've had this problem on Windows XP as well in 5.0.15
[23 Nov 2005 17:18] Paul Freeman 
I'm having the same problem to, on Linux 2.6.13, MySQL 5.0.15 source, with InnoDB engine
[30 Nov 2005 21:26] Konstantin Osipov 
The patch submitted on IRC has been approved by email.
[1 Dec 2005 10:28] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32899
[1 Dec 2005 14:57] Ramil Kalimullin 
fixed in 5.0.17
[8 Dec 2005 5:27] Paul DuBois 
Noted in 5.0.17 changelog.
[18 Jan 2006 11:11] Konstantin Osipov 
Bug#11890 is marked as duplicate of this bug.