From: Jorgen Loland
Date: January 17 2011 9:26am
Subject: bzr commit into mysql-5.5 branch (jorgen.loland:3256) Bug#55385 Bug#57373
List-Archive: http://lists.mysql.com/commits/128909
X-Bug: 55385,57373
Message-Id: <20110117092627.AE5C279E@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============0436950639325611013=="

--===============0436950639325611013==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

#At file:///export/home/jl208045/mysql/mysql-5.5/ based on revid:john.embretsen@stripped

 3256 Jorgen Loland	2011-01-17
      Bug#55385: UPDATE statement throws an error, but still updates the 
                 table entries
      Bug#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a table 
                 is updated twice
      
      Partitioning fails if multi-update updates the same partitioned 
      table twice and the partitioning key is updated. This is 
      because updates on the first table may move records from one 
      partition to another, and update on the second table will fail 
      to locate the records to update due to this.
      
      In InnoDB (BUG#57373), the result was that updates on the 
      first table were performed. The transaction was then aborted
      once updates on the second table failed to locate records. 
      Error ER_KEY_NOT_FOUND was returned. Problem: unneccessary work
      was performed (update + abort) and misleading error message 
      returned.
      
      In MyISAM (BUG#55385), the result was that updates on the first
      table were performed. The transaction was then stopped once
      updates on the second table failed to locate records. Error
      "Got error 134 from storage engine" was returned. However, 
      since MyISAM is unable to abort, the updates on the first 
      table were still in effect. Problem: misleading error message
      and half-performed transaction.
      
      The fix is to chech if multi-table update will 
       a) update the same partition twice, and 
       b) at least one of these will update the partitioning key
          and thereby risk moving records to another partition. 
      If this is the case, a meaningful error message is issued 
      before any update work has been done.
     @ mysql-test/r/partition.result
        Add test for bugs 55385 and 57373.
     @ mysql-test/t/partition.test
        Add test for bugs 55385 and 57373.
     @ sql/share/errmsg-utf8.txt
        New error message for multi-table update where the same partition is updated twice.
     @ sql/sql_update.cc
        Check if multi-table update is about to update the same partitioned table twice and issue error.

    modified:
      mysql-test/r/partition.result
      mysql-test/t/partition.test
      sql/share/errmsg-utf8.txt
      sql/sql_update.cc
=== modified file 'mysql-test/r/partition.result'
--- a/mysql-test/r/partition.result	2011-01-10 14:08:31 +0000
+++ b/mysql-test/r/partition.result	2011-01-17 09:26:25 +0000
@@ -2264,3 +2264,51 @@ INSERT INTO t1 VALUES(0);
 DROP TABLE t1;
 SET GLOBAL myisam_use_mmap=default;
 End of 5.1 tests
+#
+# BUG#55385: UPDATE statement throws an error, but still updates 
+#            the table entries
+# BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a 
+#            table is updated twice
+#
+CREATE TABLE t1_part (
+col1 int,
+col2 int
+) PARTITION BY LINEAR HASH(col1) PARTITIONS 3;
+INSERT INTO t1_part VALUES (1, 1) , (10, 10);
+CREATE VIEW v1 AS SELECT * FROM t1_part;
+
+SELECT * FROM t1_part;
+col1	col2
+1	1
+10	10
+
+# Case 1
+# Update is refused because partitioning key is updated
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.col1 = 2, B.col2 = 3;
+ERROR HY000: Cannot do multi-table update because partitioned table 't1_part' is updated both by table/view 'A' and 'B'
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.col2 = 2, B.col1 = 3;
+ERROR HY000: Cannot do multi-table update because partitioned table 't1_part' is updated both by table/view 'A' and 'B'
+
+# Case 2
+# Like 1, but partition accessed through a view
+UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.col2 = 2 , B.col1 = 3;
+ERROR HY000: Cannot do multi-table update because partitioned table 't1_part' is updated both by table/view 'A' and 'B'
+UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.col2 = 2 , B.col1 = 3;
+ERROR HY000: Cannot do multi-table update because partitioned table 't1_part' is updated both by table/view 'A' and 'B'
+
+SELECT * FROM t1_part;
+col1	col2
+1	1
+10	10
+
+# Case 3
+# Update is accepted because partitioning key is not updated
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.col2 = 2 , B.col2 = 3;
+
+SELECT * FROM t1_part;
+col1	col2
+1	3
+10	3
+
+DROP VIEW v1;
+DROP TABLE t1_part;

