php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #45834 INSERT INTO t VALUES (...) ON DUPLICATE KEY UPDATE ... and mysql_insert_id()
Submitted: 2008-08-16 10:53 UTC Modified: 2008-09-14 01:00 UTC
Votes:3
Avg. Score:4.3 ± 0.9
Reproduced:2 of 3 (66.7%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: swadlern at op dot pl Assigned:
Status: No Feedback Package: MySQL related
PHP Version: 5.2.6 OS: Mandriva Linux 2008.1 x86_64
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2008-08-16 10:53 UTC] swadlern at op dot pl
Description:
------------
I execute the following query:

INSERT INTO test VALUES (NULL,'key','value') ON DUPLICATE KEY UPDATE v='value';

key exists and value of v column is the same as new value in UPDATE clause.
I call $id=mysql_insert_id(); and expect that $id is id of existing row (id is INT NOT NULL PRIMARY KEY AUTO_INCREMENT column) but $id contains another value.

MySQL version is 5.0.51a

Reproduce code:
---------------
<?php
/* MySQL table definition
CREATE TABLE test
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    k VARCHAR(10) NOT NULL UNIQUE,
    v VARCHAR(10) NOT NULL
);
*/

$db_host='localhost'; //database host
$db_user=''; //database user
$db_pass=''; //database password
$db_name='test'; //database name

mysql_connect($db_host,$db_user,$db_pass); //connect to database
mysql_query("USE $db_name"); //select database
mysql_query('TRUNCATE test'); //clear test table
mysql_query('INSERT INTO test VALUES (NULL,\'key\',\'value\')');
mysql_query('INSERT INTO test VALUES (NULL,\'key\',\'value\') ON DUPLICATE KEY UPDATE v=\'value\'');
$id=mysql_insert_id();
mysql_close();
echo("Expected id is 1, id is $id\n");
?>


Expected result:
----------------
I expect $id to be 1

Actual result:
--------------
$id is 2

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2008-09-06 15:55 UTC] jani@php.net
After running the script, what is the output of 'select * from test' ? 
 [2008-09-14 01:00 UTC] php-bugs at lists dot php dot net
No feedback was provided for this bug for over a week, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
 [2009-09-01 14:14 UTC] chuck dot joga at windriver dot com
Occurs when a duplicate key exists.  The next autoincrement number is returned instead of the id of the row updated.  No row is inserted and the next autoincrement number is not used.

I'm still trying to find a workaround.
 
PHP Copyright © 2001-2022 The PHP Group
All rights reserved.
Last updated: Fri Oct 07 11:05:53 2022 UTC