From: Zardosht Kasheff
Date: February 26 2013 9:47pm
Subject: properly detecting cases where handler can silently overwrite rows
List-Archive: http://lists.mysql.com/internals/38735
Message-Id: <CABFd+SF3UD+hpjAeGdkhrp9Db=8x8xF1EhQdZzRtDyxhK+dOhg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1

Hello all,

For a bunch of statements, "replace into", "replace into ... select",
"load data... replace", we sometimes do not follow the protocol of
return an error if we see a duplicate key so that a subsequent update
can overwrite the row. Instead, we have handler::write_row silently
overwrite the data and report success.

The trouble we have is properly detecting when we are indeed allowed
to silently overwrite the row. What we have been doing is checking
(thd->lex->duplicates == DUP_REPLACE).

We recently found what seems like a corner case where this does not
work. During replication on a slave, if slave_exec_mode is set to
IDEMPOTENT, then thd->lex->duplicates is set to DUP_REPLACE, but the
operation is not just a silent overwrite. The operation works like an
insert on duplicate key update, where the updated row is dependent on
the existing row and the inserted row.

My questions:
 - why is DUP_REPLACE being set in this case in replication?
 - What is the proper way to determine when we can safely silently
overwrite an existing row. We already check for having a binary log in
row format and for triggers. It seems checking thd->lex->DUP_REPLACE
is not sufficient.

Thanks
-Zardosht