=== modified file 'mysql-test/t/partition.test'
--- a/mysql-test/t/partition.test	2011-01-10 14:08:31 +0000
+++ b/mysql-test/t/partition.test	2011-01-17 09:26:25 +0000
@@ -2267,3 +2267,53 @@ DROP TABLE t1;
 SET GLOBAL myisam_use_mmap=default;
 
 --echo End of 5.1 tests
+
+--echo #
+--echo # BUG#55385: UPDATE statement throws an error, but still updates 
+--echo #            the table entries
+--echo # BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a 
+--echo #            table is updated twice
+--echo #
+
+CREATE TABLE t1_part (
+  col1 int,
+  col2 int
+) PARTITION BY LINEAR HASH(col1) PARTITIONS 3;
+
+INSERT INTO t1_part VALUES (1, 1) , (10, 10);
+CREATE VIEW v1 AS SELECT * FROM t1_part;
+
+--echo
+SELECT * FROM t1_part;
+
+--echo 
+--echo # Case 1
+--echo # Update is refused because partitioning key is updated
+--error ER_MULTI_UPDATE_SAME_PARTITION
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.col1 = 2, B.col2 = 3;
+--error ER_MULTI_UPDATE_SAME_PARTITION
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.col2 = 2, B.col1 = 3;
+
+--echo 
+--echo # Case 2
+--echo # Like 1, but partition accessed through a view
+--error ER_MULTI_UPDATE_SAME_PARTITION
+UPDATE t1_part AS A NATURAL JOIN v1 as B SET A.col2 = 2 , B.col1 = 3;
+--error ER_MULTI_UPDATE_SAME_PARTITION
+UPDATE v1 AS A NATURAL JOIN t1_part as B SET A.col2 = 2 , B.col1 = 3;
+
+--echo
+SELECT * FROM t1_part;
+
+--echo
+--echo # Case 3
+--echo # Update is accepted because partitioning key is not updated
+UPDATE t1_part AS A NATURAL JOIN t1_part B SET A.col2 = 2 , B.col2 = 3;
+
+--echo
+SELECT * FROM t1_part;
+
+--echo
+# Cleanup
+DROP VIEW v1;
+DROP TABLE t1_part;

=== modified file 'sql/share/errmsg-utf8.txt'
--- a/sql/share/errmsg-utf8.txt	2010-11-05 17:42:37 +0000
+++ b/sql/share/errmsg-utf8.txt	2011-01-17 09:26:25 +0000
@@ -6394,3 +6394,6 @@ ER_SLAVE_HEARTBEAT_VALUE_OUT_OF_RANGE_MA
 
 ER_STMT_CACHE_FULL  
         eng "Multi-row statements required more than 'max_binlog_stmt_cache_size' bytes of storage; increase this mysqld variable and try again"
+
+ER_MULTI_UPDATE_SAME_PARTITION
+  eng "Cannot do multi-table update because partitioned table '%-.192s' is updated both by table/view '%-.192s' and '%-.192s'"

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2010-12-29 00:26:31 +0000
+++ b/sql/sql_update.cc	2011-01-17 09:26:25 +0000
@@ -1077,10 +1077,42 @@ int mysql_multi_update_prepare(THD *thd)
 
   thd->table_map_for_update= tables_for_update= get_table_map(fields);
 
