php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #80908 pdo_mysql function lastInsertId() return wrong.
Submitted: 2021-03-26 07:10 UTC Modified: 2021-04-16 07:51 UTC
Votes:1
Avg. Score:3.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: atlanticfeng at icloud dot com Assigned:
Status: Closed Package: PDO MySQL
PHP Version: master-Git-2021-03-26 (Git) OS: Ubuntu 18.04
Private report: No CVE-ID: None
 [2021-03-26 07:10 UTC] atlanticfeng at icloud dot com
Description:
------------
- Latest or earlier version has this problem.
- My tests version is PHP-master-Git-2021-03-26(Git).
- In php, i use the pdo method: PDO::lastInsertId(), when MySQL table's auto_increment id bigger than int64, eg: 10376293541461622848, the method return a wrong result, is's like return '-8070450532247928768'.
- It's because the `static zend_string *pdo_mysql_last_insert_id(pdo_dbh_t *dbh, const zend_string *name)` use funtion `php_pdo_int64_to_str(int64_t i64)` to convert the mysql api funciton `last_insert_id()`, it should use `uint64_t`.
- In MySQL Reference Manual, `last_insert_id` With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit), With an argument, LAST_INSERT_ID() returns an unsigned integer. Is'a always greater than zero.

Test script:
---------------
<?php

try {
    // My localhost mysql server, version is 5.7.0
    $dbh = new PDO('mysql:127.0.0.1:33060;dbname=test', 'root', 'secret');
    // In table `foo`, have 2 columns, primary key `id` auto_increment=10376293541461622848, name is a varchar(20)
    $dbh->exec("insert into foo (`name`) values ('bar')");
    echo $dbh->lastInsertId() . PHP_EOL;
} catch( PDOExecption $e ) {
    print "Error!: " . $e->getMessage() . "</br>";
}

Expected result:
----------------
return "10376293541461622849"

Actual result:
--------------
return "-8070450532247928768"

Patches

mysql_insert_id_convert_to_uint64_t (last revision 2021-03-26 07:10 UTC by atlanticfeng at icloud dot com)

Add a Patch

Pull Requests

Pull requests:

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-04-16 07:29 UTC] twosee@php.net
-Status: Open +Status: Verified
 [2021-04-16 07:45 UTC] nikic@php.net
The following pull request has been associated:

Patch Name: :bug: Fixed Bug #80908 PDO::lastInsertId() return wrong.
On GitHub:  https://github.com/php/php-src/pull/6810
Patch:      https://github.com/php/php-src/pull/6810.patch
 [2021-04-16 07:46 UTC] twosee@php.net
That's an interesting problem, I even found that Navicat also has a similar problem.
Due to the MySQL protocol does not provide sign-bit information of lastInsertId, we can only support one type here, supporting unsigned type does look better to me. Although we can create a negative id in MySQL, it is interesting to note that we can not set AUTO_INCREMENT to a negative value, and I am sure that most people never use a negative value as an insert ID... (Of course, generally speaking,  I also think it is difficult to exceed INT64_MAX...)
BTW, open a pull request on Github is better (at least for me), and php_pdo_int64_to_str was replaced by zend_i64_to_str on the master branch [1].

[1] <https://github.com/php/php-src/commit/65a5c184d75127c1376df697748319cce32569cf>
 [2021-04-16 07:51 UTC] twosee@php.net
Sorry, I didn't notice the PR on GitHub... Thanks to Nikita for associate them up.
 [2021-04-26 09:50 UTC] git@php.net
Automatic comment on behalf of  (author) and nikic (committer)
Revision: https://github.com/php/php-src/commit/25dc931d83f96d760d047789321ce284b90dc85c
Log: Fixed bug #80908
 [2021-04-26 09:50 UTC] git@php.net
-Status: Verified +Status: Closed
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Sun Nov 28 09:03:14 2021 UTC