MySQL Bugs: #21616: LAST_INSERT

Bug #21616	LAST_INSERT_ID() Wrong results
Submitted:	14 Aug 2006 10:32	Modified:	14 Sep 2006 0:15
Reporter:	Vadim Gukhman
Status:	Duplicate
Category:	Server	Severity:	S3 (Non-critical)
Version:	5.0.24	OS:	Microsoft Windows (WindowsXP)
Assigned to:		Target Version:

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*/

sorry 5.0.22

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.

sveta, check the results of this testcase. looks wrong to me :)

Attachment: testcase.sql (text/x-delimtext), 863 bytes.

second last_insert_id call in the sp seemed to return previous calls value.

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`();

This bug is a duplicate of bug #21726.

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