Paul DuBois> INSERT IGNORE can't fully assess whether the record is to be ignored Paul DuBois> until the record's contents have been generated.
And why can't it wait until after the ignore/don't ignore assessment before assigning a new id? Call it a bug... call it a design feature... call it what ever... it doesn't change the fact that the behavior is counter-intuitive and could cause problems with some designs (at the very least, having gaps in the sequence could result in minor performance degradation due to uneven key distribution). Will French > -----Original Message----- > From: Paul DuBois [mailto:[EMAIL PROTECTED]] > Sent: Sunday, September 30, 2001 10:45 PM > To: marcus davy; [EMAIL PROTECTED] > Subject: Re: last_insert_id() bug ?? using INSERT IGNORE > > > At 11:44 AM +1200 10/1/01, marcus davy wrote: > >If you specify the keyword IGNORE in an INSERT, any rows that duplicate > >an existing PRIMARY or UNIQUE key in the table are ignored and are not > >inserted. > >But the last_insert_id() function still appears to increment by one > >in situations when the query is not adding any new information to > >the table. > > > >This looks like a bug to me can anyone enlighten me to this anomaly? > > Why is it a bug? > > INSERT IGNORE can't fully assess whether the record is to be ignored > until the record's contents have been generated. > > > > >I am using 3.23.42-log on red hat 7.1 (also same results on 3.23.40-log). > >I have searched the list archive for this topic but havent found > anything yet. > > > > > >mysql commands to test this below > > > >CREATE DATABASE test; > >use test; > >CREATE TABLE organism ( > > OM varchar(100) NOT NULL unique, > > OM_ID tinyint(1) NOT NULL auto_increment, > > PRIMARY KEY (OM_ID) > > ) TYPE=MyISAM; > > > >INSERT IGNORE INTO organism VALUES('foo', NULL); > >INSERT IGNORE INTO organism VALUES('fodda', NULL); > >SELECT * FROM organism; > > > >#+-------+-------+ > >#| OM | OM_ID | > >#+-------+-------+ > >#| foo | 1 | > >#| fodda | 2 | > >#+-------+-------+ > >#2 rows in set (0.00 sec) > > > >SELECT last_insert_id(); > > > >#+------------------+ > >#| last_insert_id() | > >#+------------------+ > >#| 2 | > >#+------------------+ > >#1 row in set (0.00 sec) > > > >INSERT IGNORE INTO organism VALUES('fodda', NULL); > > > >#Query OK, 0 rows affected (0.00 sec) > ># NO NEW INFORMATION ADDED > > > >SELECT * FROM organism; > >#+-------+-------+ > >#| OM | OM_ID | > >#+-------+-------+ > >#| foo | 1 | > >#| fodda | 2 | > >#+-------+-------+ > >#2 rows in set (0.01 sec) > > > >SELECT last_insert_id(); > > > >#+------------------+ > >#| last_insert_id() | > >#+------------------+ > >#| 3 | > >#+------------------+ > >#1 row in set (0.01 sec) > > > ># last_insert_id has incremented by 1, by adding a further new field, > ># last_insert_id() will correctly show the last inserted id again > > > -- > Paul DuBois, [EMAIL PROTECTED] > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
