Bug #52593 SHOW CREATE TABLE is blocked if table is locked

           for write by another connection

The problem was that if a table was locked in one connection by
LOCK TABLES ... WRITE, REPAIR TABLE or OPTIMIZE TABLE, SHOW CREATE
TABLE from another connection would be blocked. As SHOW CREATE TABLE
only reads metadata about the table, such blocking is not needed.

The problem was that when SHOW CREATE TABLE tried to get a metadata
lock on the table in order to open it, it used the wrong type of
metadata lock request. It used MDL_SHARED_READ which is used when
the intent is to read both table metadata and table data. Instead
it should have used MDL_SHARED_HIGH_PRIO which signifies an intent
to only read metadata.

This patch fixes the problem by making sure SHOW CREATE TABLE uses
the MDL_SHARED_HIGH_PRIO metadata lock request type when trying to
open the table. The patch also fixes a similar problem with the
mysql_list_fields API call.

Test case added to show_check.test.

1 files changed, 17 insertions, 0 deletions

diff --git a/mysql-test/r/show_check.result b/mysql-test/r/show_check.result
index b6207d830d8..9ba1a0446dd 100644
--- a/mysql-test/r/show_check.result
+++ b/mysql-test/r/show_check.result
@@ -1448,3 +1448,20 @@ DROP USER test_u@localhost;
 SHOW CREATE TABLE non_existent;
 ERROR 70100: Query execution was interrupted
 End of 5.1 tests
+#
+# Bug#52593 SHOW CREATE TABLE is blocked if table is locked
+#           for write by another connection
+DROP TABLE IF EXISTS t1;
+CREATE TABLE t1 (i INT PRIMARY KEY);
+LOCK TABLE t1 WRITE;
+# Switching to connection 'con1'.
+# This statement used to be blocked.
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `i` int(11) NOT NULL,
+  PRIMARY KEY (`i`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+# Switching to connection 'default'.
+UNLOCK TABLES;
+DROP TABLE t1;