From: Dmitry Lenev Date: May 26 2011 1:14pm Subject: bzr commit into mysql-5.1 branch (Dmitry.Lenev:3631) Bug#11762012 List-Archive: http://lists.mysql.com/commits/138215 X-Bug: 11762012 Message-Id: <20110526131458.EAA3974067D@bandersnatch> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1602332364==" --===============1602332364== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/dlenev/src/bzr/mysql-5.1-11762012/ based on revid:sven.sandberg@stripped 3631 Dmitry Lenev 2011-05-26 Fix for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". Attempt to update an InnoDB temporary table under LOCK TABLES led to assertion failure in both debug and production builds if this temporary table was explicitly locked for READ. The same scenario works fine for MyISAM temporary tables. The assertion failure was caused by discrepancy between lock that was requested on the rows of temporary table at LOCK TABLES time and by update operation. Since SQL-layer requested a read-lock at LOCK TABLES time InnoDB engine assumed that upcoming statements which are going to be executed under LOCK TABLES will only read table and therefore should acquire only S-lock. An update operation broken this assumption by requesting X-lock. Possible approaches to fixing this problem are: 1) Skip locking of temporary tables as locking doesn't make any sense for connection-local objects. 2) Prohibit changing of temporary table locked by LOCK TABLES ... READ. Unfortunately both of these approaches have drawbacks which make them unviable for stable versions of server. So this patch takes another approach and changes code in such way that LOCK TABLES for a temporary table will always request write lock. In 5.1 version of this patch switch from read lock to write lock is done inside of InnoDBs handler methods as doing it on SQL-layer causes compatibility troubles with FLUSH TABLES WITH READ LOCK. @ mysql-test/suite/innodb/r/innodb_mysql.result Added test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". @ mysql-test/suite/innodb/t/innodb_mysql.test Added test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". @ mysql-test/suite/innodb_plugin/r/innodb_mysql.result Added test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". @ mysql-test/suite/innodb_plugin/t/innodb_mysql.test Added test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE::UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". @ storage/innobase/handler/ha_innodb.cc Assume that a temporary table locked by LOCK TABLES can be updated even if it was only locked for read and therefore an X-lock should be always requested for such tables. @ storage/innodb_plugin/handler/ha_innodb.cc Assume that a temporary table locked by LOCK TABLES can be updated even if it was only locked for read and therefore an X-lock should be always requested for such tables. modified: mysql-test/suite/innodb/r/innodb_mysql.result mysql-test/suite/innodb/t/innodb_mysql.test mysql-test/suite/innodb_plugin/r/innodb_mysql.result mysql-test/suite/innodb_plugin/t/innodb_mysql.test storage/innobase/handler/ha_innodb.cc storage/innodb_plugin/handler/ha_innodb.cc === modified file 'mysql-test/suite/innodb/r/innodb_mysql.result' --- a/mysql-test/suite/innodb/r/innodb_mysql.result 2010-12-08 14:21:03 +0000 +++ b/mysql-test/suite/innodb/r/innodb_mysql.result 2011-05-26 13:14:47 +0000 @@ -2639,7 +2639,6 @@ COUNT(*) 1537 SET SESSION sort_buffer_size = DEFAULT; DROP TABLE t1; -End of 5.1 tests # # Test for bug #39932 "create table fails if column for FK is in different # case than in corr index". @@ -2668,3 +2667,20 @@ DROP TABLE IF EXISTS t1, t2; CREATE TABLE t1 (a INT, INDEX(a)) engine=innodb; ALTER TABLE t1 RENAME TO t2, DISABLE KEYS; DROP TABLE IF EXISTS t1, t2; +# +# Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE:: +# UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". +# +DROP TABLE IF EXISTS t1; +CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB; +INSERT INTO t1 VALUES (1); +LOCK TABLES t1 READ; +# Even though temporary table was locked for READ we +# still allow writes to it to be compatible with MyISAM. +# This is possible since due to fact that temporary tables +# are specific to connection and therefore locking for them +# is irrelevant. +UPDATE t1 SET c = 5; +UNLOCK TABLES; +DROP TEMPORARY TABLE t1; +End of 5.1 tests === modified file 'mysql-test/suite/innodb/t/innodb_mysql.test' --- a/mysql-test/suite/innodb/t/innodb_mysql.test 2010-12-20 11:58:33 +0000 +++ b/mysql-test/suite/innodb/t/innodb_mysql.test 2011-05-26 13:14:47 +0000 @@ -868,9 +868,6 @@ SET SESSION sort_buffer_size = DEFAULT; DROP TABLE t1; ---echo End of 5.1 tests - - --echo # --echo # Test for bug #39932 "create table fails if column for FK is in different --echo # case than in corr index". @@ -900,3 +897,25 @@ CREATE TABLE t1 (a INT, INDEX(a)) engine ALTER TABLE t1 RENAME TO t2, DISABLE KEYS; DROP TABLE IF EXISTS t1, t2; --enable_warnings + + +--echo # +--echo # Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE:: +--echo # UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB; +INSERT INTO t1 VALUES (1); +LOCK TABLES t1 READ; +--echo # Even though temporary table was locked for READ we +--echo # still allow writes to it to be compatible with MyISAM. +--echo # This is possible since due to fact that temporary tables +--echo # are specific to connection and therefore locking for them +--echo # is irrelevant. +UPDATE t1 SET c = 5; +UNLOCK TABLES; +DROP TEMPORARY TABLE t1; + +--echo End of 5.1 tests === modified file 'mysql-test/suite/innodb_plugin/r/innodb_mysql.result' --- a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result 2010-11-23 10:18:47 +0000 +++ b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result 2011-05-26 13:14:47 +0000 @@ -2438,4 +2438,20 @@ COUNT(*) 1537 SET SESSION sort_buffer_size = DEFAULT; DROP TABLE t1; +# +# Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE:: +# UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". +# +DROP TABLE IF EXISTS t1; +CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB; +INSERT INTO t1 VALUES (1); +LOCK TABLES t1 READ; +# Even though temporary table was locked for READ we +# still allow writes to it to be compatible with MyISAM. +# This is possible since due to fact that temporary tables +# are specific to connection and therefore locking for them +# is irrelevant. +UPDATE t1 SET c = 5; +UNLOCK TABLES; +DROP TEMPORARY TABLE t1; End of 5.1 tests === modified file 'mysql-test/suite/innodb_plugin/t/innodb_mysql.test' --- a/mysql-test/suite/innodb_plugin/t/innodb_mysql.test 2010-11-23 10:18:47 +0000 +++ b/mysql-test/suite/innodb_plugin/t/innodb_mysql.test 2011-05-26 13:14:47 +0000 @@ -689,4 +689,24 @@ SET SESSION sort_buffer_size = DEFAULT; DROP TABLE t1; + +--echo # +--echo # Test for bug #11762012 - "54553: INNODB ASSERTS IN HA_INNOBASE:: +--echo # UPDATE_ROW, TEMPORARY TABLE, TABLE LOCK". +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings +CREATE TEMPORARY TABLE t1 (c int) ENGINE = InnoDB; +INSERT INTO t1 VALUES (1); +LOCK TABLES t1 READ; +--echo # Even though temporary table was locked for READ we +--echo # still allow writes to it to be compatible with MyISAM. +--echo # This is possible since due to fact that temporary tables +--echo # are specific to connection and therefore locking for them +--echo # is irrelevant. +UPDATE t1 SET c = 5; +UNLOCK TABLES; +DROP TEMPORARY TABLE t1; + --echo End of 5.1 tests === modified file 'storage/innobase/handler/ha_innodb.cc' --- a/storage/innobase/handler/ha_innodb.cc 2011-04-11 13:40:28 +0000 +++ b/storage/innobase/handler/ha_innodb.cc 2011-05-26 13:14:47 +0000 @@ -7325,10 +7325,18 @@ ha_innobase::external_lock( reset_template(prebuilt); - if (lock_type == F_WRLCK) { + if (lock_type == F_WRLCK + || (table->s->tmp_table + && thd_sql_command(thd) == SQLCOM_LOCK_TABLES)) { /* If this is a SELECT, then it is in UPDATE TABLE ... - or SELECT ... FOR UPDATE */ + or SELECT ... FOR UPDATE + + For temporary tables which are locked for READ by LOCK TABLES + updates are still allowed by SQL-layer. In order to accomodate + for such a situation we always request X-lock for such table + at LOCK TABLES time. + */ prebuilt->select_lock_type = LOCK_X; prebuilt->stored_select_lock_type = LOCK_X; } === modified file 'storage/innodb_plugin/handler/ha_innodb.cc' --- a/storage/innodb_plugin/handler/ha_innodb.cc 2011-04-11 13:40:28 +0000 +++ b/storage/innodb_plugin/handler/ha_innodb.cc 2011-05-26 13:14:47 +0000 @@ -8642,10 +8642,18 @@ ha_innobase::external_lock( reset_template(prebuilt); - if (lock_type == F_WRLCK) { + if (lock_type == F_WRLCK + || (table->s->tmp_table + && thd_sql_command(thd) == SQLCOM_LOCK_TABLES)) { /* If this is a SELECT, then it is in UPDATE TABLE ... - or SELECT ... FOR UPDATE */ + or SELECT ... FOR UPDATE + + For temporary tables which are locked for READ by LOCK TABLES + updates are still allowed by SQL-layer. In order to accomodate + for such a situation we always request X-lock for such table + at LOCK TABLES time. + */ prebuilt->select_lock_type = LOCK_X; prebuilt->stored_select_lock_type = LOCK_X; } --===============1602332364== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/dmitry.lenev@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: dmitry.lenev@stripped # target_branch: file:///home/dlenev/src/bzr/mysql-5.1-11762012/ # testament_sha1: 70db016745dfe2dcd4aecd569e6f5fba308964a6 # timestamp: 2011-05-26 17:14:58 +0400 # base_revision_id: sven.sandberg@stripped\ # wykkmpktyye2kav6 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWa0kULIACJl/gEAwAIBb9/// f+//4L////xgEN9XtsiVGislCoqooSSUANM2DRQUK1ZioVFRBCvgkoiaY1SP1PTKn6BT1Pymp6nt Ke1J6ZNJoyekMIeoyZPUMOMjTJiaDJkwmmQMhoDQGmTQwAmgMcZGmTE0GTJhNMgZDQGgNMmhgBNA YSIiAQyRkxAE0TFPap6jZT0anlPU000GgAaDKoHqA9R6gANAAAAAAAAAAKkkJpoAI0yBNGgmmmmk JspvUTyhmkbU0GZJ9+C8L0Uoublyx0fsfU5vodPLplby0/1UafGlxHkiYEbP/S1rf1+Ok+x/Mrst eH/SzTlBMVDxdO0xD4+7GLFevYsWLHMrDXJVul1WW9v05mRoWjbFAt98wSGARIwL2kdbQfxOw1Ld zS97dlu+/PdZESxPcEc5IQs23mkDBXOdHdOd211TIQCswhIPGpDzQ3Q2nZH5P8OcL/5PrT3J4pVV VRVKqUxvd/ZLDq/3ZNDtnrmxyoeTsdCyZ7n3teBoZfaFlS8uvlUc4xgzB1cs9WwPzh1HpPkgYfQO XYHl6Dme00HfD4w8DNNfPOdPof28bSi2mH4X3bDhZPXXeYWWNVzS14/ifwh/WD7Y/XUk2mBhDsM9 H/LWdnF1vf9nWvdXcv+xOSqTNjezl/66z0zTWFD3swqQ/0yi44tu9A+kxFD6wzMLKZyYhgFdeOyw sR3uLQyJFMxglj/7IClB7CDAIxyrgaWN08y8x4FjLWeTLLKMxplWP7ate/5JyyY5zUXY32WWDKF9 4qJVk35UUtRVK08NehKPeMLuBZZNWzxORR/QqWKubtkhaamlz3Xl3Aw1P6ZHe4k3MS/8G/OXSZXV 07LXHCZmbOZu00G6L+XG/Hnhr1ZszdUmE4+BmaTbmtRkx/Q9r5Plr4+Oce081D44Ji8a4NdAt2A3 4PPoQagVaBgQsEn7lxRSlKUpSlFFSNSbh6XzeGETW7KcTl1c3gsZPdD/2GiG2SOZ6zV+mXzODanG u1SeyEv6dTEPmurE+vv2z0noPW9i2ExBwbEkYt+H0C/YP0bkYsfmU5vGeSYLjVfUhw3sFFj74qUu w5H2y2Lrz3zynvOOTSceHcjaie+540B5GveIaYj+ZxNgqWXi2jlqsj5yVTKhQIn1a+/BC4shktNo T4o3UVJAsAloAgfsCoWVUWZMy6YK808GRzeZgwmMz2uSKmJ472TIlzz241rZ9l8vT7GdtYZOxcNu ya8FNB0/PxkyMrthzBrX5LXq3IIjoCbnR021BdyvPV+ITnnCjYThvaFTbsWlSHW4+XXlEaFOhit9 sN+fK81dq0jFYtYrtzdubZ3X6ta/fQZNDsZjhO4VP27KnVsC0za33cNbPlvm7ZdZ3amaclvPn1f9 GPM3utrRoNDtx7Gt6H0efV2b9bU19ea3kO5LHJ8VgbDq6RWLx3GfTHDHeWoI208KwgRboIt7Pgr+ IuvJm+QdlmUvnUuNIe2YdOTfTPrmqQul0w8FVzLjzWTaRsDXuYasqYUQZfg+5w4/ES7TQWC1gEmZ zU5E2Gv6+dbTYZj4G3dfuwWdydzoJ7DotqAY1zZgYlRwAZRzu1DqQec6YWw2g+bxVpORxftaV6Or eaF7b2NqnW1GLOdU8O+uvDlmXM52SS9PQoSB3WkKRdBO0zKy6rct8kqLArTEaHtb+nhlhMesyhlN HBmuNsx3htb3W47mDNyQ8Bj1Pzi6kqVLgwLCdZgX6wDwTV7t23LUOt/d4TXfuZ5xzapr1YSGHz0z waN+UMuDac4dOGuXw0NDPmX5tjS1YTn1ta6bGspm0NnTFfmmfGY9Z16+bOs6g4tvGMnINk8J7Zu0 tVde7fVYV6Fobh2QvMdJBeqq3jABeaYsRKw0vvLNW+0q5C8sjPbw0uJcpoY9MtLQs7nW7rDGbTmt qKZvJxldMy58NWqWaJ5Twm1y2Y5Y8SkNEpgcsd97LsXLMzx7C/LLNFlzUxvavhnv7OTmdWWCOtTt U1Jj4c+Oft5MRi2UtwvbHazTBc2Nqy92NT1Orh871mfMObpkzCgzkGl9trUjFcWZ7CwOi5oqZ5yr TSbTA38dnI13Wm/fDNZoaUuKH5HWRuxqTeGWeYvVZxWLttzsNra1Gh1aWlrbrlnql7w0Xcy91aas 0nNi5tne3trsYczdDls4W6lH3QvMmqLkVtrbPRHAW2+IxtudpVKqVk2YXlgFwoukiUqRMCLblfF0 ttjuX1gw94PgHxDUHL2JMP3qKZvlXVPEqylPel1xVKkzHp+tPf6TrHtnn4vhLoP9IieiJ+Z2R+EH OG2HRmKifeg8A4scpDRubE2OA+5B/hCFbP0KABTAvYRP4yTCHykwGX/m9/M0RLFFFRN8N8Gy5cvg 3Folx+ZUhYuL4aLR/cqOiTV+LUY58xnion9z9IWhcUftj+Z/xcj80H6HFTvSTtVJGAyKNhSNLtP+ 7vvujPB3L45RLjmWQyQodRaHMFQ3Nw1O+C0NIykn5QbtfYmEhdcTI6niOMN8ThBUN8KFFJjRwi+F zeaoWNmq0YQzFl2ptMi+SRpUYoqCoY6DNDQYRlRwLFlHb+ZRfdeb4VpVGoyg1Q4Lofk633vQnyqq +uhqYMzCGoNiSO2y0C0nYfMthKL5EZCJc/MM5TF/Ap+Da+/5P33MFzqaUXv99Lin8aicnJZHF/lO WX8kS/ebYqv9nUwaJKhg/gaf47NEjHYmEqpdqkRcWrRC1FPkD6BrixFySyvPwDFIHVobW2Ao8X8S AJuOxK/OCRUaMP4D0O1Xzq5AxHLQ4hD0g0FMVWuwfMIP0/Wk6tGA3oCONJGQ72Df6sXXPWnnN74v KTTya5dtnsnsa+G+LqiihRSXVJ4VMXnM1keupHOamyIuPyNJeO11uLi3sHc7nJnZw7TkmJ35mRi3 HLcajm+aaSyzqU7HdPZHbu/oV1fRV7HnPdVXFVn8gq/c++YtkkX8C6pLURaRMNqJ7vseW5gjgrdP 3kBkGbVq2QfUN1IFIwUQQ4hEJM6+y57H+M3j69eOfJ4c3qnlMMFnSNgqyvUvjdqF2DZjyzxN7tdJ KSdipIzTenpP1F+ft9Ri+lr2m1rky0ss8j8X0yUm+8447KXHpnXgep7CnL6try77DiW7yt93bGHN FwfmxCJFSyLzjSVIpeJfjNQpLPMuyVfUS9QMvXnVT0rHlwLTudU9LY/LNj61lLT1t3wX2aG/4pwT LPupLcCboUeb6ntaI3+Tn8HskPI749/PfPKel42hc8q0nLjdweCzNTlY5dHf8MMM/jW3y6SdWp0y wrL87cWcxxN9WiPNmzWk6MvuzhXs56ZMZ2WHKobXNt9emrlhnuIJ8NOstS8y1K8pCuDbaRqpcUCg u6fRml6CwPoLWJdYsqjgi7Z0Q0HxYQ62MTYz62qUpK4noT6vF7T1PoZnueef0WZNp40+he1HqwaY bJNDdLH4p8OSUmodJ0WSP2KGqSmF9uV3jN2HfCqpH0982OpCs+F7MVUs97TY2TR4vFHETY2LsByk 2LDwKioAcFWDQS94e0Z8mywswZ4X2ljzV8XHRdEwV8g5/WeV+RQVVgHrAIFEkFpEqzEacrFjSUSv Q8DJIFbWfBht1xY6Fxfx8P1PrqDPb4Mva7nH4Sdh+TZ7N0JkwuEysaJyvrZKGJA9FJcsigR6rbc7 10MlJfENMpiojxgglREwjjtR+r2V07dEHOxsaDTjeHJoYmDMV3IOmiXTgWiYQjr4pBid3p6pFOrE 7Tule/6dMK8BhLj4y+9c9081iUzVweiF6efunScn1VhsnAaN0J9j1m91wzXT9Myz2G+en5c5KSki tiJSFj1VvD0yLSMjuknXXHPxoL4Flq7mqMhkeJIg2i81gjaTgvSYmKOzZfCiPCqhNofJbW6nm7Hf NTVvWWS2nCS/PXvmj/dxAMZXBMx0IRgl2rIEatn1KqPAEesuz+7JSDJhGCdCYlH2QeDW6iiq6FTq VEqnQJ52oxhr5DESgakaRBZyQ7BBJ97BVlwHfiFwFC1BsSsEgvQkUj1qt8odAaCGgatn4LNtkOcR 76xrow7p8Z6GyTdK6reYMy+Ie7LXmO65B2T7lTx4LyVTBCC7aIbQrfZK9VUVsoPmB+pRHyUfNE7J HwkMV8nh0tPnhbNP5XHydPTs6Qu70u+5w8PcnXLOHD4WShSkuj1ipPixNk9jXhPP6paQ46TRjLpc 04PI/cxmqJPRNnVNw0ZvTNqQ2gbeyCGhMR5NHwH9jMCqEFtSFzzsZ9dLHZVkurieeMnaXyTZgPsR yxlK1F/tjjf4CZVGMByDufdt776Vy2hu1IyKgR6lE5JA4QD6VBUPSHRCbvtgcP1/OlAKI6+eCUux ERVGDLn8xyx0MkuHJbaWGWHVXpV5E+hUHqqc0wmaM9jv0wqzBNkdVTX5enTa1sTqhM6myYybnOP1 rLpIxrqpLJWUii0mk1LkbzAw9LjkmCnCw/xa4wLHjdZWD3K5FpadVS0ujYuNIma9PcTrXjCk+1Ui zRiUqUZrLjposrBezSHBZcXHaN5SzMoJ0RvLBbqpPIhKKQhbmFZaOWQqWM3k7gse60kl9+XUXpIu SNwRgBUQUKXXwBJxqqedbJ6DsXX5uBz9uSTRgB4iKBwUTUUlFbIlb6eY2ANLyK/arzgbsF7Odh8v dYp2KqEehUg+AZLks9y2EOE9XGaTX0M6OEa9MuTMyscJTXUdkqKuS5etatntp7lgUEtuzpRnpBnE TARCQaRBaNACXQijQQg0DHLgBJWNUqXlyLqCIhIKzEpKjIRDMEmcH6jbEYfBkH8XckU4UJCtJFCy --===============1602332364==--