Bug #76896 [Com]: PDO::lastInsertId resets after SELECT
Edit report at https://bugs.php.net/bug.php?id=76896&edit=1
ID: 76896
Comment by: php at pimpin dot ninja
Reported by: symos at yahoo dot com
Summary: PDO::lastInsertId resets after SELECT
Status: Open
Type: Bug
Package: PDO MySQL
Operating System: Ubuntu 18.04
PHP Version: 7.2.10
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2019-02-12 13:16:30] 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
------------------------------------------------------------------------
[2018-10-03 00:28:11] 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.
------------------------------------------------------------------------
[2018-10-02 20:53:48] 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-09-18 13:29:49] 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-09-17 14:43:24] 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
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=76896&edit=1
Thread (7 messages)