PDO::lastInsertId returns 18446744073709551615 instead of -1

[mowangjuanzi]

Description

SQL to create max_id table:

CREATE TABLE `max_id` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The following code:

<?php
$pdo = new PDO("mysql:dbname=test;host=127.0.0.1", 'root', 'password');
$stmt = $pdo->prepare("INSERT INTO max_id (id, title) VALUES(?, ?)");
var_dump($stmt->execute([-1, "C"]));

Resulted in this output:

"18446744073709551615"

But I expected this output instead:

"-1"

I found that the problem may be in https://github.com/php/php-src/blob/master/ext/pdo_mysql/mysql_driver.c#L296

zend_u64_to_str does not recognize negative IDs.

PHP Version

8.1.11

Operating System

Ubuntu 22.04

[jhdxr]

well, although the value for PK can be negative, the return value of mysql_insert_id from mysql driver is unsigned (my_ulonglong or uint64_t, check https://dev.mysql.com/doc/c-api/8.0/en/mysql-insert-id.html). I suggest we may add a note to the doc, like what we do for BIGINT PK.

[mvorisek]

related https://bugs.mysql.com/bug.php?id=69228

[hormus]

Currently after the patch the expected result is a 64-bit unsigned integer. (From 0 to 2⁶⁴-1, 18446744073709551615)
But if mysql now indicates a special flag https://bugs.mysql.com/bug.php?id=69228 if a negative number a patch could be created, the choice depends on the maintainer
php/php-src#8746 (comment)
BIGINT PK note is for ext/mysql extension, for PDO::LastInsertId() and drivers mysql or mariadb it returns a string, which indicates that the value cannot be represented in the int type of php in some circumstances

[cmb69]

Indeed, this is a duplicate of php/php-src#8746.

I suggest we may add a note to the doc, like what we do for BIGINT PK.

ACK.