On Tue, 02 Oct 2001 14:37, Paul DuBois wrote: > 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.
Ok, I see what you mean, my mistake. I guess I was thinking on those lines because of an example in the mysql documentation 3.5.6 Using Foreign Keys. INSERT INTO persons VALUES (NULL, 'Antonio Paz'); INSERT INTO shirts VALUES (NULL, 'polo', 'blue', LAST_INSERT_ID()), (NULL, 'dress', 'white', LAST_INSERT_ID()), (NULL, 't-shirt', 'blue', LAST_INSERT_ID()); INSERT INTO persons VALUES (NULL, 'Lilliana Angelovska'); INSERT INTO shirts VALUES (NULL, 'dress', 'orange', LAST_INSERT_ID()), (NULL, 'polo', 'red', LAST_INSERT_ID()), (NULL, 'dress', 'blue', LAST_INSERT_ID()), (NULL, 't-shirt', 'white', LAST_INSERT_ID()); So last_insert_id() can only be relyed on when you can guarantee that the information going into the persons database is unique. If it is not, then the information going into shirts will be corrupted anyway because the persons INSERT wil fail but the shirts INSERT will succeed with last_insert_id() = (n+1). But like you are saying a duplicate record can be anywhere, not necessarily the last id inserted. 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