+  leaves= lex->select_lex.leaf_tables;
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+
+  /*
+    Check if the same partitioned table is updated more than once and
+    if so issue error if the partitioning key is updated (BUG#55385)
+  */
+  for (tl= leaves; tl ; tl= tl->next_leaf) 
+  {
+    if (tl->table->map & tables_for_update && 
+        tl->table->part_info)
+    {
+      // tl is a partitioned table that will be updated
+      for (TABLE_LIST* tl2= tl->next_leaf; tl2 ; tl2= tl2->next_leaf)
+      {
+        if (tl2->table->map & tables_for_update && // tl2 is also updated
+            tl->table->s == tl2->table->s && // tl and tl2 is same table
+            (partition_key_modified(tl->table, tl->table->write_set) ||
+             partition_key_modified(tl2->table, tl2->table->write_set))
+           )
+        {
+          my_error(ER_MULTI_UPDATE_SAME_PARTITION, MYF(0), 
+                   tl->table_name,
+                   tl->belong_to_view ? tl->belong_to_view->alias : tl->alias,
+                   tl2->belong_to_view ? 
+                   tl2->belong_to_view->alias : tl2->alias);
+          DBUG_RETURN(TRUE);
+        }
+      }
+    }
+  }
+#endif
+
   /*
     Setup timestamp handling and locking mode
   */
