| 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: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 4.0.15 | OS: | Linux (linux / x86) |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
[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).
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.