From: Jorgen Loland Date: June 16 2011 6:24am Subject: bzr commit into mysql-5.5 branch (jorgen.loland:3454) Bug#11882110 List-Archive: http://lists.mysql.com/commits/139279 X-Bug: 11882110 Message-Id: <20110616062408.908892640@atum21.norway.sun.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============4777686250039845988==" --===============4777686250039845988== 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:vinay.fisrekar@stripped 3454 Jorgen Loland 2011-06-16 BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS UPDATED TWICE For multi update it is not allowed to update a column of a table if that table is accessed through multiple aliases and either 1) the updated column is used as partitioning key 2) the updated column is part of the primary key and the primary key is clustered This check is done in unsafe_key_update(). The bug was that for case 2), it was checked whether updated_column_number == table_share->primary_key However, the primary_key variable is the index number of the primary key, not a column number. Prior to this bugfix, the first column was wrongly believed to be the primary key. The columns covered by an index is found in table->key_info[idx_number]->key_part. The bugfix is to check if any of the columns in the keyparts of the primary key are updated. The user-visible effect is that for storage engines with clustered primary key (e.g. InnoDB but not MyISAM) queries like "UPDATE t1 AS A JOIN t2 AS B SET A.primkey=..." will now error with "ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'." instead of "ERROR 1032 (HY000): Can't find record in 't1_tb'" even if primkey is not the first column in the table. This was the intended behavior of bugfix 11764529. @ mysql-test/r/multi_update.result Add test for bug#11882110 @ mysql-test/r/multi_update_innodb.result Add test for bug#11882110 @ mysql-test/t/multi_update.test Add test for bug#11882110 @ mysql-test/t/multi_update_innodb.test Add test for bug#11882110 @ sql/sql_update.cc unsafe_key_update() wrongly checked if the primary key index number was the same as updated column number. Now it is checked whether any of the columns making up the primary key is updated. @ sql/table.h Fix comment on TABLE_SHARE::primary_key. Incorrect comment was introduced by an earlier merge conflict (as per dlenev) modified: mysql-test/r/multi_update.result mysql-test/r/multi_update_innodb.result mysql-test/t/multi_update.test mysql-test/t/multi_update_innodb.test sql/sql_update.cc sql/table.h === modified file 'mysql-test/r/multi_update.result' --- a/mysql-test/r/multi_update.result 2011-03-08 17:39:25 +0000 +++ b/mysql-test/r/multi_update.result 2011-06-16 06:24:00 +0000 @@ -697,4 +697,40 @@ SELECT * FROM t1; pk a 1 2 DROP TABLE t1; -end of tests +# +# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS +# UPDATED TWICE +# +CREATE TABLE t1 ( +col_int_key int, +pk int, +col_int int, +key(col_int_key), +primary key (pk) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,2,3); + +CREATE TABLE t2 ( +col_int_key int, +pk_1 int, +pk_2 int, +col_int int, +key(col_int_key), +primary key (pk_1,pk_2) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,2,3,4); + +UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7; + +SELECT * FROM t1; +col_int_key pk col_int +1 7 3 + +UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7; + +UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11; + +SELECT * FROM t2; +col_int_key pk_1 pk_2 col_int +1 7 11 4 +DROP TABLE t1,t2; === modified file 'mysql-test/r/multi_update_innodb.result' --- a/mysql-test/r/multi_update_innodb.result 2011-02-21 15:49:03 +0000 +++ b/mysql-test/r/multi_update_innodb.result 2011-06-16 06:24:00 +0000 @@ -27,3 +27,43 @@ pk a b 0 1 2 DROP VIEW v1; DROP TABLE t1; +# +# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS +# UPDATED TWICE +# +CREATE TABLE t1 ( +col_int_key int, +pk int, +col_int int, +key(col_int_key), +primary key (pk) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,2,3); + +CREATE TABLE t2 ( +col_int_key int, +pk_1 int, +pk_2 int, +col_int int, +key(col_int_key), +primary key (pk_1,pk_2) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1,2,3,4); + +UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7; +ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'. + +SELECT * FROM t1; +col_int_key pk col_int +1 2 3 + +UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7; +ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'. + +UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11; +ERROR HY000: Primary key/partition key update is not allowed since the table is updated both as 'A' and 'B'. + +SELECT * FROM t2; +col_int_key pk_1 pk_2 col_int +1 2 3 4 +DROP TABLE t1,t2; === modified file 'mysql-test/t/multi_update.test' --- a/mysql-test/t/multi_update.test 2011-03-08 17:39:25 +0000 +++ b/mysql-test/t/multi_update.test 2011-06-16 06:24:00 +0000 @@ -703,4 +703,50 @@ UPDATE t1 AS A, t1 AS B SET A.pk = 1, B. SELECT * FROM t1; DROP TABLE t1; ---echo end of tests +--echo # +--echo # BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS +--echo # UPDATED TWICE +--echo # + +# Results differ between storage engines. This test is to verify that +# the bugfix did NOT change behavior for MyISAM. +# See multi_update_innodb.test for the InnoDB variant of this test +CREATE TABLE t1 ( + col_int_key int, + pk int, + col_int int, + key(col_int_key), + primary key (pk) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1,2,3); + +--echo +CREATE TABLE t2 ( + col_int_key int, + pk_1 int, + pk_2 int, + col_int int, + key(col_int_key), + primary key (pk_1,pk_2) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,2,3,4); + +--echo +UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7; + +--echo +SELECT * FROM t1; + +--echo +UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7; +--echo +UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11; + +--echo +SELECT * FROM t2; + +DROP TABLE t1,t2; + + + + === modified file 'mysql-test/t/multi_update_innodb.test' --- a/mysql-test/t/multi_update_innodb.test 2011-02-21 15:49:03 +0000 +++ b/mysql-test/t/multi_update_innodb.test 2011-06-16 06:24:00 +0000 @@ -31,3 +31,47 @@ SELECT * FROM t1; DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS +--echo # UPDATED TWICE +--echo # + +# Results differ between storage engines. +# See multi_update.test for the MyISAM variant of this test +CREATE TABLE t1 ( + col_int_key int, + pk int, + col_int int, + key(col_int_key), + primary key (pk) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,2,3); + +--echo +CREATE TABLE t2 ( + col_int_key int, + pk_1 int, + pk_2 int, + col_int int, + key(col_int_key), + primary key (pk_1,pk_2) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES (1,2,3,4); + +--echo +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7; +--echo +SELECT * FROM t1; + +--echo +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7; +--echo +--error ER_MULTI_UPDATE_KEY_CONFLICT +UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11; + +--echo +SELECT * FROM t2; + +DROP TABLE t1,t2; === modified file 'sql/sql_update.cc' --- a/sql/sql_update.cc 2011-02-21 15:49:03 +0000 +++ b/sql/sql_update.cc 2011-06-16 06:24:00 +0000 @@ -1071,17 +1071,27 @@ bool unsafe_key_update(TABLE_LIST *leave return true; } - if (primkey_clustered && - (bitmap_is_set(table1->write_set, table1->s->primary_key) || - bitmap_is_set(table2->write_set, table2->s->primary_key))) + if (primkey_clustered) { - // Clustered primary key is updated - my_error(ER_MULTI_UPDATE_KEY_CONFLICT, MYF(0), - tl->belong_to_view ? tl->belong_to_view->alias - : tl->alias, - tl2->belong_to_view ? tl2->belong_to_view->alias - : tl2->alias); - return true; + // The primary key can cover multiple columns + KEY key_info= table1->key_info[table1->s->primary_key]; + KEY_PART_INFO *key_part= key_info.key_part; + KEY_PART_INFO *key_part_end= key_part + key_info.key_parts; + + for (;key_part != key_part_end; ++key_part) + { + if (bitmap_is_set(table1->write_set, key_part->fieldnr-1) || + bitmap_is_set(table2->write_set, key_part->fieldnr-1)) + { + // Clustered primary key is updated + my_error(ER_MULTI_UPDATE_KEY_CONFLICT, MYF(0), + tl->belong_to_view ? tl->belong_to_view->alias + : tl->alias, + tl2->belong_to_view ? tl2->belong_to_view->alias + : tl2->alias); + return true; + } + } } } } === modified file 'sql/table.h' --- a/sql/table.h 2011-03-25 09:06:07 +0000 +++ b/sql/table.h 2011-06-16 06:24:00 +0000 @@ -627,8 +627,8 @@ struct TABLE_SHARE uint db_options_in_use; /* Options in use */ uint db_record_offset; /* if HA_REC_IN_SEQ */ uint rowid_field_offset; /* Field_nr +1 to rowid field */ - /* Index of auto-updated TIMESTAMP field in field array */ - uint primary_key; + /* Primary key index number, used in TABLE::key_info[] */ + uint primary_key; uint next_number_index; /* autoincrement key number */ uint next_number_key_offset; /* autoinc keypart offset in a key */ uint next_number_keypart; /* autoinc keypart number in a key */ --===============4777686250039845988== 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\ # 637j8qymf3vxdb41 # target_branch: file:///export/home/jl208045/mysql/mysql-5.5/ # testament_sha1: ff9a52820a2bd58818bc402120e750e176b1494d # timestamp: 2011-06-16 08:24:08 +0200 # source_branch: file:///export/home/jl208045/mysql/mysql-trunk-55385/ # base_revision_id: vinay.fisrekar@stripped\ # nqvofg478jaj3oph # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWXbjDgYACPX/gFF9IgB4//// ///fqr////5gER19n2y9Y63baq27dlwqp1oGN0KBrcNu1Ow9ne287LLLGmbNsVdcbuuImkgB5RoA A0AGgAAAAAAAACSRDQ00BMoMIhtU8EymjTQyBoYmhpoAAaDgyDRoAyaYhpoMgxDCBoDRiYjQAACR EImSPSaMTU2QjJ6ImnqbUnoDSADQaB6nqMQZBFRDIp6T00U9qaZJPxTU3ppNQ9T0T9U0ADTTT1PU 9Q0AaG1AVJRqaZAEaBomjTSempo1MQyM1ANGhoZDQG1ND67ZwOvoN0ON7I/m3/wJLlwNYwD19RDb P4QYDQjr1EUyCcD8UHggHJH0zWjpEEwE/dEGxzuPqCkrYv5YEoFbUUj36nr38V7rbXW9u4XtlKqK zvgyyZOqZHt49CXPnWHFvvMDAsrD4088ARQYxob/exxDiCmr3SAe+gXAwlBy+GBsGebTOiklz6Va ublcMOOfd/dKnlhHcaM9OzJFU7eTYG8IC6NFsglOQTCNd6TDLdmgNxOwo40l9SRs7BgZzf0uJjNA HLF/LpswI3Ae7+RbRo5hf5F2CbYNNMZl9oHZ5c1sv6x1sr5n12vqVnMW3TUoFe2UUdmXMrVqv6un 3nGJ0RM8cYUUL+0/L47BaMp7BwDzoiB1I6jP1B5mA1882yygFnTLAIoxPOhMfVF59ZRsb4yc1Mo6 DoN+/w8b748X6Tw57/Wc93wOKVICYDCDDXUtbovsUZgB7OK8hxgD4RqulmLmlmvKxXMtAHzY3xfa 9FJmsRFTSXBIcpQoUCgUK3OlLY0ClL4mkUkHTGen7aoCoZBA+jz/tGZCvhyETPHpG6ch9V5cZqy4 rnBosGhxjinAQENW299Lkqwr8DrQe4PjCA9wMFKCQk69WUcG1pK1E3XNrY8LmXDM3WmXtzZtRhyO 3Fj3fUzOpk51OjbKrw92hEhla6bPvQxqlGUXtgW8288Z5N49HbJ479AfL2+2No49l4vkv+XB3diR b6VCQuUgYwalKjqPrj4z/faYDyVDzPjuh21OkVI6HI27wHuDDG72Ygc02fysbJATTP4tozDHNeB4 mR719t6S4pJR8dXNX4OZtG0azwG0sOSSOWgaAzt6coi5xf4gXiVYBFdJVMMzQN0vmdqeTnp92fWR UqYu+seE+JO84aZudNmZNxz+eYS6etrg7Lu4x6qcH4Q46ugTuvXK50KUkqkwu04BI6w6wYGDBg6Z 2DG2h+3uO77P4wLNjeLm5txEUBknXcr1YkWKFe9y3FBfa6g9kd6ehPNcqCiAzGAxVY2MEJmpiJ4y H1M/i+Dg3NuottMECwALExK4iii7OZ8zpUUBCMwpacLDNJ3zMiTS5yuqo7BzH9nWSM0EaHcAMrpM z0KkKIVkFsiZwKgkGNyMxxBhtC8ctKkRvjy0DFQoSCcZt8vbVpCarBMB4ooU80PkQvy24unIEVcK hcdu2udpt26GBnSPzyeIclinMviwoJIP+vSF3TW3yrr+1ze/nnBgjR6NmKWO2lUpuTyi7JipZytr kTc53btsTtal/TuuVsL6iLDut7B2TqnJyzgm+pJN/GocsOra0QuyZJq5w0rLTI7FTLNcpbMaMyZ8 e7V1Zssoco5Re2uouki/bcf6YFlhyl68aib1goWrxLYqYT9prAyW1UDi2kw897i6zNmVwTDTw68r 2Jwmq2OTSY6Lg6cNCweTuJEV7FkGBTdmcq4DceEzUD4FAA0VybLhHHXnrXmX83UdGfqDmacE66cH RUTnv05LcbKOMseFMS4gTXVlZzUFIqqSkNExOTlhRePVtEhnjMT72C5KJOSM7zUUDyw1jLA1Sywj RHnAcB5M5Flb2rVB+JWvIKg1wNZlU0mwqqvIagvxFqOCsnUJynYeQcVFmZiMXmgtNy2LpUTRaVGu y/CNChbk37V6b5UhUwYrw7JOIZczDQy6F/jbI6CBQShURrDjrwQyV6ZFjisyXcK99uVpSTaq9Qol 8nHPRqecFBOxRWcy75GnaYF2ZPtMVe/QhU05yaoDw1MXi4rJQ9us4IAOku8A+BcandMic3LLctsg DHKNwTTGhPPaRGeg3JZlo+bEaI+YjxzIH2OK4u0rkV3lhIyXcAGqvk1sw0IimemfO15Xsz3PO8VQ gQhWUzitIbC0U5UYZSnyjzQfqssLDx2oPzGOasB+syCNTKGUy9Yq53ZhzTFEmlQrmd1WwKCum9yl aLnatEF97dFP0Z7MpBDooGTMkfVv10oFXs+ptscl55d/s+NLrz09/d58K5BDGHzCukpSkU06h5TA pSqqtPEef5k9I+CkZml3kOpggUI/MEEfnUOsH1gdi+rsJC9iXhSR9JPdLRL2k/ITpdqq+ylyqKzH 0SGOJo+2P0mcXS8VZqi+NcidyYH2x9Uc0SiooUK5sRQ23i0cBpGaOxlLCw9z7hfE+4mJluj92tOS NA5dWmMw06sAq7ktChPzR9IFpIClHcuMIvQEEbu2aKcSetDrFfzOUsMdgukxLzQc0S4mEXDApkLY ReF2LUP+xOodZUPJHSmEaTQ1SJqjTHLmixNukZoxHaaoyiYxnXQwbYuF6TbF8mkaxhE101SMYmga L7SGiQc5iMIvJaMIvZXyqqsByR8JhFo8mET2ms2kzCo2eiMrpHdPx793gCDzBmSuDSglKNYW9HiR wF4skTCROR+cH1LFqFUKlpGb6L5Fny/DTBelUDf+ISOBPvFMA69DD6OAvI+cjcBD6Yj5i49CSJHp 9GRPWfP5rvSPPQYWFJaffYHCaMAZ6+6IWBGvEd9ZWTlCZJkH03LFTr1qb+QlcyKuUV+cx4ZODi7H OBGyszU88i1cPQpqYJL1rRwgpBKsoD7hvK5OZ5e1VDJuQSRSJ2svYnOZZrw6VJH4GQ4A2HcOx3vp d7uXr2K3CbnnzHgqfh/Rc8VNrCdeMqe4dSxNRaavXiPTryqFYU5mIniYGt2gGHOaJtDjJ2mzqGaT yFi6CbDZnOjnOUIHpxDRiY8BlFzYJmI78blqlyJbl+TzP6Fiwoo4i6TNQ4xCZtebPBjJidy0KQsi sJEN+iCzJa6wi6OZKbOvmXzwnFc7GqdzjLvIrUPGWXs8b9Y3bmhlM8SpfUGfMzNDikj1yPgDF7DB 5fArMCsssYNDitSYnXSqh9SxHepY4jgdPTx9nZsYsx9HjRS1ENMUD5IIqDQ4wGJcAakpgyEMQu1o hIYvV2QsPWr48g9hXcekoHOeGo9SxxvjkcXnmKG8odJPVmFziuNj1MI5AUpEmKCCe0ynLq4HibDW dYIsjccTzdXDr2UDXaFaa0LKO+K0sRiGAoLBJMAxG+YEpKCKoRMBgckbtCTSOQjKIQNSHMkMOeWQ Vo5eOYcCO04cLV3S1KlMBoVtkdcqJxm3McnNvVszUMrJiDTblbfZRZwpqkuSIkOYwS98gGI+SuK9 M0+XtzwvOU1RO0iNlvfpSlRVPITzPCdhIAjqOctNm42hmLGBM1rWQ1sDGsoGNwOU7hdfCJhcqDwL eFa2dQMB6KEiDdi2J91BzojOYS0eXgilQqIwi+O5rmDD0vGdjfOuedOG07pE1wEm4Cw19DY0DaE+ g1WRdNVwOpXS2OwI0k7F0CUI7FngSOcNKMo2T2eg1tAP6dpVOyNwaru6LahPoczXwIOFQLaQa1tb wlrRY4zZvb6OTC6RaieXOSXEU+/3+TDaa4Vsbeyb5ZvcNVKkVKka2KLS+Ys7pxj0vd88x+enX42L eOnqNlbJiAeB8jzPMydD+RvWAx7+Uqcx2rWtxzDBsPBRAJFVlK2G4OgbQmA4YoYdIQBDaHLcLj+E ++fuZPq+6XF5O9ZEoNPIuZ4y5matT4GA53iChqQqpKXu8gATSZFE0+qXfNOnF2MOxKFV2IlRclFE rdNZyx6PDLttjddHq6yMxwi+TlLrpE3k2PzVwa3rM0HQ7SOGqZkBcriXIvISmsjjAKeEocja2A4j DMliSY+fyLzzMqSLREOKREGs++qLByrRktjy4yaQyQZJQ43Na1BFSAKYistQwu0H6IFEw0GYgEOA vGoiBDCZUGLsKqR8SAzQ1MJ1tbvmw1so1UNa66JKkqIqUodmO9qM9lZM6Db4AjciFnHyCakISMjH OwPBagA+vrBHllAiuSFq1UsGBVnSuOExT8ThOg/4RvBr/D4QSbAo3VPeA3Br2pbZWS7L068bs0+0 IeJ1mjK7YjADlIwA5wjKikbFUsWrvYPBdNc+dmdz5D1CyyyxYWWWfYWGDdvnFxF6vbbbYm/F5OHm z0uMlsGek713mwQetek7lpPMssV8gB6ja2DTQ2229cBDGUH0DN5AfZZMZCh4YUFlVSY2lXOvTn01 rNsRJxgYrm6J9VRRQrgzYTc6rnyuLBoy3x05THVHa1RENKcHCSGNFWUKwDBuakES4FLkabGNJspC 3wcVSp0m0vNaWnjM2OBV806Ahb2EJZ8y1QgkW5fCaAN/iLe8IiIiIiMTtRBMqljRNyOlURy03m6a IXwq+b8xyKz+NW6ZMxT3wFBlgMSs331C193eXGJdcCUFFRUNRWNqxshNdQnuFHxPJM7ZTJExfMSm dONc+ik/4+gdC/NSAUWSNRO4Rk4Z1vHMFQt4zItGcuXkuxdBkliN9Qt0LjD9bDPekcOLGFBQk/VA hCZMkUJlC4G9qd73yglaWtAFlw9TgdD4XpeUycE3eVTQvuqyLuQYGNbwaCWEzhgOZFgBnhCzGaba m0MG6bzEmbhg8pc2tKSV7mf1KRxGAYrA21INF2nlOJIL1QtkC4c40XQp5NrbOpXI2DmhkoJWC0r4 ChnwTbHzFjap59AN86ptmUF9E5FaKrtuSVJHF23xhUFqaVEBLPsSZ1MVENCzrjW9dABKpZBVcSth 1MWlcrxN5S2WN5seR83J/umij/xdyRThQkHbjDgY --===============4777686250039845988==--