-  leaves= lex->select_lex.leaf_tables;
   for (tl= leaves; tl; tl= tl->next_leaf)
   {
     TABLE *table= tl->table;


--===============0436950639325611013==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/jorgen.loland@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: jorgen.loland@stripped\
#   d4r3y8xhvarkjsal
# target_branch: file:///export/home/jl208045/mysql/mysql-5.5/
# testament_sha1: 39b94825184680c325c17c76b9e2d54f545377d4
# timestamp: 2011-01-17 10:26:27 +0100
# base_revision_id: john.embretsen@stripped\
#   kez2rfgsujofb9os
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWb3GiE0ABz9fgFXw2///////
36C////+YA9dbA+r3c16vaqIp5kyM3y0UX3dVcUK9g3bX10chsyVUVqcNFPSSfqjT1NhJ5I2o03p
T1H6ppkD1A9Q0ABoABoaDJCYENAEmp+k1DTRoBoADQAAAAAJSKfkqepmKAZNDTQAGEaaNNMmQAAA
AAkREE0NCYRpqZMnpGpqflR+on6psoMQ00HlPU9R6j1NDygikQiGp+owmT0p6nqGT9UxNAAaD0jR
pk00AAAIpE0AQIxpNBpTyZTamaEmQzUBk0Gh6jIAaXh/BoRzHMmEfh+WnI+xJvS9XQRwMItc4F3X
27GH9k8BozLNVrD+9zMSIiJYSAI4giqYtJRm7DOrj1MzGeMfN55Bp+KTuZRzlh+Q4mSSEpKEpSlh
L+JQWChCI9RBjZSuqAPxmAedxELOmGvFPqehxptLBaGF63bpS4fUclzuOqytG30zjaa2crM/xgSX
hJn9XN5snv52+Y85FL1fGa7KABiCOANJfDAF5DJg/I8IBhAYHsMxT+X28evvIr7QNWG07BTzHeHw
DqBtppr8bj4o5K46iozqovNxeq2lbT9oiVkk0RRbmk4sfMbC4wTUl3TWAMBsY03TeZBG4RpFUYxl
QYaRHSHkfpCS0eZPi9wHwGWBn/IT7y4njgy4fYJyC82HWI9R7yivWAaypU4rNrj4np+/06rExhqu
8UeYnkfKlpPT1RFeNlE5BhCdRemlKYekxTTVE5BOPUduMEV7hXAQgPr25efCXrR0rYcxo9QxyEyc
iunDh6/u0yaiGm5eEn3k3dDBGmHVuA48RK9xGxqDhArEUBR8/1O60ILCWlmYFzWhqDioILUZ5RLW
XrUZFly0EdwvgM2hIwtz6EQNXsbyo/OQZwK5xvM3I+Vn5w4ZXsiqMxjPJqcF0oWqJNZsCDDbcCSO
R0RMYQSDKRS+iMrp0+elDV4qJuMgwVpBRQ9p8SwDEgjnWHwsg4YePQrHbpuN4WtUlUUnUkpVvyAf
czvFCwCzV4SIIhsbbbbbbbbbb/uHc04Ni7VUhdAt7wB1wDUQA9DItc5vUOYGuGZNjwMtMUwVwYfl
8ZHX13enbUeeoD9EHIKXGrfQ0FxLwiICDO4rrIbC6hpSR7loXFVQeKQcT1maGx3jgheqJbbZETMj
aD8FD6mAv84r6R7C36avPsHzY9lhJ9gGkfcb9C6LdENxoYaujkV2jmK2yj6D0iYDCMGIPREnE4SV
M0+fve4+2Et3WZzDXpKJE06U1+7rvV5Wm6/gwj94dKrRfutFwGuAcTCZTLE0A0CqQSHBeuqhZ6Qw
J76RlRAoXmLLFUFSVgUCqVFJYVKmA69q1qcerUe4YFQPkJlJINhBg3Nh9FRQwcGCIVjZIxZsqBlg
c/hqbASss5DE1a7J076bXHXOPHfojjeN95S09ozT92XE4l19re23lFbDZWU4eXVzDdy5iZmUZFjX
jhHUcwchAuOUZuOY3BYV3yg5DUX5tew5UxYwgKSEATpQiFDebSszsK9ppJ6GlHnutB59uqQuvAzn
rLK7rBquvKVZzXYbijjgf8jkZmJ+Dto+nJ9b8WfCCUSxs2rjqKDAHcdkXgKRxaIPwFocIwOY57gg
ZuuA0SQO0JMLLwJIXqNBlOsAt2mIsDZhczt6Osz4DXqAsNP3woFLKDelnBTyM88Ryk9m3odK4D7A
2Ig0rbBOFpq+W5b1QFpyzfBqMYgvBPdospnAhPjd5Uw6E/549RGFolK2trghaKjO0KEzXavFgxEj
XHhUIRGQ1nM8oOy3jvG0TgI3BQ0XVk626IpFoBUxqyzIrmZkenvKU74s1cTXdI8DYWc3GV0Jb62z
acxViG7uact5hKvJ2GautCRaWnhY5F4zm67mo8NRadhRsNFQvAgWRVi6tTQb2OCqRbwHB820Kewv
MyFg6woTrCBd4aRBDGMJKriGWSUIjIAwilqIlISBmXhFQOe+uxoa4OANj3ucYNehWJVKOmlNAR1V
ia1sY6gShgW0MVIZFK+rnHSzZCZZlmMmRyYRyBZLtlRZFBolFSLGKwIscIpSuEXpXVfAyDGxt+J1
QSMSSVNXDSeaQibKowP02br4YY4/IESIWLuPyhvAbEIlBmupdR69AnAECb6cyn9OYDzHMpUD6CYc
Sh5xNwlSJDqBITEc+IiCGDgT3lREFcF1taSA31CVfdLAMlD3B/a4KuZfgQAxnyk2BpqBrA+dwQZQ
hhqvKCLv/B8w9t/9R0Dfh9mQTAxXPVLdpKDdHEPuE0aDIoVqfkKvhNAo0+9gTjqDUJ9AdQEUKtY0
JsGgCBqGYzXcJvlMSsKm2sKEwi7E/AS4CijAnYHvC0LkYDY8EgxkzFUUEfMLItA7QwDSNqmMgcSY
ORcORlEQ0JCUKUxAsEuN9gdNqpaDRTaN5ZXWJbqDG9uuOYDiNYUaxNA6VJiXBFh3BbUlAvCUD0hI
Vd8C0aRb/A6UejvCE4ISgKEd4gJr7KEh9LQscYdAqGuE8CIQrF4iS6ke8KSS2Oa5eL5xFqBkIGSA
AwBUQF84sahfDVeHTJBxtBTCwvlNwTB3/L/sNuig601zbL5IcfTJ98E0dNNR7UoTaJaCnSMSnuBg
VdELq6xdCrkWYXh5L165cZcFaERAAbGBMAqeFBLTW1eyl/Lun09z9fj1/ZDrFZYXhM05SzhpN7qN
xVNsh13lk6SRs67NbXnSTo0KcOuHM0niSLSUGJdpZiaA5iKE4Li03s6FxTGyp7dlovoP5nmwvvxl
IMjJhIPSa1iBZH6T+qoCpSARvvAhlweVcVlok2NDGmgbY0mNPYMgbSbNYMXcMyb4VhrMrOk1UPUD
KW2k3EbXLAZ1IQj9eGg7RF4F57zE6Tr79uW9HSSd1upcu8Vnejkia47GfmccTPTuKIfA6hhaGWiL
nbauIxUCaJskL49CvQyyAZcaYqMRNYItoQyL6ECOyLsLKpjmMjV81qIviqJSCykIK4fkN5B1lI4S
RvNW86ylE+0NAVVjMYyArWJnRsmNkBsOOzyDaJtt71CDna0035LvujMy0aMz6KgULSyoeQqpJ5jQ
YysVyZeXsmebO37+zMaDdyLcxel5vuxs2JyOcq6QDRgXnZo5FEFx91ordPPAdrUyQzLqo38ux0eT
Q5BVo8f3kfHo7QhPfAW97iJnqIJ5c6GxrElHMukgdcLYh80Fx2NiG4G6jwhD4/m5eJ4jhDPwJGsu
7X0EjtrNKVl5U5hbk6Li8ex9hUlXRuZn/g6/MIDFZRY+k4PwqDkUkWXhnM4vNSsu52Ae87CzXUFA
N+MkIF7NCreNrq3dcmlSsy6OVajgjEeI1RTYI10giF7mOiACCMdXhJhhgpgztKhgmlb8AcyE1cvJ
9bLo3BQyWfDq6IBjgVNehTy+j/uYyg9dS/UdV6AzOsjahlpw60unbp05TUBpd4evs6b65kszBMYU
PlcnWF4+ZAh98BLJc/MqZFygTsC8GbwSlFplCVAoFBAwEjlJdkAqrmUT85mKzJSS4LI1gvRvXsLW
99k1xBsb6dppw6CW+CUBUnAmpBKIY3VcIQ0QLAN1astLbSXBuC5HfcW4DWEIsb1CAkkARkcz1erN
02Gy1KrJmthN1YHo2+Ta4o4BoLLrF5jdIHb2+jeiXkfOancaaOKkqg5NTMKJoJHTocc3H1byxyvT
LtrklEM/51vaWLH9GlvMOcqlzAlt7VKFIIaanHME2stEmmEJNCskl8WgxT2qizWQkUBxMgWhkyhJ
LkLyRvw72baYGDBAMQM8Jss92pQ4Bvh3JDKqz4nk8atgWBqOLQznz+0esAPXYjQmiZNawIe5gNJD
M7qMjXUhncEgMDXgaY1kDBCRCQfgoTAwSev7bUecqWlz8wcbn3uROGCpIKerqlFZ6ZuznKLEb70c
ZZbAQNogWzXkZ4WrwiFHj91QC8KrER7AYJGKN9lWbdvX2qyl12SOtWHjzyty6O2BL05fry5kBwyw
xre90o7QT6GpF5d+lmNGIuI3rZNSSexLXrmb5ST3tqXCxowaeJQwB8pLnoTocPqL9ZjusQOCXN+l
PrhsS5wxAVBpDmEgIE2QiePOVIYZlRGpT0dlEXCpAxsiHBSW0lqUkSQb/tLEX+TF8eSEA9Ay22Xj
wElLmK2orJ7MHpzMkkHdE08vRrSkHyr13ra1Q1LRy6DtDK80RERHgsfUpIo1Wo3A5mL2EzORJDJi
BvXF2NS+2q4oWFsCHKKSXzIZVRQoaRuv12oyFxFVVVW6vg9NTEE/oECWdz5YCZXBKjfdICLUPfD/
ch68wJSI5IQkapFgYoBdmSHGOkhSlObVU9UCXHyBoKyUwJZpmlYVAOshmQhR9UIUquNGQbzQjYmJ
KiQGQPm19J92eaMGOgwbwYtzW4MUODjUyDAOpkOWom+Fa2O1LdvfgRHqfR+01lr2ypqIXleshKsw
4jHNTPAzSEHJBVjzMCYGKaLvjYj5LgMrvAH121PHE7fdceSbAtsXn+n8UwcDuNjk1B3YdPPLEsB2
hkyN4pbbxhy8YMN0M1IMsjLagZYBsod52gdksumxqO3naUk32pm117ZOkGFFjkuCCPl6bv8pEUn+
LuSKcKEhe40Qmg==


--===============0436950639325611013==--