List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:May 5 2008 11:37pm
Subject:re: Handling duplicates for REPLACE
View as plain text  
Hi!

>>>>> "Timothy" == Timothy P Clark <Timothy> writes:

Timothy> The storage engine that I am developing handles error reporting for
Timothy> duplicate key values differently than MySQL expects. Specifically, when a
Timothy> row causes duplicate value conflicts with multiple keys, only the last key
Timothy> that caused the dup error is known. However, MySQL expects the keys to be
Timothy> reported in ascending order.

Timothy> For example, for the following sequence:
Timothy> CREATE TABLE t1 (a INT, UNIQUE (a), b INT NOT NULL, UNIQUE (b), c INT NOT
Timothy> NULL, INDEX(c));
Timothy> REPLACE INTO t1 VALUES (1,1,1),(2,2,2),(2,1,3);
Timothy> MySQL expects the conflict with 'a' to be reported before the conflict with
Timothy> 'b'. Our storage engine always reports 'b' before 'a'.

Timothy> This causes a problem in sql_insert.cc::write_record() when a REPLACE
Timothy> command is executed, because the loop that retries the replace after a
Timothy> duplicate key error depends on the ascending key order. As a result, the
Timothy> REPLACE command above will fail for our storage engine and return "ERROR
Timothy> 1062 (23000): Duplicate entry '1' for key 'b'".

Timothy> I have a possible workaround, which is to always set handler::errkey to 0
Timothy> when (thd_sql_command() == SQLCOM_REPLACE*). This seems to work for the
Timothy> limited set of operations that I have tried and a quick inspection of
Timothy> write_row leads me to believe that this should be OK. Can anyone convince
Timothy> me that this is a bad idea?

This is probably ok, but a better way would to introduce a new table
flag HA_DUPLICATE_KEY_NOT_IN_ORDER and change the test in
write_record() from:

static int last_uniq_key(TABLE *table,uint keynr)
{
  while (++keynr < table->s->keys)
    if (table->key_info[keynr].flags & HA_NOSAME)
      return 0;
  return 1;
}

to

static int last_uniq_key(TABLE *table,uint keynr)
{
  if (table->file->ha_table_flags() & HA_DUPLICATE_KEY_NOT_IN_ORDER)
    return 0;
  while (++keynr < table->s->keys)
    if (table->key_info[keynr].flags & HA_NOSAME)
      return 0;
  return 1;
}

Regards,
Monty
Thread
Handling duplicates for REPLACETimothy P Clark5 May
  • re: Handling duplicates for REPLACEMichael Widenius6 May
  • Re: Handling duplicates for REPLACESergei Golubchik6 May
    • Re: Handling duplicates for REPLACESergei Golubchik7 Jul
      • Re: Handling duplicates for REPLACETimothy P Clark7 Jul