Hi!
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'".
>
> I have a possible workaround, which is to always set handler::errkey to 0
> when (thd_sql_command() == SQLCOM_REPLACE*). This seems to work for the
> limited set of operations that I have tried and a quick inspection of
> write_row leads me to believe that this should be OK. Can anyone convince
> me that this is a bad idea?
The following if() looks suspicious:
/*
Don't allow REPLACE to replace a row when a auto_increment column
was used. This ensures that we don't get a problem when the
whole range of the key has been used.
*/
if (info->handle_duplicates == DUP_REPLACE &&
table->next_number_field &&
key_nr == table->s->next_number_index &&
(insert_id_for_cur_row > 0))
goto err;
It could be that by forcing key_nr to 0 you bypass this check.
Otherwise your trick looks ok.
Of course, 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.
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
