For this specific scenario, I believe
handler::extra(HA_WRITE_CAN_REPLACE) should NOT be called.
Unfortunately, I see that it is. So either this is a bug in the
setting of HA_WRITE_CAN_REPLACE, or the semantics that InnoDB uses are
not necessarily the only acceptable semantics.
Nevertheless, if we want to match InnoDB's behavior, we cannot rely on
HA_WRITE_CAN_REPLACE for this scenario.
Is there anyway to properly detect the cases when we can silently overwrite?
On Wed, Mar 6, 2013 at 7:36 AM, Zardosht Kasheff <zardosht@stripped> wrote:
> I have, but we decided not to use it back in 5.1 because it was
> causing us problems. Perhaps that is different now. Thank you for the
> feedback. I will dig into old emails and try to remember what the
> issues were, and then will try it.
> On Wed, Mar 6, 2013 at 1:06 AM, Dmitry Lenev <Dmitry.Lenev@stripped> wrote:
>> Hello Zardosht!
>> * Zardosht Kasheff <zardosht@stripped> [13/03/06 06:27]:
>>> On Tue, Feb 26, 2013 at 4:47 PM, Zardosht Kasheff <zardosht@stripped>
>>> > 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
>>> > 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
>>> > is not sufficient.
>> Have you seen HA_EXTRA_WRITE_CAN_REPLACE ?
>> AFAIR it was added to implement optimization similar
>> to the one you describe for NDB cluster.
>> Best regards,
>> Dmitry Lenev, Software Developer
>> Oracle Development SPB/MySQL, www.mysql.com
>> Are you MySQL certified? http://www.mysql.com/certification