Bug #77210 [Opn->Fbk]: PDO::lastInsertId returns 0

From: Date: Tue, 27 Nov 2018 15:04:19 +0000
Subject: Bug #77210 [Opn->Fbk]: PDO::lastInsertId returns 0
References: 1  Groups: php.bugs 
Request: Send a blank email to php-bugs+get-218167@lists.php.net to get a copy of this message 
Edit report at https://bugs.php.net/bug.php?id=77210&edit=1

 ID:                 77210
 Updated by:         danack@php.net
 Reported by:        fcools at digilive dot nl
 Summary:            PDO::lastInsertId returns 0
-Status:             Open
+Status:             Feedback
 Type:               Bug
 Package:            PDO MySQL
 Operating System:   MS Windows 8 v6.3 build 9600
 PHP Version:        7.2.12
 Block user comment: N
 Private report:     N

 New Comment:

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.


Previous Comments:
------------------------------------------------------------------------
[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)

« previous php.bugs (#218167) next »