|  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: -
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
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
Block user comment
Status: Assign to:
Bug Type:
From: symos at yahoo dot com
New email:
PHP Version: OS:


 [2018-09-17 14:43 UTC] symos at yahoo dot com
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:

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

$pdo = new PDO("mysql:host=;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`)

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

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

Actual result:
Second value is zero


Add a Patch

Pull Requests

Add a Pull Request


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:

 [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-2021 The PHP Group
All rights reserved.
Last updated: Sat Sep 25 19:03:38 2021 UTC