Bug #15023 incorrect auto_increment value inside a stored procedure
Submitted: 17 Nov 2005 15:16 Modified: 1 Dec 2005 12:04
Reporter: Arnold Greving Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0.15/5.0.17 BK OS:Linux (Debian sid)
Assigned to: Assigned Account CPU Architecture:Any

[17 Nov 2005 15:16] Arnold Greving 
Description:
When your insert a record into table test1 and then a few in table test2 and then another record into test2 the auto_increment value from test2 is used for test1 if your next insert is into test1.

How to repeat:
drop table if exists test1;
create table test1 (id int primary key auto_increment);

drop table if exists test2;
create table test2 (id int primary key auto_increment);

drop procedure if exists foo;
delimiter //
create procedure foo()
begin
    insert into test1 set id=null;  /* id = 1 */

    insert into test2 set id=null;
    insert into test2 set id=null;
    insert into test2 set id=null;
    insert into test2 set id=null;
    insert into test2 set id=null;
    insert into test2 select null as id; /* This goes wrong */

    insert into test1 set id=null; /* id should be 2 but is 7 */
    insert into test1 set id=null;

    select * from test1;

end
//
delimiter ;

call foo();
[23 Nov 2005 18:17] Paul Freeman 
I'm having this exact problem too, on Linux 2.6.13, MySQL 5.0.15 source, with
InnoDB engine
[1 Dec 2005 12:04] Ramil Kalimullin 
Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

See bug #14304: auto_increment field incorrect set from within stored procedure (insert select).