mysql_insert_id Bug

I may have stumbled on some either incorrect, or incorrectly documented, behavior with mysql_insert_id() and I'd like to hear some feedback.

From http://www.mysql.com/doc/en/mysql_insert_id.html:

"Note that mysql_insert_id() returns 0 if the previous query does not generate an AUTO_INCREMENT value."

However, when inserting a non-unique value into a table with a UNIQUE index, thus making the INSERT fail and no AUTO_INCREMENT value being generated, mysql_insert_id() returns the value of the last successful INSERT statement. I understand that this is the correct behavior for the SQL function LAST_INSERT_ID() (which is not reset between queries), however not for the C API function mysql_insert_id().

Please see below for a C program that illustrates this, as well as my current environment. Any insight would be helpful. Thanks,

Hans




The setup:

MySQL 4.0.13-max-log
FreeBSD 4.8-STABLE
MySQL is the mysql.com binary package



The table:

CREATE TABLE `links` (
`linkid` int(10) unsigned NOT NULL auto_increment,
`link` varchar(255) NOT NULL default '',
PRIMARY KEY (`linkid`),
UNIQUE KEY `link` (`link`)
) TYPE=MyISAM;



The code:

#include
#include
#include
#include
#include "mysql/mysql.h"

MYSQL mysql;
MYSQL_RES *mysql_result;
MYSQL_ROW mysql_row;

char *links[6] = {"http://hans.zaunere.com",
"http://zaunere.com",
"http://hans.zaunere.com",
"http://nyphp.org",
"http://lists.nyphp.org",
"http://nyphp.org"};


int main(void) {

int i,R_linkid;
char insert_string[255];

mysql_init(&mysql);

mysql_real_connect(&mysql,"localhost","ptips","ptips--",NULL ,0,NULL,0);

for( i = 0; i < 6; ++i ) {
printf("\nLink %d: %s\n", i,links[i]);
sprintf(insert_string, "INSERT INTO ptips.links (linkid,link)
VALUES(NULL,'%s')",links[i]);

mysql_real_query(&mysql,insert_string,sizeof(insert_string)) ;

R_linkid = mysql_insert_id(&mysql);

printf("\nR_linkid: %d\n",R_linkid);
}

}





--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysql_insert_id Bug

On Tue, 11 Aug 2003, Hans Zaunere wrote:
> http://lists.mysql.com/list.php?9:mss:14894:200308:pgbmapllc ppekmkmgbak
>
> I may have stumbled on some either incorrect, or incorrectly documented,
> behavior with
> mysql_insert_id() and I'd like to hear some feedback.
>
> >From http://www.mysql.com/doc/en/mysql_insert_id.html:
>
> "Note that mysql_insert_id() returns 0 if the previous query does not
> generate an AUTO_INCREMENT
> value."
>
> However, when inserting a non-unique value into a table with a UNIQUE
> index, thus making
> the INSERT fail and no AUTO_INCREMENT value being generated,
> mysql_insert_id() returns
> the value of the last successful INSERT statement. I understand that this
> is the correct
> behavior for the SQL function LAST_INSERT_ID() (which is not reset between
> queries),
> however not for the C API function mysql_insert_id().
>
> Please see below for a C program that illustrates this, as well as my
> current environment.
>
> Any insight would be helpful. Thanks,

The documentation is wrong. We will get it updated

Thanks,


--
Boyd Gerber, Software Engineer & Support Specialist
MySQL AB, www.mysql.com

Are you MySQL certified? www.mysql.com/certification



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: mysql_insert_id Bug

Boyd Lynn Gerber wrote:

> On Tue, 11 Aug 2003, Hans Zaunere wrote:
>
>>http://lists.mysql.com/list.php?9:mss:14894:200308:pgbmapl lcppekmkmgbak
>>
>>I may have stumbled on some either incorrect, or incorrectly documented,
>>behavior with
>>mysql_insert_id() and I'd like to hear some feedback.
>>
>>>From http://www.mysql.com/doc/en/mysql_insert_id.html:
>>
>>"Note that mysql_insert_id() returns 0 if the previous query does not
>>generate an AUTO_INCREMENT
>>value."
>>
>>However, when inserting a non-unique value into a table with a UNIQUE
>>index, thus making
>>the INSERT fail and no AUTO_INCREMENT value being generated,
>>mysql_insert_id() returns
>>the value of the last successful INSERT statement. I understand that this
>>is the correct
>>behavior for the SQL function LAST_INSERT_ID() (which is not reset between
>>queries),
>>however not for the C API function mysql_insert_id().
>>
>>Please see below for a C program that illustrates this, as well as my
>>current environment.
>>
>> Any insight would be helpful. Thanks,
>
>
> The documentation is wrong. We will get it updated

Thanks for looking into this. I'll report this to the PHPdoc team so they can update as well.

Hans Zaunere
President, New York PHP
http://nyphp.org
hans@nyphp.org



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org