List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 2 2001 2:37am
Subject:Re: last_insert_id() bug ?? using INSERT IGNORE
View as plain text  
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, paul@stripped
Thread
last_insert_id() bug ?? using INSERT IGNOREmarcus davy1 Oct
  • Re: last_insert_id() bug ?? using INSERT IGNOREPaul DuBois1 Oct
    • RE: last_insert_id() bug ?? using INSERT IGNOREWill French1 Oct
      • Re: last_insert_id() bug ?? using INSERT IGNOREBenjamin Pflugmann1 Oct
    • Re: last_insert_id() bug ?? using INSERT IGNOREmarcus davy2 Oct
      • Re: last_insert_id() bug ?? using INSERT IGNOREPaul DuBois2 Oct
        • Re: last_insert_id() bug ?? using INSERT IGNOREmarcus davy2 Oct