At 10:45 AM +1200 10/2/01, marcus davy wrote: >On Mon, 01 Oct 2001 14:44, Paul DuBois wrote: >> 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. >> > > >I think it a potential bug corrupting primary keys on records where some >duplicate information is ignored if you were using last_insert_id() in >table generation. > >If I had a large relational database, and somewhere in the middle of that I >had a unique table I was writing information to. If I was relying on the >last_insert_id() value of this table as a primary key to other tables >downstream, then every record where the INSERT IGNORE didnt add new >information will corrupt that primary key of every furthur linked table (by 1 >more than it should be). If the very last record also was IGNORED on the >INSERT IGNORE, statement then you would get an empty set on a query of that >last record, because its primary key is (n+1) which doesnt exist in the >unique table.
In this particular situation, how would it help you if last_insert_id() *didn't* increment? If the record was ignored, that doesn't necessarily mean that you can assume the correct primary key value is n rather than n+1. -- 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
