Bug #62320 InnoDB and MyISAM show different LAST_INSERT_ID()/AUTO_INCREMENT behaviour
Submitted: 1 Sep 2011 11:23 Modified: 1 Sep 2011 21:19
Reporter: Ulf Wendel Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.5.15 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[1 Sep 2011 11:23] Ulf Wendel
Description:
MySQL 5.5. changes the default table engine from MyISAM to InnoDB. This breaks the PHP MySQL driver test ext/mysqli/tests/mysqli_last_insert_id.phpt which compares SQL LAST_INSERT_ID() vs API last_insert_id() API call. 

The test finds that MyISAM and InnoDB behave differently for the below SQL sequence. The SQL sequence will pass with MyISAM (because it gets the AUTO_INCREMENT value "right") and it will fail with InnoDB. 

Issue observed with MySQL 5.1.60, 5.5.15, 5.5.17, 5.6.4-m5. 

How to repeat:
Compare the results of the following SQL sequence when using MyISAM respectively InnoDB table engine for table "test". Modify the CREATE TABLE statement accordingly prior to running. 

USE test;
DROP TABLE IF EXISTS test;
CREATE TABLE test (id INT auto_increment, label varchar(10) NOT NULL, PRIMARY KEY (id))
INSERT INTO test (id, label) VALUES (null, 'a');
INSERT INTO test (id, label) VALUES (null, null);
select * from test;
show create table test;
select LAST_INSERT_ID() AS _id;
UPDATE test SET id=LAST_INSERT_ID(id+1);
select * from test;
show create table test;
select LAST_INSERT_ID() AS _id;
INSERT INTO test(id, label) VALUES (LAST_INSERT_ID(id + 1), 'b');
select * from test;
show create table test;
select LAST_INSERT_ID() AS _id;
INSERT INTO test(label) VALUES ('c');

Suggested fix:
Align MyISAM and InnoDB behaviour.
[1 Sep 2011 21:19] Miguel Solorzano
Thank you for the bug report.