MySQL: LAST_INSERT_ID() returns 0
I've got this test table:
CREATE TABLE IF NOT EXISTS `test` (
`id` INT(10) AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4;
inserting using either of these three
INSERT INTO `test` (`id`) VALUES (NULL);
INSERT INTO `test` (`id`) VALUES (0);
INSERT INTO `test` () VALUES ();
and issuing
SELECT LAST_INSERT_ID();
but the query always results in 0
.
PHP's mysql_insert_id
and PDO::lastInsertId()
yield no result either.
I've been toying with this whole day and can't get it to work. Ideas?
Solution 1:
The problem seemed to be in MySQL's phpmyadmin config file PersistentConnections
set to FALSE
which resulted in a new CONNECTION_ID
every time a query was issued - therefore rendering SELECT LAST_INSERT_ID()
ineffective.
more info in the subsequent topic Every query creates a new CONNECTION_ID()
Also thanks dnagirl for help
Solution 2:
Just my 50 cents for this issue, I simply noticed that you won't get a LAST_INSERT_ID
greater than 0
if your table has no AUTO_INCREMENT
set to an index.
I wasted about half hour on this. Turns out I keep getting a LAST_INSERT_ID()
of 0
, which for this table is actually ok.
Solution 3:
you have to combine
INSERT INTO test (title) VALUES ('test');SELECT LAST_INSERT_ID();
Then you will get the last insert id
Solution 4:
I had the same issue. mysql_insert_id()
or LAST_INSERT_ID()
returned 0 when requested inside a PHP function with an INSERT query.
I sorted the issue by requesting the value of mysql_insert_id()
from a separate PHP function called right after the function that INSERT query.