Bug #21616 LAST_INSERT_ID() Wrong results
Submitted: 14 Aug 2006 8:32 Modified: 13 Sep 2006 22:15
Reporter: Vadim Gukhman Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.24 OS:Windows (WindowsXP)
Assigned to: CPU Architecture:Any

[14 Aug 2006 8:32] Vadim Gukhman 
Description:
I'm 2 times work with INSERT in one stored procedure.

And it returns result only from first insert.

How to repeat:
    INSERT INTO
        `vopros_users`(`user_hash`, `user_name`, `user_email`, `user_ip`, `dt`)
    VALUES
        (hash_for_user,nick,email,ip,now());

    SET user_id = LAST_INSERT_ID();
/*all ok correct value*/

INSERT INTO
    `vopros_questions` (`question`,`question_ip`,`dt`,`to_user_id`)
VALUES
    (question, ip, now(), user_id);

SELECT hash_for_user as user_hash, LAST_INSERT_ID() as question_id;

/*returns wrong_value*/
[14 Aug 2006 9:06] Vadim Gukhman 
sorry 5.0.22
[14 Aug 2006 9:20] Sveta Smirnova 
Thank you for the report.

>sorry 5.0.22

Does it mean you can not repeat it using last stable 5.0.24 version? If you can repeat bug using last 5.0.24 version, please, reopen the bug and provide output of SHOW CREATE TABLE vopros_users and SHOW CREATE TABLE vopros_questions.
[3 Sep 2006 16:24] MySQL Verification Team 
sveta, check the results of this testcase. looks wrong to me :)

Attachment: testcase.sql (text/x-delimtext), 863 bytes.
[3 Sep 2006 16:25] MySQL Verification Team 
second last_insert_id call in the sp seemed to return previous calls value.
[4 Sep 2006 7:47] Sveta Smirnova 
Thank you, Shane, for the tescase: it is good testcase for this bug.

Verified using Shane Bester's test case.

Or even:

drop table if exists `t1`;
drop table if exists `t2`;
create table `t1` (id int not null auto_increment primary key);
create table `t2` (id int not null auto_increment primary key);
insert into `t2` values (),(),(),();
insert into `t1` values ();
select last_insert_id();
insert into `t2` values ();
select last_insert_id();

delimiter //
create procedure `p1`()
begin
	insert into `t1` values ();
	select last_insert_id();
	insert into `t2` values ();
	select last_insert_id();
end;//
delimiter ;
call `p1`();
[13 Sep 2006 22:15] Kolbe Kegel 
This bug is a duplicate of bug #21726.

I'm closing this bug, as the newer bug has more info in it.