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.

1 files changed, 33 insertions, 0 deletions

diff --git a/mysql-test/t/show_check.test b/mysql-test/t/show_check.test
index fa003c2fe69..89a43f24296 100644
--- a/mysql-test/t/show_check.test
+++ b/mysql-test/t/show_check.test
@@ -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