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 <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
#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/[email protected]