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
2) The same row is intentionally updated through both aliases
This is the closest I got (JDBC style)
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 :)
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).
>> --- Jon Olav
Jørgen Løland | Senior Software Engineer | +47 73842138