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

From: swadlern at op dot pl Operating system: Mandriva Linux 2008.1 x86_64 PHP version: 5.2.6 PHP Bug Type: MySQL related Bug description: INSERT INTO t VALUES (...) ON DUPLICATE KEY UPDATE ... and mysql_insert_id() 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 bug report at http://bugs.php.net/?id=45834&edit=1 -- Try a CVS snapshot (PHP 5.2): http://bugs.php.net/fix.php?id=45834&r=trysnapshot52 Try a CVS snapshot (PHP 5.3): http://bugs.php.net/fix.php?id=45834&r=trysnapshot53 Try a CVS snapshot (PHP 6.0): http://bugs.php.net/fix.php?id=45834&r=trysnapshot60 Fixed in CVS: http://bugs.php.net/fix.php?id=45834&r=fixedcvs Fixed in release: http://bugs.php.net/fix.php?id=45834&r=alreadyfixed Need backtrace: http://bugs.php.net/fix.php?id=45834&r=needtrace Need Reproduce Script: http://bugs.php.net/fix.php?id=45834&r=needscript Try newer version: http://bugs.php.net/fix.php?id=45834&r=oldversion Not developer issue: http://bugs.php.net/fix.php?id=45834&r=support Expected behavior: http://bugs.php.net/fix.php?id=45834&r=notwrong Not enough info: http://bugs.php.net/fix.php?id=45834&r=notenoughinfo Submitted twice: http://bugs.php.net/fix.php?id=45834&r=submittedtwice register_globals: http://bugs.php.net/fix.php?id=45834&r=globals PHP 4 support discontinued: http://bugs.php.net/fix.php?id=45834&r=php4 Daylight Savings: http://bugs.php.net/fix.php?id=45834&r=dst IIS Stability: http://bugs.php.net/fix.php?id=45834&r=isapi Install GNU Sed: http://bugs.php.net/fix.php?id=45834&r=gnused Floating point limitations: http://bugs.php.net/fix.php?id=45834&r=float No Zend Extensions: http://bugs.php.net/fix.php?id=45834&r=nozend MySQL Configuration Error: http://bugs.php.net/fix.php?id=45834&r=mysqlcfg