#45834 [Opn->Fbk]: INSERT INTO t VALUES (...) ON DUPLICATE KEY UPDATE ... and mysql_insert_id()

ID: 45834 Updated by: jani@php.net Reported By: swadlern at op dot pl -Status: Open +Status: Feedback Bug Type: MySQL related Operating System: Mandriva Linux 2008.1 x86_64 PHP Version: 5.2.6 New Comment: After running the script, what is the output of 'select * from test' ? Previous Comments: ------------------------------------------------------------------------ [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