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.
Regards / Mit vielen Grüssen,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@stripped>
/ /|_/ / // /\ \/ /_/ / /__ Principal Software Engineer/Server Architect
/_/ /_/\_, /___/\___\_\___/ Sun Microsystems GmbH, HRB München 161028
<___/ Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Häring
