Bug #77210 [Asn->Csd]: PDO::lastInsertId returns 0
Edit report at https://bugs.php.net/bug.php?id=77210&edit=1
ID: 77210
Updated by: cmb@php.net
Reported by: fcools at digilive dot nl
Summary: PDO::lastInsertId returns 0
-Status: Assigned
+Status: Closed
Type: Bug
Package: PDO MySQL
Operating System: MS Windows 8 v6.3 build 9600
PHP Version: 7.2.12
Assigned To: cmb
Block user comment: N
Private report: N
New Comment:
Okay, so I assume that issue has been resolved.
Previous Comments:
------------------------------------------------------------------------
[2020-09-02 07:16:22] fcools at digilive dot nl
Unfortunately for this bug, I've switched to more recent versions of OS, php and mariadb.
------------------------------------------------------------------------
[2020-09-01 12:10:10] cmb@php.net
The given test script from the comment works as expected for me
(Windows 10, MySQL 5.6). Can you still reproduce the reported
behavior with any of the actively supported PHP versions[1]?
[1] <https://www.php.net/supported-versions.php>
------------------------------------------------------------------------
[2018-11-27 18:04:09] fcools at digilive dot nl
I'm happy to correct the code. My apologies.
Errors were created while adapting the code for this report.
I don't know how edit the original code as I don't see another option than adding a
comment.
Hopefully I'm doing the right thing by adding the corrected code as a comment (without any
errors or unknown references.
Test script:
---------------
<?php
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';
$options = array(
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
\PDO::ATTR_EMULATE_PREPARES => false,
\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4',
\PDO::MYSQL_ATTR_LOCAL_INFILE => true
);
try {
$dbh = new PDO($dsn, $user, $password, $options);
} catch (\PDOException $e) {
exit('Connection failed: ' . $e->getMessage());
}
$statement = <<<SQL
UPDATE my_table
SET
my_name = :my_name,
my_id = LAST_INSERT_ID(my_id)
WHERE my_id = :my_id;
SQL;
try {
$sth = $dbh->prepare($statement);
$sth->bindValue(':my_name', 'Foo');
$sth->bindValue(':my_id', 12, PDO::PARAM_INT);
$sth->execute();
if ($dbh->lastInsertId() == 0) {
echo 'Id not found!';
} else {
echo 'Row Successfully updated.';
}
} catch (\PDOException $e) {
echo 'Transaction failed!';
}
Expected result:
----------------
PDO::lastInsertId returns the value of expr of LAST_INSERT_ID(expr).
In case of my Test Script: 12
'Row Successfully updated.' is echoed.
Actual result:
--------------
PDO::lastInsertId returns 0.
'Id not found!' is echoed.
------------------------------------------------------------------------
[2018-11-27 15:04:19] danack@php.net
Please can you provide a working reproduction test script. Your code has reference to $this when not
in a class, and has missing semi-colons.
------------------------------------------------------------------------
[2018-11-27 15:00:44] fcools at digilive dot nl
Description:
------------
Considering the attached Test Script...
The my_id = LAST_INSERT_ID(my_id) part in my SET clause, sets the value of Maria DB's
LAST_INSERT_ID() to the value of my_id of the updated row.
Executing SELECT LAST_INSERT_ID(); in my sql client confirms the value is set (result = 12).
In php I use PDO::lastInsertId to get this value and if it's 0, a matching row doesn't
exist. This way I can make a difference between a 'my_id doesn't exist' error and a
silent UPDATE of nothing.
This works fine in PHP 5.6.23/MariaDB 10.1.13, but now I'm at PHP 7.2.11 or 7.2.12/MariaDB
10.1.36 and the returnvalue of PDO::lastInsertId remains zero while the row is updated indeed.
As per mysql/MariaDB manual:
If one gives an argument to LAST_INSERT_ID(), then it will return the value of the expression and
the next call to LAST_INSERT_ID() will return the same value.
and
The mysql_insert_id() C API function can also be used to get the value. See Section 28.7.7.38,
âmysql_insert_id()â.
I can confirm the code still works with PHP v7.1.8
Test script:
---------------
<?php
$statement = <<<SQL
UPDATE my_table
SET
my_name = :my_name,
my_id = LAST_INSERT_ID(my_id)
WHERE my_id = :my_id;
SQL;
try {
$sth = $this->dbh->prepare($statement);
$sth->bindValue(':my_name', 'Foo');
$sth->bindValue(':my_id', 12, PDO::PARAM_INT);
$sth->execute();
if ($this->dbh->lastInsertId() == 0) {
echo 'Id not found!';
} else {
echo 'Row Successfully updated.'
} catch (\PDOException $e) {
echo 'Transaction failed!';
}
Expected result:
----------------
PDO::lastInsertId returns the value of expr of LAST_INSERT_ID(expr).
In case of my Test Script: 12
'Row Successfully updated.' is echoed.
Actual result:
--------------
PDO::lastInsertId returns 0.
'Id not found!' is echoed.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=77210&edit=1
Thread (6 messages)