Bug #76896 [Opn->Nab]: PDO::lastInsertId resets after SELECT
Edit report at https://bugs.php.net/bug.php?id=76896&edit=1
ID: 76896
Updated by: cmb@php.net
Reported by: symos at yahoo dot com
Summary: PDO::lastInsertId resets after SELECT
-Status: Open
+Status: Not a bug
Type: Bug
Package: PDO MySQL
Operating System: Ubuntu 18.04
PHP Version: 7.2.10
-Assigned To:
+Assigned To: cmb
Block user comment: N
Private report: N
New Comment:
> I am seeing the same issue when running your test using the
> mysqli commands.
Right.
> From what I understand this is expected behavior, so mysqlnd is
> backwards compatible with libmysql.
That. From the MySQL docs[1]:
| mysql_insert_id() returns 0 if the previous statement does not
| use an AUTO_INCREMENT value. If you must save the value for later,
| be sure to call mysql_insert_id() immediately after the statement
| that generates the value.
It is still possible to build against libmysql-client, so having
different behavior for either, would be a serious issue.
| If the mysql query "select last_insert_id()" is fired even after
| the select query, the correct ID is retrieved.
Right. From the MySQL docs[1]:
| LAST_INSERT_ID() is not reset between statements because the
| value of that function is maintained in the server.
If anybody wants this to be changed, report that upstream (and
don't forget about all the forks).
[1] <https://dev.mysql.com/doc/c-api/8.0/en/mysql-insert-id.html>
Previous Comments:
------------------------------------------------------------------------
[2019-09-13 21:25:38] php at pimpin dot ninja
I'm seeing the same behavior when doing a statement using ON DUPLICATE KEY UPDATE:
php 7.2.10
'INSERT INTO BLAH (field) values ('blah') ON DUPLICATE KEY UPDATE id =
last_insert_id(id), field = 'blah2';
Passing the ID to last_insert_id() tells mysql the value to return in the next last_insert_id()
call. The issue here is the PDO gets a 'number of rows affected' response of 1 if the row
was created or 2 if the row was updated.
------------------------------------------------------------------------
[2019-02-12 13:16:30] holysatan84 at gmail dot com
If the mysql query "select last_insert_id()" is fired even after the select query, the
correct ID is retrieved.
The same when fired using a PDO::lastInsertId(), returns a "0".
This signifies there is the PDO::lastInsertId() is not inline with the mysql statement SELECT
LAST_INSERT_ID();" hence its a valid bug
------------------------------------------------------------------------
[2018-10-03 00:28:11] mitchconkin at gmail dot com
From what I understand this is expected behavior, so mysqlnd is backwards compatible with libmysql.
The core developers purposefully reset the last insert ID on non INSERT statements to emulate the
same behavior as the old libmysql library.
------------------------------------------------------------------------
[2018-10-02 20:53:48] ryan at mrrsm dot com
I am seeing the same issue when running your test using the mysqli commands. This leads me to think
it may be an issue with mysqlnd.
------------------------------------------------------------------------
[2018-09-18 13:29:49] james at jamesking56 dot uk
Reproduced in Ubuntu 16.04.5 LTS in the following PHP versions:
5.6.37
7.0.32
7.1.20
7.2.9
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
https://bugs.php.net/bug.php?id=76896
--
Edit this bug report at https://bugs.php.net/bug.php?id=76896&edit=1
Thread (7 messages)