php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #77210 PDO::lastInsertId returns 0
Submitted: 2018-11-27 15:00 UTC Modified: 2020-09-02 10:08 UTC
Votes:2
Avg. Score:4.5 ± 0.5
Reproduced:2 of 2 (100.0%)
Same Version:1 (50.0%)
Same OS:1 (50.0%)
From: fcools at digilive dot nl Assigned: cmb (profile)
Status: Closed Package: PDO MySQL
PHP Version: 7.2.12 OS: MS Windows 8 v6.3 build 9600
Private report: No CVE-ID: None
 [2018-11-27 15:00 UTC] 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.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2018-11-27 15:04 UTC] danack@php.net
-Status: Open +Status: Feedback
 [2018-11-27 15:04 UTC] 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 18:04 UTC] fcools at digilive dot nl
-Status: Feedback +Status: Open
 [2018-11-27 18:04 UTC] 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.
 [2020-09-01 12:10 UTC] cmb@php.net
-Status: Open +Status: Feedback -Assigned To: +Assigned To: cmb
 [2020-09-01 12:10 UTC] 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>
 [2020-09-02 07:16 UTC] fcools at digilive dot nl
-Status: Feedback +Status: Assigned
 [2020-09-02 07:16 UTC] fcools at digilive dot nl
Unfortunately for this bug, I've switched to more recent versions of OS, php and mariadb.
 [2020-09-02 10:08 UTC] cmb@php.net
-Status: Assigned +Status: Closed
 [2020-09-02 10:08 UTC] cmb@php.net
Okay, so I assume that issue has been resolved.
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Wed Sep 30 16:01:24 2020 UTC