#45834 [Com]: INSERT INTO t VALUES (...) ON DUPLICATE KEY UPDATE ... and mysql_insert_id()

ID: 45834 Comment by: chuck dot joga at windriver dot com Reported By: swadlern at op dot pl Status: No Feedback Bug Type: MySQL related Operating System: Mandriva Linux 2008.1 x86_64 PHP Version: 5.2.6 New Comment: 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. Previous Comments: ------------------------------------------------------------------------ [2008-09-14 01:00:01] 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". ------------------------------------------------------------------------ [2008-09-06 15:55:36] jani@php.net After running the script, what is the output of 'select * from test' ? ------------------------------------------------------------------------ [2008-08-16 10:53:38] 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 ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=45834&edit=1