MySQL Bugs: #60512: mysql_insert_id() reports wrong value

Bug #60512	mysql_insert_id() reports wrong value
Submitted:	17 Mar 2011 13:06	Modified:	17 Mar 2011 20:40
Reporter:	Guenther Schmidt	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
From the documentation:
"mysql_insert_id() returns 0 if the previous statement does not use an AUTO_INCREMENT value. If you need to save the value for later, be sure to call mysql_insert_id() immediately after the statement that generates the value."

and

"The reason for the differences between LAST_INSERT_ID() and mysql_insert_id() is that LAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide more exact information about what happens to the AUTO_INCREMENT column."

How to repeat:
I am using the following test written in PHP which uses libmysql, and not libmysql.

<?php
$link = new mysqli('127.0.0.1', 'root', 'root', 'test');

$link->query("DROP TABLE IF EXISTS `abcde`");
$link->query("CREATE TABLE abcde (`id` int(11) NOT NULL AUTO_INCREMENT, ".
			 "`value` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`));");
$link->query("INSERT INTO abcde SET `id` = 0");

$res = $link->query("SHOW WARNINGS");
echo mysqli_insert_id($link) . "\n";
echo $link->query("SELECT LAST_INSERT_ID() AS `id`")->fetch_object()->id . "\n";
?>

The output is:
1  <-- mysqli_insert_id() return 0 after mysqli has executed SHOW WARNINGS
1

According to the documentation after SHOW WARNINGS mysql_insert_id() should return 0, but it returns 1. The value returned by the SELECT LAST_INSERT_ID() query is correct.

Suggested fix:
Fix it :)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id:

For MySQL 5.1.12 and later, LAST_INSERT_ID() (with no argument) returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT  statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted. 

So this is not a bug as value 1 was just not changed after SHOW WARNINGS run