List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:February 14 2011 8:48am
Subject:Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385
Bug#57373
View as plain text  
I tried to come up with a meaningful multiupdate on two instances
of the same table. The following updates aren't that far
off (although still highly artificial), but I doubt that I'm able
to make an example where

1) A column that is a good choice for primary or partitioning key
    is updated.
2) The same row is intentionally updated through both aliases

This is the closest I got (JDBC style)

update
person as father, person as child
where child.personid=? and father.personid=?
set child.fatherid=?, father.numberofchildre=father.numberofchildren+1;

and an update that constraints our society :)

update
person as father, person as child on parent.personid=child.fatherid
set father.isparent='true', child.motherid=father.marriedto

Hence, my vote is still "disallow" as agreed.

On 02/11/2011 03:59 PM, Mattias Jonsson wrote:
> Hi all,
>
> On 2011-02-11 15.51, Jon Olav Hauglid wrote:
>> Hello Jørgen and Mattias,
>>
>> On 01/22/2011 07:09 PM, Mattias Jonsson wrote:
>>> Hi Jorgen,
>>>
>>> Sorry for taking so long time.
>>
>> I'm sorry as well :-)
>>
>>> After looking into it from the storage engine's view I agree with
>>> Matthias Leich comment [5 Jan 21:20] that MyISAM is not rolled back is
>>> an acceptable behavior (since it is not transactional). But the error
>>> code is bad, since it is a very general storage engine error. It would
>>> be better if it was the same as for InnoDB.
>>>
>>> My suggestion would be to simply translate the given handler error to a
>>> server error like other error codes are handled in handler::print_error.
>>> The error is originally from the MyISAM engine/handler and is returned
>>> through the partitioning handler).
>>
>> I'm not too fond of this suggestion. The problem is that you will get
>> multi update statements that will work or fail depending on
>> implementation details and update values. Specifically on whether an
>> update to a partition key will cause the row to be moved to a different
>> partition or not.
>>
>> My suggestion is to disallow multi update statements that update the
>> same table twice and that update a primary key or partition key. This
>> will be a small incompatible change which will disallow a few statements
>> that could have been executed successfully. However it will be
>> consistent and the behavior will not depend on how partitioning is done
>> and on the update values. We can also use the same approach for both
>> Bug#55385 and Bug#57373 so behavior is similar for InnoDB and
>> partitioning. I've checked with Staale and he is fine with making this
>> change on trunk.
>
> I'm OK with this suggestion too.
>
> When is multi table on two instances of the same table useful at all? (I
> think there could be more bugs hiding when updating more than one
> instance of the same table in the same statement).
>
> /Mattias
>
>>
>> --- Jon Olav
>>
>

-- 
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway
Thread
bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385 Bug#57373Jorgen Loland17 Jan
  • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Jon Olav Hauglid17 Jan
    • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Jorgen Loland18 Jan
  • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Mattias Jonsson22 Jan
    • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Jon Olav Hauglid11 Feb
      • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Mattias Jonsson11 Feb
        • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Jorgen Loland11 Feb
        • Re: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385Bug#57373Jorgen Loland14 Feb