Re: Handling duplicates for REPLACE

[Timothy P Clark]

Sergei Golubchik <serg@stripped> wrote on 07/07/2008 07:38:43 AM:

> Hi!
>
> On May 06, Sergei Golubchik wrote:
> > On May 05, Timothy P Clark wrote:
> > >
> > > The storage engine that I am developing handles error reporting for
> > > duplicate key values differently than MySQL expects. Specifically,
> > > when a row causes duplicate value conflicts with multiple keys, only
> > > the last key that caused the dup error is known. However, MySQL
> > > expects the keys to be reported in ascending order.
> > >
> > > For example, for the following sequence:
> > > CREATE TABLE t1 (a INT, UNIQUE (a), b INT NOT NULL, UNIQUE (b), c
> > > INT NOT NULL, INDEX(c));
> > > REPLACE INTO t1 VALUES (1,1,1),(2,2,2),(2,1,3);
> > > MySQL expects the conflict with 'a' to be reported before the
> > > conflict with 'b'. Our storage engine always reports 'b' before 'a'.
> > >
> > > This causes a problem in sql_insert.cc::write_record() when a
> > > REPLACE command is executed, because the loop that retries the
> > > replace after a duplicate key error depends on the ascending key
> > > order. As a result, the REPLACE command above will fail for our
> > > storage engine and return "ERROR 1062 (23000): Duplicate entry '1'
> > > for key 'b'".
> > >
> > it'd be better to have it fixed in MySQL - by replacing
> > a loop in last_uniq_key() with a bitmap of unique keys, so that keys
> > could be matched in any order.
>
> Thinking about it...
> This bitmap solution would disable REPLACE optimization (as
> last_uniq_key() and using update instead of delete/insert is an
> optimization) in certain cases. Using your example above:
>
>   REPLACE INTO t1 VALUES (1,1,1),(2,2,2),(3,2,3);
>
> in the  current code, last_uniq_key() will return TRUE and MySQL would
> use update. With the bitmap it'll return FALSE.
>
> I wonder, whether you could fix this in your engine. If it,
> indeed, always returns 'b' before 'a' - that is, reports key conflicts
> in the descending order - you can simply reverse the order of keys in
> ::create() method. This way, from MySQL point of your, your engine will
> report key conflicts in the ascending order, as desired. As a bonus,
> it'll benefit from the REPLACE optimization, that I've mentioned above.

I considered this solution initially, but it would effectively negate our
online add index support, as we would have to drop and re-create all
indexes in order to consistently maintain this reverse ordering. We could
turn the online add/drop support off, I suppose, but that's certainly not
an ideal fix.,

Thanks,
Tim Clark