php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #76896 PDO::lastInsertId resets after SELECT
Submitted: 2018-09-17 14:43 UTC Modified: 2021-10-15 16:51 UTC
Votes:3
Avg. Score:3.3 ± 1.7
Reproduced:3 of 3 (100.0%)
Same Version:0 (0.0%)
Same OS:1 (33.3%)
From: symos at yahoo dot com Assigned: cmb (profile)
Status: Not a bug Package: PDO MySQL
PHP Version: 7.2.10 OS: Ubuntu 18.04
Private report: No CVE-ID: None
 [2018-09-17 14:43 UTC] symos at yahoo dot com
Description:
------------
When executing an INSERT query and then immediately running PDO::lastInsertId(), the value it returns is correct. But if a SELECT query is executed in between, lastInsertId returns 0. This did not happen in PHP 5.5.9.

If you run "SELECT LAST_INSERT_ID();" as a raw query (even through PDO) the result you get is correct. 

I can't be sure if this is a bug or was changed intentionally, but I would argue it's not correct for PDO's lastInsertId to return a different value than MySQL's LAST_INSERT_ID().

Test script:
---------------
<?php

$username = "username";
$password = "password";    

$pdo = new PDO("mysql:host=127.0.0.1;port=3306;dbname=test_db", $username, $password);
    
$pdo->query("CREATE TABLE IF NOT EXISTS `test_table` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `random_field` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;");

$st = $pdo->query("INSERT INTO test_table(random_field) VALUES (" . rand(1, 100000) . ")");
    
if($st){
        echo "last_insert_id: " . $pdo->lastInsertId() . "\n";
        $pdo->query("SELECT 1 + 1");
        echo "last_insert_id: " . $pdo->lastInsertId() . "\n";
}
else {
        var_dump($pdo->errorInfo());
}

Expected result:
----------------
Both values should reflect ID of last inserted record

Actual result:
--------------
Second value is zero

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-09-18 13:29 UTC] james at jamesking56 dot uk
Reproduced in Ubuntu 16.04.5 LTS in the following PHP versions:

5.6.37
7.0.32
7.1.20
7.2.9
 [2018-10-02 20:53 UTC] ryan at mrrsm dot com
I am seeing the same issue when running your test using the mysqli commands.  This leads me to think it may be an issue with mysqlnd.
 [2018-10-03 00:28 UTC] mitchconkin at gmail dot com
From what I understand this is expected behavior, so mysqlnd is backwards compatible with libmysql. The core developers purposefully reset the last insert ID on non INSERT statements to emulate the same behavior as the old libmysql library.
 [2019-02-12 13:16 UTC] holysatan84 at gmail dot com
If the mysql query "select last_insert_id()" is fired even after the select query, the correct ID is retrieved. 

The same when fired using a PDO::lastInsertId(), returns a "0".

This signifies there is the PDO::lastInsertId() is not inline with the mysql statement SELECT LAST_INSERT_ID();" hence its a valid bug
 [2019-09-13 21:25 UTC] php at pimpin dot ninja
I'm seeing the same behavior when doing a statement using ON DUPLICATE KEY UPDATE:


php 7.2.10

'INSERT INTO BLAH (field) values ('blah') ON DUPLICATE KEY UPDATE id = last_insert_id(id), field = 'blah2';

Passing the ID to last_insert_id() tells mysql the value to return in the next last_insert_id() call.  The issue here is the PDO gets a 'number of rows affected' response of 1 if the row was created or 2 if the row was updated.
 [2021-10-15 16:51 UTC] cmb@php.net
-Status: Open +Status: Not a bug -Assigned To: +Assigned To: cmb
 [2021-10-15 16:51 UTC] cmb@php.net
> I am seeing the same issue when running your test using the
> mysqli commands.

Right.

> From what I understand this is expected behavior, so mysqlnd is
> backwards compatible with libmysql.

That.  From the MySQL docs[1]:

| mysql_insert_id() returns 0 if the previous statement does not
| use an AUTO_INCREMENT value. If you must save the value for later,
| be sure to call mysql_insert_id() immediately after the statement
| that generates the value.

It is still possible to build against libmysql-client, so having
different behavior for either, would be a serious issue.

| If the mysql query "select last_insert_id()" is fired even after
| the select query, the correct ID is retrieved.

Right.  From the MySQL docs[1]:

| LAST_INSERT_ID() is not reset between statements because the
| value of that function is maintained in the server.

If anybody wants this to be changed, report that upstream (and
don't forget about all the forks).

[1] <https://dev.mysql.com/doc/c-api/8.0/en/mysql-insert-id.html>
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sun Oct 13 01:01:28 2024 UTC