List:Internals« Previous MessageNext Message »
From:Zardosht Kasheff Date:February 24 2010 6:20pm
Subject:Re: question on HA_EXTRA_IGNORE_DUP_KEY
View as plain text  
Ok, so if I understand this correctly, the following if clause:

thd->lex->ignore && thd->lex->duplicates == 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> wrote:
>> 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 != DUP_ERROR || ignore)
>      table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
>
> For your case;  you can validate this using :
>
> if (thd->lex->ignore && thd->lex->duplicates == SQL_ERROR) {
>  // safe to ignore my dups
> }
>
> if (thd->lex->ignore && thd->lex->duplicates != 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 are
>>> 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, doing
>>> 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
> flag.
>>>> >
>>>> > I guess what I am wondering is the following, is there any way for
> the
>>>> > handler to know for sure that the user has called "insert ignore",
> and
>>>> > 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>
>>>> > 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_row
>>>> >> returns success if this flag is set, the sql statement is NOT
>>>> >> SQLCOM_REPLACE or SQLCOM_REPLACE_SELECT, and the row is not
> inserted
>>>> >> if a duplicate is otherwise there.
>>>> >>
>>>> >> Basically, the question is if this flag is set, is it ok to not
> return
>>>> >> HA_ERR_FOUND_DUPP_KEY in the cases where the statement is not
> "replace
>>>> >> into".
>>>> >>
>>>> >> Thanks
>>>> >> -Zardosht
>>>> >>
>>>> >
>>>>
>>>> --
>>>> MySQL Internals Mailing List
>>>> For list archives: http://lists.mysql.com/internals
>>>> To unsubscribe:    http://lists.mysql.com/internals?unsub=1
>>>>
>>>
>>>
>>
>
Thread
question on HA_EXTRA_IGNORE_DUP_KEYZardosht Kasheff12 Feb
  • Re: question on HA_EXTRA_IGNORE_DUP_KEYZardosht Kasheff17 Feb
    • Re: question on HA_EXTRA_IGNORE_DUP_KEYZardosht Kasheff23 Feb
      • Re: question on HA_EXTRA_IGNORE_DUP_KEYVenu Kalyan24 Feb
Re: question on HA_EXTRA_IGNORE_DUP_KEYZardosht Kasheff24 Feb
  • Re: question on HA_EXTRA_IGNORE_DUP_KEYVenu Kalyan24 Feb
    • Re: question on HA_EXTRA_IGNORE_DUP_KEYZardosht Kasheff24 Feb
      • Re: question on HA_EXTRA_IGNORE_DUP_KEYVenu Kalyan24 Feb
        • Re: question on HA_EXTRA_IGNORE_DUP_KEYZardosht Kasheff25 Feb
      • Re: question on HA_EXTRA_IGNORE_DUP_KEYSergey Vojtovich25 Feb
        • Re: question on HA_EXTRA_IGNORE_DUP_KEYZardosht Kasheff25 Feb
          • Re: question on HA_EXTRA_IGNORE_DUP_KEYSergei Golubchik26 Feb
          • Re: question on HA_EXTRA_IGNORE_DUP_KEYSergey Vojtovich26 Feb
            • Re: question on HA_EXTRA_IGNORE_DUP_KEYZardosht Kasheff26 Feb
              • Re: question on HA_EXTRA_IGNORE_DUP_KEYSergey Vojtovich2 Mar
                • Re: question on HA_EXTRA_IGNORE_DUP_KEYZardosht Kasheff2 Mar
                  • Re: question on HA_EXTRA_IGNORE_DUP_KEYSergey Vojtovich2 Mar
                    • Re: question on HA_EXTRA_IGNORE_DUP_KEYMichael Widenius19 Mar
                      • Configure error during the buildHiromichi Watari21 Mar
                        • Re: Configure error during the buildMikiya Okuno21 Mar
                          • Re: Configure error during the buildHiromichi Watari22 Mar