php
PDO::lastInsertId returns 18446744073709551615 instead of -1
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
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.
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 https://github.com/php/php-src/issues/8746#issuecomment-1153000548 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
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
BIGINTPK.
ACK.
