From: Zardosht Kasheff
Date: February 24 2010 6:20pm
Subject: Re: question on HA_EXTRA_IGNORE_DUP_KEY
List-Archive: http://lists.mysql.com/internals/37734
Message-Id: <2f9663ba1002241020j4355d7c1m53878fff02f9147b@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Ok, so if I understand this correctly, the following if clause:

thd->lex->ignore && thd->lex->duplicates =3D=3D SQL_ERROR

if it evaluates to true, I can assume that the user is doing "insert ignore=
".

How I plan to use this is as follows. Suppose I have a table, (a int,
b int, primary key (a)), that has the element (1,1) inserted.

Given the following statements:
insert ignore into table values (2,2), (1,2);
select * from table;

The expected result of the select * is to return (1,1),(2,2). The
insert will make two calls to handler::write_row. For (2,2),
handler::write_row will insert the element, and return success. With
other storage engines, for (1,2), handler::write_row will insert
nothing and return HA_ERR_FOUND_DUPP_KEY. What my change will do is
still insert nothing, but instead of returning HA_ERR_FOUND_DUPP_KEY,
it will return success.

I assume there is nothing wrong with this?

Thanks
-Zardosht

On Wed, Feb 24, 2010 at 1:05 PM, Venu Kalyan <mydb08@stripped> wrote:
> Hi !
>
> On Tue, Feb 23, 2010 at 3:23 PM, Zardosht Kasheff <zardosht@stripped> wr=
ote:
>> But does this distinguish the commands "insert ignore" and "insert on
>> duplicate key update"?
>
> Actually its contrary and unknown behavior even though MySQL allows
> the synax to use IGNORE and ON DUPLICATE KEY / REPLACE together in
> single statement, and it should in general through an error when used
> together as server does not which one to prefer.
>
> In this case (Ignore and ON DUPLICATE KEY together in single insert)
> ON DUPLICATE KEY takes precedence in the code due to the following
> condition..
>
> if (duplic !=3D DUP_ERROR || ignore)
> =A0 =A0 =A0table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
>
> For your case; =A0you can validate this using :
>
> if (thd->lex->ignore && thd->lex->duplicates =3D=3D SQL_ERROR) {
> =A0// safe to ignore my dups
> }
>
> if (thd->lex->ignore && thd->lex->duplicates !=3D SQL_ERROR) {
> // used ignore together with IGNORE and ON KEY DUPLICATE / REPLACE
> statements, I will decide what needs to be done here
> }
>
> Hope this helps.
>
> Thanks
> Venu Anuganti
>
>>
>> On Tue, Feb 23, 2010 at 6:20 PM, Venu Kalyan <mydb08@stripped> wrote:
>>> You should look for both sql_command type and lex->ignore value (both a=
re
>>> part of thd->lex->sql_command and thd->lex->ignore) to determine if the
>>> storage engine can safely return success on dup cases. But remember, do=
ing
>>> so will result in wrong stats (number of rows inserted/updated)
>>>
>>> Apart from insert/replace, load data also uses the same logic.
>>>
>>> On Tue, Feb 23, 2010 at 12:07 PM, Zardosht Kasheff <zardosht@stripped>
>>> wrote:
>>>>
>>>> Hello,
>>>>
>>>> Does anyone have any guidance here?
>>>>
>>>> Thanks
>>>> -Zardosht
>>>>
>>>> On Wed, Feb 17, 2010 at 3:47 PM, Zardosht Kasheff <zardosht@stripped>
>>>> wrote:
>>>> > So it seems that just looking at this flag will not work, because
>>>> > INSERT IGNORE and INSERT...ON DUPLICATE KEY UPDATE both set this fla=
g.
>>>> >
>>>> > I guess what I am wondering is the following, is there any way for t=
he
>>>> > handler to know for sure that the user has called "insert ignore", a=
nd
>>>> > if so, would it be ok for the handler to return success even if
>>>> > internally a duplicate key is found?
>>>> >
>>>> > Thanks
>>>> > -Zardosht
>>>> >
>>>> > On Fri, Feb 12, 2010 at 5:08 PM, Zardosht Kasheff <zardosht@stripped=
m>
>>>> > wrote:
>>>> >> Hello all,
>>>> >>
>>>> >> I am trying to implement an optimization in our storage engine if
>>>> >> HA_EXTRA_IGNORE_DUP_KEY is set. Would it be ok if handler::write_ro=
w
>>>> >> returns success if this flag is set, the sql statement is NOT
>>>> >> SQLCOM_REPLACE or SQLCOM_REPLACE_SELECT, and the row is not inserte=
d
>>>> >> if a duplicate is otherwise there.
>>>> >>
>>>> >> Basically, the question is if this flag is set, is it ok to not ret=
urn
>>>> >> HA_ERR_FOUND_DUPP_KEY in the cases where the statement is not "repl=
ace
>>>> >> into".
>>>> >>
>>>> >> Thanks
>>>> >> -Zardosht
>>>> >>
>>>> >
>>>>
>>>> --
>>>> MySQL Internals Mailing List
>>>> For list archives: http://lists.mysql.com/internals
>>>> To unsubscribe: =A0 =A0http://lists.mysql.com/internals?unsub=3Dmydb08=
@gmail.com
>>>>
>>>
>>>
>>
>