Bug #77210 [NEW]: PDO::lastInsertId returns 0
From: fcools at digilive dot nl
Operating system: MS Windows 8 v6.3 build 9600
PHP version: 7.2.12
Package: PDO MySQL
Bug Type: Bug
Bug description:PDO::lastInsertId returns 0
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 bug report at https://bugs.php.net/bug.php?id=77210&edit=1
--
Try a snapshot (PHP 5.4): https://bugs.php.net/fix.php?id=77210&r=trysnapshot54
Try a snapshot (PHP 5.5): https://bugs.php.net/fix.php?id=77210&r=trysnapshot55
Try a snapshot (trunk): https://bugs.php.net/fix.php?id=77210&r=trysnapshottrunk
Fixed in SVN: https://bugs.php.net/fix.php?id=77210&r=fixed
Fixed in release: https://bugs.php.net/fix.php?id=77210&r=alreadyfixed
Need backtrace: https://bugs.php.net/fix.php?id=77210&r=needtrace
Need Reproduce Script: https://bugs.php.net/fix.php?id=77210&r=needscript
Try newer version: https://bugs.php.net/fix.php?id=77210&r=oldversion
Not developer issue: https://bugs.php.net/fix.php?id=77210&r=support
Expected behavior: https://bugs.php.net/fix.php?id=77210&r=notwrong
Not enough info: https://bugs.php.net/fix.php?id=77210&r=notenoughinfo
Submitted twice: https://bugs.php.net/fix.php?id=77210&r=submittedtwice
register_globals: https://bugs.php.net/fix.php?id=77210&r=globals
PHP 4 support discontinued: https://bugs.php.net/fix.php?id=77210&r=php4
Daylight Savings: https://bugs.php.net/fix.php?id=77210&r=dst
IIS Stability: https://bugs.php.net/fix.php?id=77210&r=isapi
Install GNU Sed: https://bugs.php.net/fix.php?id=77210&r=gnused
Floating point limitations: https://bugs.php.net/fix.php?id=77210&r=float
No Zend Extensions: https://bugs.php.net/fix.php?id=77210&r=nozend
MySQL Configuration Error: https://bugs.php.net/fix.php?id=77210&r=mysqlcfg
Thread (6 messages)
- fcools at digilive dot nl