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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.