MySQL: LAST_INSERT_ID() returns 0

Question

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?

does this happen when you run the queries directly from a mySQL command line? If it's only happening when you run the queries through PHP, it might be a connection issue. — dnagirl, Dec 14 '12 at 14:28
I am running it directly in the command line. I have the same MySQL version as you do. Do I have to issue the commands in one block as Zubin suggests? — dwelle, Dec 14 '12 at 14:30
the `;` between them makes them separates them. Putting them together on one line makes no difference. — dnagirl, Dec 14 '12 at 14:32
That's what I thought but as Zubin suggested, when I put then in one block `LAST_INSERT_ID()` work, but when they're separated, it still results in `0`. No idea what's going on. — dwelle, Dec 14 '12 at 14:37
here's the ref for last_insert_id() http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id — dnagirl, Dec 14 '12 at 14:37
I'm wondering if there is something in your config that has turned off insert id tracking. — dnagirl, Dec 14 '12 at 14:39
How to find out/set it on? Though I doubt that's the case. I've tried it not only on local, but on remote server as well. With no success. — dwelle, Dec 14 '12 at 14:44
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/21145/discussion-between-dnagirl-and-davidl) — dnagirl, Dec 14 '12 at 14:45

score 28 · Accepted Answer · edited May 23 '17 at 11:46

28

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

edited May 23 '17 at 11:46

Community

1
1

answered Dec 14 '12 at 16:43

dwelle

6,894
2
46
70

score 21 · Answer 2 · edited Mar 20 '16 at 03:02

21

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.

edited Mar 20 '16 at 03:02

Rasclatt

12,498
3
25
33

answered Sep 08 '14 at 19:29

Ted

3,805
14
56
98

score 12 · Answer 3 · answered Dec 14 '12 at 14:13

12

you have to combine

INSERT INTO test (title) VALUES ('test');SELECT LAST_INSERT_ID();

Then you will get the last insert id

answered Dec 14 '12 at 14:13

user7282

5,106
9
41
72

I see. But is then possible to set up a `MySQL` trigger that fires after table test `INSERT` and creates a row in a junction table using a `last_insert_id()` from the test table? – dwelle Dec 14 '12 at 14:19
I think it is also possible, please have a try – user7282 Dec 14 '12 at 14:20
Trigger `INSERT INTO tasks_tags (id_task,id_tag) VALUES (LAST_INSERT_ID(),'1')` fails at FK because the `LAST_INSERT_ID()` in the trigger already results in `0` – dwelle Dec 14 '12 at 14:23
Nice idea, but it doesn't always work out of the box: http://stackoverflow.com/a/3632320/997940 , http://stackoverflow.com/a/28527704/997940 – Yoav Feuerstein Jan 15 '17 at 12:37

score 1 · Answer 4 · answered Apr 25 '16 at 21:28

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.

score 0 · Answer 5 · answered Oct 03 '19 at 12:44

I Had the same issues but I have resolved this by creating a transaction.

$db->begin();
$sql = "INSERT INTO 'test' VALUES('test')";

if ($db->query($sql))
{
    $id = $db->last_insert_id('test');
    $db->commit();
    return $id;
}
else{
    $db->rollback();
    return -1;
}

I had tried

return $db->last_insert_id('test');

after the "commit" but that always returned "0"

hope that can help you

score -2 · Answer 6 · edited Jul 22 '14 at 11:38

it work perfectly...try it...

    $result = mysql_query("INSERT INTO `test` (`title`) VALUES ('test')");
    if ($result) {
        $id = mysql_insert_id(); // last inserted id
        $result = mysql_query("SELECT * FROM tablename WHERE id = $id") or die(mysql_error());
        // return user details
        if (mysql_num_rows($result) > 0) {
            return mysql_fetch_array($result);
        } else {
            return false;
        }
    } else {
        return false;
    }

MySQL: LAST_INSERT_ID() returns 0

6 Answers6

Linked