Hi.
On Mon, Oct 01, 2001 at 12:30:10AM -0400, [EMAIL PROTECTED] wrote:
> Paul DuBois> INSERT IGNORE can't fully assess whether the record is to be
> ignored
> Paul DuBois> until the record's contents have been generated.
>
> And why can't it wait until after the ignore/don't ignore assessment before
> assigning a new id?
Because it's written in modules. First, the record is created
(including incrementing the id), then the table handler is called and
the record gets written. If it would be a duplicate, an error is
created. If IGNORE is specified, the error is ignored silently.
To solve the IGNORE beforehand, you would have to do most of the work
(accessing the disk) twice. Of course, one could code around that, but
that would get a huge "special case".
The best idea I come up with is to return a special error that tells
the upper layer what happened and that this saves and afterswards
restores this value. Not pretty either.
> Call it a bug... call it a design feature... call it
> what ever... it doesn't change the fact that the behavior is
> counter-intuitive and could cause problems with some designs
Well, the row wasn't inserted, so don't expect LAST_INSERT_ID() to
have a reasonable value. There are other means to detect whether a row
was inserted.
You are right, in that the documentation might considered wrong. It
says that LAST_INSERT_ID() has the value to the last automatic id
created, which isn't wholely true, because it just got overwritten.
On the other hand, the behaviour is the same, if you get an error
during writing for another reason. One can argue over that all over
(should it behave like an error although IGNORE is specified...).
> (at the very least, having gaps in the sequence could result in
> minor performance degradation due to uneven key distribution).
There will be no gaps?! When the next row is inserted, it gets the
same value assigned again, until a record is successfully inserted.
The only problem I can see is with mass inserts where the last record
violates a unique key and therefore has to be ignored, and if one
wants to use that last value - although that a rather special case.
Bye,
Benjamin.
>
> Will French
>
> > -----Original Message-----
> > From: Paul DuBois [mailto:[EMAIL PROTECTED]]
> > Sent: Sunday, September 30, 2001 10:45 PM
> > To: marcus davy; [EMAIL PROTECTED]
> > Subject: Re: last_insert_id() bug ?? using INSERT IGNORE
> >
> >
> > 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.
[...]
--
[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
