PDO (mysql) unexpected lastInsertId when primary key is negative number

[jtuta]

Description

The following code:

<?php
$dbh = new PDO('mysql:host=mysql;dbname=database', 'user', 'pass');
    $dbh->prepare("CREATE TABLE `Datatable` (`id` int NOT NULL, `value` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
ALTER TABLE `Datatable` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `id` (`id`);
ALTER TABLE `Datatable` MODIFY `id` int NOT NULL AUTO_INCREMENT;")->execute();
    $stmt = $dbh->prepare("insert into `Datatable` (`value`, `id`) values (?, ?)");
    $stmt->execute( array('test', -1));
    var_dump(
        $dbh->lastInsertId(),
        $dbh->query("SELECT * FROM `Datatable`")->fetch()
    );

Resulted in this output:

string(20) "18446744073709551615" array(4) { ["id"]=> int(-1) [0]=> int(-1) ["value"]=> string(4) "test" [1]=> string(4) "test" }

But I expected this output instead:

string(20) "-1" array(4) { ["id"]=> int(-1) [0]=> int(-1) ["value"]=> string(4) "test" [1]=> string(4) "test" }

Example has been run in docker image:

• php:8.1-fpm
• mysql image (if relevant) mysql:8.0.27

I

PHP Version

PHP 8.1.7

Operating System

Ubuntu 22.04

[cmb69]

The MySQL API mysql_insert_id() returns an unsigned integer, and mysqlnd follows suite; that's what PHP is reporting as string.

[jtuta]

@cmb69 Hello, thank you for your response, I have run the same code on a docker image php:7.3-fpm and result is not equal as in php:8.1-fpm.

Result in php:7.3-fpm :

string(20) "-1" array(4) { ["id"]=> int(-1) [0]=> int(-1) ["value"]=> string(4) "test" [1]=> string(4) "test" }

Result in php:8.1-fpm :

string(20) "18446744073709551615" array(4) { ["id"]=> int(-1) [0]=> int(-1) ["value"]=> string(4) "test" [1]=> string(4) "test" }

I totally understand that mysql_insert_id() returns uint64_t (in case of 8.0), but different result in php 8.1 in comparison to 7.3 is IMHO unacceptable.

I discovered this thing because after php version bump my PHPUnit tests (which run numerous times before) failed.

Please reconsider this marking as a valid bug.

[KapitanOczywisty]

but different result in php 8.1 in comparison to 7.3 is IMHO unacceptable.

https://bugs.php.net/bug.php?id=80908
#6810
It was a bug and was fixed in 8.1. 7.3 was not supported anymore, thought at the time 7.4 and 8.0 could also be fixed - I guess they didn't want to introduce BC in point release.