From: Jon Olav Hauglid Date: July 21 2010 2:27pm Subject: bzr commit into mysql-trunk-runtime branch (jon.hauglid:3081) Bug#54905 List-Archive: http://lists.mysql.com/commits/114047 X-Bug: 54905 Message-Id: <201007211428.o6L9eNqU026776@acsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============8182471004881705128==" --===============8182471004881705128== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///export/home/x/mysql-trunk-runtime-bug54905/ based on revid:jon.hauglid@stripped 3081 Jon Olav Hauglid 2010-07-21 Bug #54905 Connection with WRITE lock cannot ALTER table due to concurrent SHOW CREATE The problem was that a SHOW CREATE TABLE statement issued inside a transaction did not release its metadata locks at the end of the statement execution. This happened even if SHOW CREATE TABLE is an information statement. The consequence was that SHOW CREATE TABLE was able to block other connections from accessing the table (e.g. using ALTER TABLE). This patch fixes the problem by explicitly releasing any metadata locks taken by SHOW CREATE TABLE after the statement completes. Test case added to show_check.test. modified: mysql-test/r/show_check.result mysql-test/t/show_check.test sql/sql_show.cc === modified file 'mysql-test/r/show_check.result' --- a/mysql-test/r/show_check.result 2010-05-21 13:36:59 +0000 +++ b/mysql-test/r/show_check.result 2010-07-21 14:27:38 +0000 @@ -1466,3 +1466,24 @@ t1 CREATE TABLE `t1` ( # Switching to connection 'default'. UNLOCK TABLES; DROP TABLE t1; +# +# Bug#54905 Connection with WRITE lock cannot ALTER table due to +# concurrent SHOW CREATE +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(a INT); +# Connection con1 +LOCK TABLE t1 WRITE; +# Connection default +START TRANSACTION; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +# Connection con1 +ALTER TABLE t1 CHARACTER SET = utf8; +UNLOCK TABLES; +# Connection default +COMMIT; +DROP TABLE t1; === modified file 'mysql-test/t/show_check.test' --- a/mysql-test/t/show_check.test 2010-05-21 13:36:59 +0000 +++ b/mysql-test/t/show_check.test 2010-07-21 14:27:38 +0000 @@ -1246,6 +1246,39 @@ UNLOCK TABLES; DROP TABLE t1; +--echo # +--echo # Bug#54905 Connection with WRITE lock cannot ALTER table due to +--echo # concurrent SHOW CREATE +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1(a INT); + +--echo # Connection con1 +connect (con1,localhost,root); +LOCK TABLE t1 WRITE; + +--echo # Connection default +connection default; +START TRANSACTION; +SHOW CREATE TABLE t1; + +--echo # Connection con1 +connection con1; +# Used to block +ALTER TABLE t1 CHARACTER SET = utf8; +UNLOCK TABLES; + +--echo # Connection default +connection default; +COMMIT; +disconnect con1; +DROP TABLE t1; + + # Wait till all disconnects are completed --source include/wait_until_count_sessions.inc === modified file 'sql/sql_show.cc' --- a/sql/sql_show.cc 2010-07-15 15:46:41 +0000 +++ b/sql/sql_show.cc 2010-07-21 14:27:38 +0000 @@ -649,22 +649,33 @@ mysqld_show_create(THD *thd, TABLE_LIST Protocol *protocol= thd->protocol; char buff[2048]; String buffer(buff, sizeof(buff), system_charset_info); + List field_list; + bool error= FALSE; DBUG_ENTER("mysqld_show_create"); DBUG_PRINT("enter",("db: %s table: %s",table_list->db, table_list->table_name)); + /* + Metadata locks taken during SHOW CREATE should be released when + the statmement completes as it is an information statement. + */ + MDL_ticket *mdl_savepoint= thd->mdl_context.mdl_savepoint(); + /* We want to preserve the tree for views. */ thd->lex->view_prepare_mode= TRUE; { Show_create_error_handler view_error_suppressor(thd, table_list); thd->push_internal_handler(&view_error_suppressor); - bool error= + bool open_error= open_normal_and_derived_tables(thd, table_list, MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL); thd->pop_internal_handler(); - if (error && (thd->killed || thd->is_error())) - DBUG_RETURN(TRUE); + if (open_error && (thd->killed || thd->is_error())) + { + error= TRUE; + goto exit; + } } /* TODO: add environment variables show when it become possible */ @@ -672,7 +683,8 @@ mysqld_show_create(THD *thd, TABLE_LIST { my_error(ER_WRONG_OBJECT, MYF(0), table_list->db, table_list->table_name, "VIEW"); - DBUG_RETURN(TRUE); + error= TRUE; + goto exit; } buffer.length(0); @@ -684,9 +696,11 @@ mysqld_show_create(THD *thd, TABLE_LIST view_store_create_info(thd, table_list, &buffer) : store_create_info(thd, table_list, &buffer, NULL, FALSE /* show_database */))) - DBUG_RETURN(TRUE); + { + error= TRUE; + goto exit; + } - List field_list; if (table_list->view) { field_list.push_back(new Item_empty_string("View",NAME_CHAR_LEN)); @@ -707,7 +721,11 @@ mysqld_show_create(THD *thd, TABLE_LIST if (protocol->send_result_set_metadata(&field_list, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF)) - DBUG_RETURN(TRUE); + { + error= TRUE; + goto exit; + } + protocol->prepare_for_resend(); if (table_list->view) protocol->store(table_list->view_name.str, system_charset_info); @@ -735,10 +753,17 @@ mysqld_show_create(THD *thd, TABLE_LIST protocol->store(buffer.ptr(), buffer.length(), buffer.charset()); if (protocol->write()) - DBUG_RETURN(TRUE); + { + error= TRUE; + goto exit; + } my_eof(thd); - DBUG_RETURN(FALSE); + +exit: + /* Release any metadata locks taken during SHOW CREATE. */ + thd->mdl_context.rollback_to_savepoint(mdl_savepoint); + DBUG_RETURN(error); } bool mysqld_show_create_db(THD *thd, char *dbname, --===============8182471004881705128== MIME-Version: 1.0 Content-Type: text/bzr-bundle; charset="us-ascii"; name="bzr/jon.hauglid@stripped" Content-Transfer-Encoding: 7bit Content-Disposition: inline # Bazaar merge directive format 2 (Bazaar 0.90) # revision_id: jon.hauglid@stripped # target_branch: file:///export/home/x/mysql-trunk-runtime-bug54905/ # testament_sha1: 0b6098ac427679cb772d217668af4c43c029e231 # timestamp: 2010-07-21 16:27:42 +0200 # source_branch: file:///export/home/x/mysql-trunk-bugfixing/ # base_revision_id: jon.hauglid@stripped\ # xn93j8pwxa1lvca5 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWa6hg1sABEvfgFCwWXf//3// 3sD////+YAnHfXu7XXY2geg5Vo56e5YNRpSnnucASSEEepqehqeingmNAKeKnqaZqDaNPVPSek9J oDQEpFJ+qeDRop6n4qNAGgGgAAAAAGgGSNDSSPKZBtI0ADQAADQAAAASIinpJ4hU/0JT9RtJNHpN lNPJqeIagyD1BiHqADjRkyMIxAMJoMAmg0DJk0ZMhhAYSSCYSYE0ApiabU9U/SNJPTKH6oaDQPUZ NNG1PUkJGQF73PWU/6LkfSqfc7GYbCa3ZF8HQZ7/P49PmeUb+vK7R/quBL+2d8Goq2b4Wtdj3R2j 1RijlfAO7FLummgEswebzaa5X991H4+CFnaq6gz44BUYMod8irtjYZYx4MI+22Azox9WM+Maf3tx OaQXsJBFAZjJLaB9+v5bc+v39F+GaDoXvW1NttsFr5UbLVv3QeBy91jq8MWtaSmCGSOeFQVpQjJB XBzLNcdC9Fa93Cuk5AgF8CR4FC6+mk7zt/raaiYji5eRdX10zvqPkRCY/QcPZmHvLS9HPw2fit5n W72/lSDzB7jl7Ce2fuVU9DTckyYT4ZWIt8MTVNcAUTRNeqwITeayyprFhTY2FMZihGUFcIOuHOQf gM7iqKQmPpw2dl0b91Vmza+NS+O+zJXKGublfPTt1HTR9FvpV3nekJYBf1tjbYxjG9Z8HGy48B5+ DPY8vfGj/Pe3jWjm8yqo+gjQcHAAh06zuMYZegifM0D08OGsIMRwiLh3FehbMdCx05tMfqoGwAYl yXa02HUb0V22XIuDtzJGtiOJpjkJNAUJGJNsAggYOag4cEHEcKqP1eWS9iX9VW4ggsi5cBW4mbRb KccdUAxsxtK0ThcKNj62eVQCxj7fk47Duw34fVLIFZLOgSIVc98NG2UKB5zQ94hp8Mb8cDBGVWud ZLpxFpayqWkxrGwzRTRcRMVuYXQgvWI5n0EZmBc6um5+IbzNwA/QeIje4oHoNRnKBqLaP4lsrx0h HZ2qWN8wfTvgWS4WCvNjFh9bzGBvNlnUzz7Gzno42vTj07DEpSmucSpQf+wmoFyRQdMqcCcMhong eP1UWt2Y41REbTMq2ZicRyJRY38iGQwjI8B2BQ0LIax48OdpsORLUNYGoIkzAhB/zwHbc1V0tXBY ay+cCO4/8VJMJHSsYL40fBIVzqHyAtktY2QsJT+VaosoujJrB1uDail9t1m4ufBxLdvdcFdt79Vw PJZvXSjqjDBJx3ssW2bUXk9DaY+ByIUry20c7WPcW2Dza54ephEXjGR5vqIydEYpAHDpWw3SzFvk dwx0JTfcP4ZTYRbKISGvuUyZI1ElKHHaQjGOGByNHXES8p6VuL4WuDMJLMh40daeKlnoZjypo4Jz NZIO2EX2GQRGTw1cEZkRATSUPM6hYVbOvnH29MO+kBDiAdJoUZ117ClLFZ6w4/kSmB+RWXCtsjE8 Kc/ivcdQ8piX1kgR5mh+/MyB8D8lYXiUjAmzDG3+wI769l6P7DMQv1agaLA0iWu6gXr8xoGTAyEx h80O7MhLDArECZNWQPzGxKV/6pJBiYFE5zfpWNQwPnB2wCFNo4fcFCWuWppA+fBzzbtqUHlLwwHk w4pEaBQCZsJiX0/FGrELFYijEzIuviXsLMZMQLCJEsIELARwKAoJhi8mJiKUSyj2TNRGlakC2zFI ZEzh8kvsNvxY4LOg1Oh7pfexBMmCvZOce+kl8hzlVFEzLwOi7/uPVvv1HwX3jo/PxkJ58xdC9iue 0f8r/4pKQvXy0E12HQfGQMhI1UjtpKsJRKOGNcqIGWVQRqA5JCDrYEoUIhnYd2ziXQVUtQb9Hgdl zCeM58CMKxxBxVbOvbBAxA9A3dQZMB75DYErIVvrYOmg6iJWHgsBduBa8VkbX3GzZQ/S2/xZuT60 tAEaAwcpTLkISWKAw0C8mPzW/ewPtLUKjA2pOt1eh6dBwQwDcq29ayNzHUttRNG2Y/YRQi+pyZD+ mYnt0DPuu7PSj5FbLkYX8MG/hJlNqIPG14lTLCElvany+Q4t2RUr0t6yg5acxzVKq3aGTXlbExmj W8OV/OdgEiWhAdrV48mVo0lhqswlORmvGSIurdruTN2fRxgNn4XFfM3YyMV1enqMmLa+2rQMwylT IzuDJbjg0ujJvNzUcQ/ymexq71nqgJ9SrcdGwlEgmsYUyDISxOWsUHiPQSmqQ3Eotmg3haCuj81S nBnOR5nCByccR9MLEiRDH288uQh22noqlV0Ulfr7+9l1wMvQrjaI9o7oBsibBKTkamGZXpkvY/mS FZIBgikVasiSa1xY2FmMtVyO44eCTxK5/Kzjv2CKBMsQdt4iakzxZPa28YvKxyZl7PVwpagKMyGk dT3VNwoZfrd5C0XF+my5TcfHQ7lA95tNF/g+AMnbu3AVWA9o3iYsnfZv7xevpEMjZ1MjaaQoKu+Q aKL8fsoV546Ba08ElAoJCsGAZt4pJxk0TQ55Veavnb3JmkxqJCLEGRX4Lp3tIxvyGAPUOd1RYcjj 1/1jtVlBd8NabxPSI4Z7zcZdMbwkypnKc5otjdLBNTCWcaSo0uZNVG9KkdZBOiLmgHPeInqNRWKA mpqs7zd+grObWakmBkzJxVyeyGUDypotAGA0bstOwRzJvxgdgeY1FTkBH/akg6RJykwa4mdwNgxp y1xkjuuckfRJZi3bUYvW1jkGVG28kWRYRzQcPYgn3smQzwhQtavAR7ywierQ7zMId2J6Z/fgYw81 YkCD3ol6NTSYcQrWsvrSJJdGAiPqy+IemGlCBqJC+wKaFVRLiEZjFrHNjrzscIPGpSiFM+qtGkSq 8SvWXrNyl4gKwsLrpaV20BahOg+KNcsoAxrQ7yhED67LYXiuXVqDnd67G23huiCDUZ2bmoGtPGw+ AWii5ibHKHmrlRbFBdBV3utbZqBSJGa6EPlMcQX3eNqkyoOnaydQTPCYXAyek8h8nI8NsjmFSpUT uJqT15MLzbX77nadLdxHcCYZamlq4EbUQQ1dgqrUUi/jW56Hewu7Qrz91BH3KWKSx4OCaYsHFwz6 mCwTdZH1FmBbvXHWoh1Ie+It5DAmI3mAZiGXQ6q3HiQ/SruTCivtHCOBw0KuGP4W2GZuXrJHYI+J xOOIaoCIlbtF3/8cOY/+LuSKcKEhXUMGtg== --===============8182471004881705128==--