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.