MySQL Bugs: #15889: Stored procedure using insert into select on auto increment doesnt work

Bug #15889	Stored procedure using insert into select on auto increment doesnt work
Submitted:	20 Dec 2005 13:09	Modified:	20 Dec 2005 18:55
Reporter:	Gustav Karlman	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15 - 5.0.16	OS:	Linux (Linux 2.6.14)
Assigned to:		CPU Architecture:	Any

Description:
When having an sp and trying to do:
INSERT INTO t1 (...)
SELECT ... FROM t1

Where t1 is a table with a primary key that is auto increment.

You sometimes get duplicate key on key1 which is the auto incremented column...

The problem is intermitent, but yet easily reproducable if not every time. We can ONLY reproduce it doing this within an sp.

How to repeat:
CREATE TABLE `fl_weekly_neg` (
  `p_id` int(10) unsigned NOT NULL auto_increment,
  `event_id` int(10) unsigned NOT NULL,
  `cal_week_day` enum('1','2','3','4','5','6','7') NOT NULL,
  `event_time` time NOT NULL,
  PRIMARY KEY  (`p_id`),
  UNIQUE KEY `fl_week_neg_index1929` (`event_id`,`cal_week_day`,`event_time`),
  CONSTRAINT `fl_weekly_neg_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `fl_event` (`event_id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE PROCEDURE copy_ev_plan (IN xnew_id INT, IN xold_id INT)
BEGIN
    INSERT INTO fl_weekly_neg (event_id, cal_week_day, event_time)
    SELECT xnew_id, cal_week_day, event_time
      FROM fl_weekly_neg
     WHERE event_id = xold_id;
END

call copy_ev_plan(11,13);

This is just an example, our procedures and tables and so on is rather big, but I think you all get the picture (or I hope) what is happening.

There is a couple of notes. It is within an SP, the insert into select is done on the same table and it has a primary key with auto increment.

Gustav, I was not be able to reproduce your bug. If you have any ideas how to reproduce it, pls dont hesitate to reopen this bugreport.

mysql> CREATE TABLE `fl_weekly_neg` (   `p_id` int(10) unsigned NOT NULL auto_increment,   `event_id` int(10) unsigned NOT NULL,   `cal_week_day` enum('1','2','3','4','5','6','7') NOT NULL,   `event_time` time NOT NULL,   PRIMARY KEY  (`p_id`),   UNIQUE KEY `fl_week_neg_index1929` (`event_id`,`cal_week_day`,`event_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> CREATE PROCEDURE copy_ev_plan (IN xnew_id INT, IN xold_id INT)
    -> BEGIN
    ->     INSERT INTO fl_weekly_neg (event_id, cal_week_day, event_time)
    ->     SELECT xnew_id, cal_week_day, event_time
    ->       FROM fl_weekly_neg
    ->      WHERE event_id = xold_id;
    -> END
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call copy_ev_plan(11,13);
Query OK, 0 rows affected (0.01 sec)

mysql> call copy_ev_plan(11,13);
Query OK, 0 rows affected (0.00 sec)

mysql> call copy_ev_plan(11,13);
Query OK, 0 rows affected (0.00 sec)

mysql> call copy_ev_plan(11,13);
Query OK, 0 rows affected (0.00 sec)

Well I have no problem reproducing the case here at my place but...
Although there is a rather huge difference between this test case and what I sent (I'm sorry). I was only showing that in principal so that someone would get the idea about what it was all about.

I really dont know what to send you, there is of course data in the tables and a lot of constraints and so on.... The sp itself that fails handles about 28 tables.... Similiar, but not equal tables...

I will add the sp to this case.

The problem sure is there and as I see it, its a rather serious thing, but...

Anyway I have worked around it, just by lifting the code from the sp and doing each insert one by one, table by table, then it works....

The exact same code, only not within the sp works!

I really do think someone should look into this case and I will be happy to assist even if I am not depending upon the fix myself anymore. But its up to you guys.

The problem for me to send you a reproducable case is all the constraints really. We have been two engineers here looking into this problem and well...

SP that causes duplicate on auto increment column.

Attachment: mysqlsp.txt (text/plain), 5.52 KiB.