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: -
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:
Status: Open Package: PDO MySQL
PHP Version: 7.2.10 OS: Ubuntu 18.04
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [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

Add a Patch

Pull Requests

Add a Pull Request

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.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Mon Sep 28 13:01:23 2020 UTC