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: 2018-11-27 18:04 UTC
From: fcools at digilive dot nl Assigned:
Status: Open Package: PDO MySQL
PHP Version: 7.2.12 OS: MS Windows 8 v6.3 build 9600
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: fcools at digilive dot nl
New email:
PHP Version: OS:

 

 [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.
 
PHP Copyright © 2001-2018 The PHP Group
All rights reserved.
Last updated: Tue Dec 11 22:01:26 2018 UTC