Re: properly detecting cases where handler can silently overwrite rows

[Zardosht Kasheff]

Hello all,

Does anyone have any thoughts on this?

Thanks
-Zardosht

On Tue, Feb 26, 2013 at 4:47 PM, Zardosht Kasheff <zard...@gmail.com> wrote:
> 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

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals

[Dmitry Lenev]

Hello Zardosht!

* Zardosht Kasheff <zard...@gmail.com> [13/03/06 06:27]:

> On Tue, Feb 26, 2013 at 4:47 PM, Zardosht Kasheff <zard...@gmail.com> wrote:
> > 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.

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

--
Dmitry Lenev, Software Developer
Oracle Development SPB/MySQL, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification

[Zardosht Kasheff]

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.

Thanks
-Zardosht

[Zardosht Kasheff]

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?

Thanks
-Zardosht