From: Zardosht Kasheff Date: February 25 2010 5:20pm Subject: Re: question on HA_EXTRA_IGNORE_DUP_KEY List-Archive: http://lists.mysql.com/internals/37741 Message-Id: <2f9663ba1002250920t7f9ddd65m65db980ca229e7f3@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Thanks Venu, This worked. -Zardosht On Wed, Feb 24, 2010 at 1:32 PM, Venu Kalyan wrote: > Hi > > On Wed, Feb 24, 2010 at 10:20 AM, Zardosht Kasheff w= rote: >> 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 ign= ore". >> >> 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? > > Yup; that's one way of optimizing for faster loads especially dealing > with bulk data and/or to avoid cost of searching for dups. > > I still do this using storage engine controlled variable though; but > that's up to you guys to decide... > > Thanks > Venu Anuganti > >> >> Thanks >> -Zardosht >> >> On Wed, Feb 24, 2010 at 1:05 PM, Venu Kalyan wrote: >>> Hi ! >>> >>> On Tue, Feb 23, 2010 at 3:23 PM, Zardosht Kasheff = 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 !=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 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 t= he >>>>> 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 >>>>> wrote: >>>>>> >>>>>> Hello, >>>>>> >>>>>> Does anyone have any guidance here? >>>>>> >>>>>> Thanks >>>>>> -Zardosht >>>>>> >>>>>> On Wed, Feb 17, 2010 at 3:47 PM, Zardosht Kasheff >>>>>> 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 f= lag. >>>>>> > >>>>>> > 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 >>>>>> > 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 inser= ted >>>>>> >> if a duplicate is otherwise there. >>>>>> >> >>>>>> >> Basically, the question is if this flag is set, is it ok to not r= eturn >>>>>> >> HA_ERR_FOUND_DUPP_KEY in the cases where the statement is not "re= place >>>>>> >> into". >>>>>> >> >>>>>> >> Thanks >>>>>> >> -Zardosht >>>>>> >> >>>>>> > >>>>>> >>>>>> -- >>>>>> MySQL Internals Mailing List >>>>>> For list archives: http://lists.mysql.com/internals >>>>>> To unsubscribe: =A0 =A0http://lists.mysql.com/internals?unsub=3Dmydb= 08@stripped >>>>>> >>>>> >>>>> >>>> >>> >> >