From: Jon Olav Hauglid Date: July 22 2010 9:10am Subject: bzr commit into mysql-trunk-runtime branch (jon.hauglid:3082) Bug#54905 List-Archive: http://lists.mysql.com/commits/114119 X-Bug: 54905 Message-Id: <201007220911.o6LH6O5o008127@acsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0182265099525877853==" --===============0182265099525877853== 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 3082 Jon Olav Hauglid 2010-07-22 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-22 09:10:35 +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-22 09:10:35 +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-22 09:10:35 +0000 @@ -649,22 +649,30 @@ 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= TRUE; 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())) + goto exit; } /* TODO: add environment variables show when it become possible */ @@ -672,7 +680,7 @@ 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); + goto exit; } buffer.length(0); @@ -684,9 +692,8 @@ 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); + goto exit; - List field_list; if (table_list->view) { field_list.push_back(new Item_empty_string("View",NAME_CHAR_LEN)); @@ -707,7 +714,8 @@ 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); + goto exit; + protocol->prepare_for_resend(); if (table_list->view) protocol->store(table_list->view_name.str, system_charset_info); @@ -735,10 +743,16 @@ mysqld_show_create(THD *thd, TABLE_LIST protocol->store(buffer.ptr(), buffer.length(), buffer.charset()); if (protocol->write()) - DBUG_RETURN(TRUE); + goto exit; + error= FALSE; my_eof(thd); - DBUG_RETURN(FALSE); + +exit: + close_thread_tables(thd); + /* 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, --===============0182265099525877853== 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: eaef9dd9553996a764c623bed2be117c05999c07 # timestamp: 2010-07-22 11:10:39 +0200 # source_branch: file:///export/home/x/mysql-trunk-bugfixing/ # base_revision_id: jon.hauglid@stripped\ # tx4c71abu8tbihhk # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWQBdbRAABEXfgFCwWXf//3// 3sD////0YAnN307lovN5pEO6dxtqK422zM3StLbayEkiJoynomaaGEJgUeCmTaj1NADTRoA0BJFM QyJiYlTynsQp6eqP1RppkNDTCaGgaB6IGiNTT0SnqfqgM1DIAZGAjINMgAHqNDQSJEamg1MjKbKe piaeo2p6mmI9QaDIMg0ZA0OAYRhNMQwCAZADCNMmTCMBDQSSBNAATTQmpgJpkjJG0nqZHqeo/VNk gwj0hIWEXfY9VqNkfe9FrdQY8xFazOHs9k7v7ff0Hya7oqd0GR+1eGms4du7beKCHrvoa4Dm1ncM sqAOlA9DdPJHjpVDr3YVjVTqBm7AKbQyZ1yFVjjGbuDVY3PNhpqiqY2E+E5+deB2ArmAURGQyRqB 88/87qcPn667JOPWvmttNtttC2vEg2cm3ZTBlgm5PLcVHmrrrnSkESJsseZ3HVOCJeOcxtWSnKnB 8zLas5UT6UiQF5Y+ffOw3/rWaCIko3dpzrwzWXzHyJAiPiOHszD3khb06J/sqBqQntUvRoi1g2uU LUW4UEMIQNWERkYNtKyKQq4ZSjBNATPM7cZhI9pUEqHaJRozXEJzxA1QCEUZOGMIO8Z1KtKITH+o v45YbeGNXmaamMtw4rF1Nw1tvfyuHTA3FSi7OKLUNpLJrbG2xjGN7RrgyYDQSOpyeVt/lsRv7KG2 SYZHt6CRRvjEYBwUgEGuc85IGP3CLORuIiE3bduoIGObkpTkYKqZVTcyZ7vgoC+AMJarMyZg4laI Z4lpU0KReYRSwMM8HlsMyhIBhLJEMAmQMHMGxkLOQIqUh4oAK0LgIEkSVhGTtVARjVNEtjgwzEnm wUeywUZVWy8MwCTHn9jl4jqOG7DlXkRk+doVk2fCFPl1ofOYEhyIIImtfuWbBKGqwn54eD3aKijU YLkxWq2Ga+mjTiCJkuTCxLjuMleTnveoXhvLXAGCFwjWX00CVYcD6DiuPQUtW70S4wB3XWR4QFiX kS/2MZvuIm80hzJCLcdW6rZ7TcxmYjpitiJGAKtzzEyJeUN1ZwKaS3eSgdp0+nRpjj0iLdi/TQyL RyMhFEow4ajjeIyOwfgaVNuhrBqCIdsCRKoaISC76K4whYU9+I9lGJ3DdSokfDmYS0iSP+JlbtX9 601WcaYHXYDtOvArMTOvXXTq357yVNTO83FzPjhOoHYMnYfDpgVURXYtWuO4sm6UFye2qha44O6+ JkbqItu0vGKjI3D+45kK9NJ83bh7himVbP4LtghQkF3veZMiZY6TrKJZIoOQxR7Cc4ardi1ykERp 8XuLRx40UmZB/TfU/DA0MCAxqdae/Kd6LgeZCIVXMcxEZZxoGvOXlItGOZnfc1+Rmalx1k8qa42D Dh1DzDfuRWiICU5IdbqCgqqOrjsccvJDmDmoc5TIGOuHiQQREL+sMulPcNOol1Kh4wm5rVHFbjaH TZEvMUBHKYzm2TEEpnYqy0SobBcbGNv8Ajrq0XkfwKyF1NQNFAaRJromF5fUaBpg0JnuxfhkJYYE 4gWFalA7xsS2f+qtIiu9iZ6veYlyP23VBf7QRbmXm54ws73NYan5/K95rrUUjym4Lx5MOKRGkKQJ m4mJfT80WYBJSpYmYlt0S5hZDJiBIiRJECEgRwKQUEwxcTExFRJWSabsx3bpMvV4UhouGbpS1nB8 malvINET5q+dk0yYIy15OeH7MCv9R3Wci4D1Hg+76TLyntl+fnPLbvNgYbVvzX3kfnZ70lQa6UhQ 5oRF8CKvuPO5QmUSTONa7UHFQYleHmrIvwCuM0Q4sP9Fgl6xeuWYc4Gh+UrS8mR6WXxzKTaWMCBn xO/NRQOGgbDe7rOjYkv6yO4KGEs9mLWMTc8sN2iJGZjYGZ5rA7xp0mm887OjaPX/JLAB2AWM9J72 eDklhegOLhepj9lw4MFVCiFawNcnpdwqTwcIYI6FOkTLlSo6aPRFiGoIIRZQjkUuhywE/zHdwY9N t/Uj5Fcpous0ccdB70zJzi6yqUE3M5JUMt+O+ZbxZHGiwpnNKbcucjNyuKbTlZNdUgp3Gk24Xg7n 7jIBClnQHzbDVJW0lsqNGnPiH1tdvD5BW5iShsn4cYDRykV4qrZFFxd2srp1VTVoMwykmRxcN86b ux6R5nxteXy7cnzx7fJAVW1aOPi0yuIm62FQg4Eqzx7Rc49+xM9MiImPQcSZMHxhPL8FcTgrNp9T VAXHGwfkEb4dgjNEhjycMmMh2XnGiVHiksd3j72XPAz4q86htANYGglJxdzDMsKEvc/2HNSOxalA G+1I0p7eYAzvJjvO/kU6xd3bepJ4la/oly4zDqUySDqoUmcLF7VWjFpVHFmWzTXakQFpmQ0RiN8Z hFBz8nbhZVU+meVWXHG+POonJl3nEGTsGS2K0wGcbBsGOdyf1YswtXihC8T8CY1MQgKO+oGRBez0 QI+J+QtE1yScTlQRdXBtbmknMmiaHdlOou91OtM1bFxskgyKeDsaRjsyGAOs8RD3+CKDkcfD/uu9 SpF2Q0TdRwy2NTD67KgeYz3ye0W0wXJME1KErBpKbETG7VpIrmoqFoQDnvETsLCqKAmpqqdxv9hV ObSsSYGTMnFTk9kMoHrp0WgDAaN0VnQI7Sb8IHMPEwInSCO3CkHGJOSTBrOzoBsGNOTW4UN7BCXa kshb9UYPWOrjkGVTa8kSiwjbxQUdTJkM8JlGt8ZAgduZ2GQQ47Drs9t5shFaGe3al26X0PQYGuLu eTlqUcCIC3XXc/eblQrEHgtfKIXWNfvHo30m7Ews3+uqN4tSxWpW1epysNi7sKPDF8KXQTyK4GIi Muh7OXDMM4zosP3e3puENtZTXQy+qAzFUtbNl4rF5KBwu8tbbbv5oggxFjMTUDWxuMMQrZrSSoaQ 1VZQWicTcRatoxolAIOeMZwHN4FtoL+/j1CLBTqGtuZPMTVBaFNgOoJc1El+zo6+iw5BcXFBP3i1 SOFti9byYuS2UtN7MVZgTGr7pfzlV5E0O73BTVxbGrfO/xCIIqgvEbGXaFPks/FRxSWPS4VpiY4t GfUXq9N5UHiSvK+OSgHkcfbEWxC8msRDJbGCXqPQq8OJCziwv6DCNjbMpcMfCmRiarvKD4Gx0bAp wJ39+n/i7kinChIAC62iAA== --===============0182265099525877853==--