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
