From: Jon Olav Hauglid Date: July 23 2010 8:45am Subject: bzr commit into mysql-trunk-runtime branch (jon.hauglid:3084) Bug#55498 List-Archive: http://lists.mysql.com/commits/114207 X-Bug: 55498 Message-Id: <201007230846.o6N8kcOk013297@acsinet15.oracle.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============2267069985751040310==" --===============2267069985751040310== 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-newbug/ based on revid:davi.arnaut@stripped 3084 Jon Olav Hauglid 2010-07-23 Bug #55498 SHOW CREATE TRIGGER takes wrong type of metadata lock The first problem was that SHOW CREATE TRIGGER took a stronger metadata lock than required. This caused the statement to be blocked when it was not needed. For example, LOCK TABLE WRITE in one connection would block SHOW CREATE TRIGGER in another connection. Another problem was that a SHOW CREATE TRIGGER statement issued inside a transaction did not release its metadata locks at the end of the statement execution. This happened even if SHOW CREATE TRIGGER is an information statement. The consequence was that SHOW CREATE TRIGGER was able to block other connections from accessing the table (e.g. using ALTER TABLE). This patch fixes the problem by changing SHOW CREATE TRIGGER to take a MDL_SHARED_HIGH_PRIO metadata lock similar to what is already done for SHOW CREATE TABLE. The patch also changes SHOW CREATE TRIGGER to explicitly release any metadata locks taken by the statement after it 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-07-22 09:10:35 +0000 +++ b/mysql-test/r/show_check.result 2010-07-23 08:44:55 +0000 @@ -1487,3 +1487,30 @@ UNLOCK TABLES; # Connection default COMMIT; DROP TABLE t1; +# +# Bug#55498 SHOW CREATE TRIGGER takes wrong type of metadata lock. +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (a INT); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1; +# Test 1: SHOW CREATE TRIGGER with WRITE locked table. +# Connection con1 +LOCK TABLE t1 WRITE; +# Connection default +SHOW CREATE TRIGGER t1_bi; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_bi CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1 utf8 utf8_general_ci latin1_swedish_ci +# Connection con1 +UNLOCK TABLES; +# Test 2: ALTER TABLE with SHOW CREATE TRIGGER in transaction +# Connection default +START TRANSACTION; +SHOW CREATE TRIGGER t1_bi; +Trigger sql_mode SQL Original Statement character_set_client collation_connection Database Collation +t1_bi CREATE DEFINER=`root`@`localhost` TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1 utf8 utf8_general_ci latin1_swedish_ci +# Connection con1 +ALTER TABLE t1 CHARACTER SET = utf8; +# Connection default +COMMIT; +DROP TRIGGER t1_bi; +DROP TABLE t1; === modified file 'mysql-test/t/show_check.test' --- a/mysql-test/t/show_check.test 2010-07-22 09:10:35 +0000 +++ b/mysql-test/t/show_check.test 2010-07-23 08:44:55 +0000 @@ -1279,6 +1279,52 @@ disconnect con1; DROP TABLE t1; +--echo # +--echo # Bug#55498 SHOW CREATE TRIGGER takes wrong type of metadata lock. +--echo # + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +CREATE TABLE t1 (a INT); +CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW SET new.a = 1; + +--echo # Test 1: SHOW CREATE TRIGGER with WRITE locked table. + +--echo # Connection con1 +connect (con1, localhost, root); +LOCK TABLE t1 WRITE; + +--echo # Connection default +connection default; +# Should not block. +SHOW CREATE TRIGGER t1_bi; + +--echo # Connection con1 +connection con1; +UNLOCK TABLES; + +--echo # Test 2: ALTER TABLE with SHOW CREATE TRIGGER in transaction + +--echo # Connection default +connection default; +START TRANSACTION; +SHOW CREATE TRIGGER t1_bi; + +--echo # Connection con1 +connection con1; +# Should not block. +ALTER TABLE t1 CHARACTER SET = utf8; + +--echo # Connection default +connection default; +COMMIT; +DROP TRIGGER t1_bi; +DROP TABLE t1; +disconnect con1; + + # 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-22 09:10:35 +0000 +++ b/sql/sql_show.cc 2010-07-23 08:44:55 +0000 @@ -7779,6 +7779,10 @@ TABLE_LIST *get_trigger_table(THD *thd, bool show_create_trigger(THD *thd, const sp_name *trg_name) { TABLE_LIST *lst= get_trigger_table(thd, trg_name); + uint num_tables; /* NOTE: unused, only to pass to open_tables(). */ + Table_triggers_list *triggers; + int trigger_idx; + bool error= TRUE; if (!lst) return TRUE; @@ -7790,35 +7794,35 @@ bool show_create_trigger(THD *thd, const } /* - Open the table by name in order to load Table_triggers_list object. - - NOTE: there is race condition here -- the table can be dropped after - LOCK_open is released. It will be fixed later by acquiring shared - metadata lock on trigger or table name. + Metadata locks taken during SHOW CREATE TRIGGER should be released when + the statement completes as it is an information statement. */ + MDL_ticket *mdl_savepoint= thd->mdl_context.mdl_savepoint(); - uint num_tables; /* NOTE: unused, only to pass to open_tables(). */ - - if (open_tables(thd, &lst, &num_tables, 0)) + /* + Open the table by name in order to load Table_triggers_list object. + */ + if (open_tables(thd, &lst, &num_tables, + MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL)) { my_error(ER_TRG_CANT_OPEN_TABLE, MYF(0), (const char *) trg_name->m_db.str, (const char *) lst->table_name); - return TRUE; + goto exit; /* Perform closing actions and return error status. */ } - Table_triggers_list *triggers= lst->table->triggers; + triggers= lst->table->triggers; if (!triggers) { my_error(ER_TRG_DOES_NOT_EXIST, MYF(0)); - return TRUE; + goto exit; } - int trigger_idx= triggers->find_trigger_by_name(&trg_name->m_name); + trigger_idx= triggers->find_trigger_by_name(&trg_name->m_name); if (trigger_idx < 0) { @@ -7826,16 +7830,22 @@ bool show_create_trigger(THD *thd, const (const char *) trg_name->m_db.str, (const char *) lst->table_name); - return TRUE; + goto exit; } - return show_create_trigger_impl(thd, triggers, trigger_idx); + error= show_create_trigger_impl(thd, triggers, trigger_idx); /* NOTE: if show_create_trigger_impl() failed, that means we could not send data to the client. In this case we simply raise the error status and client connection will be closed. */ + +exit: + close_thread_tables(thd); + /* Release any metadata locks taken during SHOW CREATE TRIGGER. */ + thd->mdl_context.rollback_to_savepoint(mdl_savepoint); + return error; } class IS_internal_schema_access : public ACL_internal_schema_access --===============2267069985751040310== 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-newbug/ # testament_sha1: 9f8ecd40cc4cdbde896b6765fb62c2bea821dc8a # timestamp: 2010-07-23 10:45:02 +0200 # source_branch: file:///export/home/x/mysql-trunk-bugfixing/ # base_revision_id: davi.arnaut@stripped\ # k6317tm931nq6pr7 # # Begin bundle IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWUtcjtsABPPfgEAxWXf//3// /uD////wYAv9r7tNuJdzuzbSpWBNqdzq5qK6DqTq63FlKgJJIQmntIQek02hqZNMhoAAaBoNDQNA kkJoGmgEJiU/KmntSPSPKPUA0AG1NNqAaHAMIwmmIYBAMgBhGmTJhGAhoJEiYgRDTInonqnkA1PU zU2k8UZBoDQxNNBwDCMJpiGAQDIAYRpkyYRgIaCSIIBNGmhoNTQaVPaYFQaPKPU9QPUPUPU009RO TD8yZ/B+R8Ame81nKXr6DRrgMGQ/hHudd3J4grx7nJeUz9/HKyHJzlh13528hulOLyDzzdlW2bmp JMG+VE6ZAXYBc5eFvhy5K3Nbb4RbbGDq3VQrsiSCGAyinFGT7sFR0twiO3HTTb3sYM2ujOf0xu0U Hy7EgoQiskZZiQlbLRlIR1dtpZVOmZmZMyRJiLgiWxTo58MEbYh8lxeX2v5ZXEQ5RyVgi1TGslgy zlEYujHROJonQ3Zg8C8WJA2DRMiEXCAFyk4cxQC19xCeTtb26oWsZ1BC8qhgue5flfYWGChYHVz4 Mh3ylQcQHUIGI6X+RgkMke7dUHAI4HEv9NX25xhNjlEc8/gHQnnas60+dJlwmyvIOBFzIeymFK2V QxywtK7bNl7D3wtgxGiznwCsYNL+PixXM71LeGCdcnTnnldWn02tXRa59eVQWZWYSqINUY5B4gy9 Y0REcSzFQVwrXqU88IY2e5ZRKMY0ZtkZZ1ydc6SblwgRnvM5nCaDRvrpOXzV7Mj0zpHTVdWmW8lN oWr1NjaaaaY229Yz2V05zVHDkeHs6PPswMPnrx8Y+5ZozunXflKcaULQgHAAx+kprqIu+9CdwaK3 KJH0efp5woYlSI5jnMNN9Sao+Jk6C8+Cy6lbnkzheTKMQUBLQjTMHIuQo7JrkvhJAng8AxLg7iYB FmAIDjA0+9PIUS918YoyJnKPEnWVAE4RKYCJJihEFX55FiZe7pUWZ8ikCN5Np3FdRIk3lIDRYKte isArY7pOMV+vM4Wm/CgvVuJTVVjaF1g7Zq68jao3lRNbEXokYRuYJdKpq+yBcn9b/yO7vKjGoyhv d3HbQmBOYmCrMnSvV9ViYsfDKFcKJaA4MAZFKwacxXgxE5YbnfyVsDJWEVxGVNVBT2mstwFXe+Bm W++ZK5s5VMXM6T1kY4iIl2CrWFUITg3UPLKV+4nEazqrOZQRjZA0uUZu2o1DnIr392k7lr6xG83a MaExltZDLA4PRvTUZiJmFg/QRbZOFbh6cMmYNA1gOnYa7/9xJRlRXgpCKNYU/8YE2cyiSoWwXKQj 4LhiGipzAHPbHZWMXnLkTvxLNZpbKuV2EYFK65c1jJGJ0+FdMDMbSctr0bL9A1OJ1rj7RxTbk9Tm 8Y3nTgAdoj8OcMOy0YsLa4o1sikZCY8GlTJFJHA3DcTE9S5QM6eGmWzYngXhct4xMqeIyKGCZzcF EBFDyu0qLFgSgOfjNEyQbREhFLQJFygRLqO9Nmoc4aR6SF5LQOnxLzIuLGKn1RbBDmZQsdDhGUIp CHRzCoqVd3o4DCc+1yy8Dq3tfPOLUrV2cO4vOsjuLplxf1hzIzk6ZzI3FU+pd+BifWeX1Ld0i9ZV YJpGpAawvNZwGlSPwChRIV2hNsbHQPcgJ+SRaYQexFT9BkgGNCmDAPipCsXGB7C8IQKGmCZ8nYJp CyZ7zoD2/CqTQLmNQYjFgfD4mkkH/QEWEKIJSFMmMhESSuELafUgKFo33Ng6MrEfc160BVuNZkic zVMsN5+P4uLYcCdZAW5skaSgHmQpaoMNiEQCiKklSbla8GEBI2j0SSFIkIXePejhFgFgoUkyRoFI WIzY0MGQcT2vLyAUmAmCogQQGBakJ6wYwM0MUQlQaMG2msGVQKSMDix3i7DUOnkBeb9kuw9Z4j6S RPqJTXjVQYvPU93T+9bsafiy8uUJrMePS8COr5RQBQDOZh3iGYmmhAZ3ZpIZlkNSu4xvPRxTVbL3 41QRH/X9N6QSCuaCbjQiKKVA59V85kRS+cDTQw7ph/pm5cg2rsCbK8uvCedIUPxQd5InegokDDHZ WJepB4yzUdQuXp3mJxPX6qiUdp0IOIHM3Gn+xUfrBUAeCZE0YDSPnuOUSnoHyYSyyYmthuePOOCs OS6jLLXxPZbaciPZaek0SvSGCF5m0ppSlJQwhATKUNKA4pAdDWvXUhEghYl4xjQDabwx5SSZGchT 5yGtygwx4thfcd+tGdKGHkhFzsg1LZV9Y3lHWXF2tBO1+gf1vmS50fWVxyghYYmzqjVxbEc8pFE5 76guYT3a9JJttIOLLsIy1PwY89BsN6lO6ggUnI9FoXFV9jverBa5QVp5a2gDac0iKW0Bebaem8tp Z2erEv9w5j12b9zY1FtiCeVVu9uv8nLSv4MblvQbtC+z8zhl2wxOo7RkZMavTQw5IuMsSHxc05oZ xmds1cLN03by3wk9Onq/OiQoalRaQS+LVErmFehzqZNUgyKLjvDMVosm8UZ8RfhJiMVKUpIJr5SC nIFnOtJaausTjz7G6cjygPyXjG5dswGPTr5nO0qdhT9DoVMkNikmiktWz2XukeUxGYZrxNg7AQc2 sOvPYJOLwZhkZSSkQXqSW0kkblX8hkjXT1kzwYzAIBCMkx7zowiQsyzbN+mWaUBLRCCgS2kwpSVf FBJVU0IgZnMU9Umoqmz3EuLYB7dONdyQqmGBhmqlWvOsQ2CfnW4+IOsOp+yy5VOPDM7xED5DUzXs PIGT7eq8mwVsCzTeDLS4nu6hd0AvMPMoLHwLg7hERU74BUOIjJl6PmIh91I7kHSKaTKaAeJyiDAQ C1N2JKJjCszOWSHEuvyGkNFhwwzBaWVn3jSCwLenW3HVpwTAHYclNQ/RYe5ESB5+H/MFwZWoO6Gs Bvedgh+WPA0mr26i60Knka59Cewkt51ZZuUCBlnNziZA0JMYpKC9byouguq00qDeAoUQccKpARCY 5CktLpoFQc2p+mo5O1ekxvqnnoAbYNNqDKFJo5spzVD0ZbtqQzCwGuz1sJBhISCMIS+G8VjIYCiM CKov5gF1iTkIYsseeoRcFhMYmKjWjwUCnjjV/ML2oCgUUanWGxOdoa01jP5c1PyZC/Ug38E7Cq8G TIZQD9FYTa2ooCB8Gp5gfzwPdp5q5ThQLPHFBnbkXIyYZps3a5Zm4pUVBJkBEYTjKSls4cftNFYg buTa9rgskY/UPSPA3GKNzpI7dFTPlyT5+BOjJcDUg2Npq9XWg5GmbUJ/F9bRqeKgpdTJWjYJx2V5 rXrotvhv3X4i07Id+2xkrEjgZNa2u2Y2SKu2dhVX2YHS1zYrtbbeXe7CUiRvcBkNGx36oIagbUdb BiJsIqEjAxCkXbOi1gZSoIPbN5QeTSeMidbpK6CQ1aZD8piJZDEebcsAvVceNaCSxxE1mUSMexmh FkEJU7hpULMRu73AWPCGk0GInvFpCAeiYXxYwWrzvhDKzIkXgwwgYYtciuXQtpkppoxesWKzL5rr pCbOPuiVbd/Pf7VciXFZepr7q/ar+CntSVwiqeIZyFUmWgoLymUrTN6JOug4xt8qzxJ4GupQDyOv 8IKQtojMnqLREgxWKYDeYe5lKgdLxGDbfr5kCg5MHyGEco8dpVFoD8VM1m9e8mcDfcERFe8qt/+L uSKcKEglrkdtgA== --===============2267069985751040310==--