Bug #1582 mysql_insert_id differs from manual
Submitted: 16 Oct 2003 21:58 Modified: 3 Feb 2004 11:46
Reporter: L. Blunt Jackson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:4.0.15 OS:Linux (linux / x86)
Assigned to: Paul DuBois CPU Architecture:Any

[16 Oct 2003 21:58] L. Blunt Jackson
Description:
Not sure if the software is wrong or the manual.
Note that the manual disagrees with itself:

function documentation:
http://www.mysql.com/doc/en/mysql_insert_id.html

additional, contradictory documentation:
http://www.mysql.com/doc/en/Getting_unique_ID.html

Actual behavior:

A) api docs claim any subsequent query will result in
   mysql_insert_id returning 0, but selects or
   updates to tables without an auto_increment
   column do not reset to 0.

B) additional docs claim that updates to a table
   that do not invoke auto_increment behavior will
   not resert mysql_insert_id's value, but, in fact,
   they do.

B.1: updating a field in a table that has an auto_increment
     column, even if that column is not updated, will
     reset mysql_insert_id to 0.

B.2: inserting a row into a table that has an auto_increment
     column, even if the value of that column is specified,
     will reset mysql_insert_id to the specified value.

See examples.

How to repeat:
create table test (test_id int not null auto_increment, test_text varchar(10));

insert into test (test_text) values ('Hello.');

> fetch value with mysql_insert_id returns '1'.

insert into test (test_id, test_text) values ('40', 'Hi 40');

> fetch value with mysql_insert_id: expected '1', received '40';

update test set test_text = 'Yo!' where test_id = '1';

> fetch value with mysql_insert_id: expectations unclear due to
  contradictory documentation. actual return value: 0.

Suggested fix:

make behavior consistent with documentation;
make documentation consistent with itself;

most convenient would be for mysql_insert_id to retail
the last id generated by invocation of auto_increment
regardless of subsequent queries that do other things.
[17 Oct 2003 3:49] Sergei Golubchik
It's a documentation issue.

A) I wasn't able to repeat this. select and updates that did not affect auto_increment column always returned 0 in mysql_insert_id() for me.

B) No, manual specifies that updates to a table that do not generate auto_increment value (not "invokes auto_increment behaviour") will set mysql_insert_id() to 0. That's what happened to me too.

Your examples:

create table test (test_id int not null auto_increment primary key, test_text varchar(10));

insert into test (test_text) values ('Hello.');

> fetch value with mysql_insert_id returns '1'.

insert into test (test_id, test_text) values ('40', 'Hi 40');

> fetch value with mysql_insert_id: expected '1', received '40';

==> wrong. new auto_increment value for a column was generated. You can easily see it by inserting NULL in the column - new auto_increment value will be 41. That is your insert indeed affected auto_increment value for a column.

update test set test_text = 'Yo!' where test_id = '1';

> fetch value with mysql_insert_id: expectations unclear due to
  contradictory documentation. actual return value: 0.

==> correct, auto_increment value was not affected. On inserting NULL new auto-generated auto_increment value will not be affected by the above upfate.

Probably, we need to clarify this in the manual, thanks for pointing this out.
[3 Feb 2004 11:46] Michael Widenius
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).