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.
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.