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.
I also noticed that last_insert_id() increments for failed INSERT records...
SELECT * from organism;
#+-------+-------+
#| OM | OM_ID |
#+-------+-------+
#| foo | 1 |
#| fodda | 2 |
#+-------+-------+
#2 rows in set (0.00 sec)
INSERT INTO organism VALUES('fred',NULL);
#Query OK, 1 row affected (0.00 sec)
INSERT INTO organism VALUES('fred',NULL);
#ERROR 1062: Duplicate entry 'fred' for key 2
SELECT * from organism;
+-------+-------+
| OM | OM_ID |
+-------+-------+
| foo | 1 |
| fodda | 2 |
| fred | 3 |
+-------+-------+
3 rows in set (0.00 sec)
SELECT last_insert_id();
#+------------------+
#| last_insert_id() |
#+------------------+
#| 4 |
#+------------------+
#1 row in set (0.00 sec)
# last_insert_id() STILL INCREMENTED 1 TOO MANY
cheers,
marcus
______________________________________________________
The contents of this e-mail are privileged and/or confidential to the
named recipient and are not to be used by any other person and/or
organisation. If you have received this e-mail in error, please notify
the sender and delete all material pertaining to this e-mail.
______________________________________________________
---------------------------------------------------------------------
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
