Fix for bug #50908 "Assertion `handler_tables_hash.records == 0'

failed in enter_locked_tables_mode".

Server was aborted due to assertion failure when one tried to 
execute statement requiring prelocking (i.e. firing triggers
or using stored functions) while having open HANDLERs.

The problem was that THD::enter_locked_tables_mode() method
which was called at the beginning of execution of prelocked 
statement assumed there are no open HANDLERs. It had to do 
so because corresponding THD::leave_locked_tables_mode()
method was unable to properly restore MDL sentinel when
leaving LOCK TABLES/prelocked mode in the presence of open 
HANDLERs.

This patch solves this problem by changing the latter method
to properly restore MDL sentinel and thus removing need for 
this assumption. As a side-effect, it lifts unjustified
limitation by allowing to keep HANDLERs open when entering 
LOCK TABLES mode.

mysql-test/include/handler.inc:
  Adjusted tests after making LOCK TABLES not to close
  open HANDLERs. Added coverage for bug #50908
  "Assertion `handler_tables_hash.records == 0' failed
  in enter_locked_tables_mode".
mysql-test/r/handler_innodb.result:
  Updated test results (see include/handler.inc).
mysql-test/r/handler_myisam.result:
  Updated test results (see include/handler.inc).
sql/mysql_priv.h:
  Introduced mysql_ha_move_tickets_after_trans_sentinel()
  routine which allows to move tickets for metadata locks
  corresponding to open HANDLERs after transaction sentinel.
sql/sql_class.cc:
  Changed THD::leave_locked_tables_mode() to correctly restore 
  MDL sentinel value in the presence of open HANDLERs.
sql/sql_class.h:
  Removed assert from THD::enter_locked_tables_mode() as we
  no longer have to close HANDLERs when entering LOCK TABLES 
  or prelocked modes. Instead we keep them open and correctly
  restore MDL sentinel value after leaving them.
  Removal of assert also fixes problem from the bug report.
sql/sql_handler.cc:
  Introduced mysql_ha_move_tickets_after_trans_sentinel()
  routine which allows to move tickets for metadata locks
  corresponding to open HANDLERs after transaction sentinel.
sql/sql_parse.cc:
  We no longer have to close HANDLERs when entering LOCK TABLES 
  mode. Instead we keep them open and simply correctly restore 
  MDL sentinel value after leaving this mode.

5 files changed, 42 insertions, 15 deletions

diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 6f207ccb00e..a4d9eab2685 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1438,6 +1438,7 @@ bool mysql_ha_read(THD *, TABLE_LIST *,enum enum_ha_read_modes,char *,
 void mysql_ha_flush(THD *thd);
 void mysql_ha_rm_tables(THD *thd, TABLE_LIST *tables);
 void mysql_ha_cleanup(THD *thd);
+void mysql_ha_move_tickets_after_trans_sentinel(THD *thd);
 
 /* sql_base.cc */
 #define TMP_TABLE_KEY_EXTRA 8
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index ae6d1b1a0a0..bdf83ebe8ec 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -3312,6 +3312,22 @@ void THD::set_query_id(query_id_t new_query_id)
 
 
 /**
+  Leave explicit LOCK TABLES or prelocked mode and restore value of
+  transaction sentinel in MDL subsystem.
+*/
+
+void THD::leave_locked_tables_mode()
+{
+  locked_tables_mode= LTM_NONE;
+  /* Make sure we don't release the global read lock when leaving LTM. */
+  mdl_context.reset_trans_sentinel(global_read_lock.global_shared_lock());
+  /* Also ensure that we don't release metadata locks for open HANDLERs. */
+  if (handler_tables_hash.records)
+    mysql_ha_move_tickets_after_trans_sentinel(this);
+}
+
+
+/**
   Mark transaction to rollback and mark error as fatal to a sub-statement.
 
   @param  thd   Thread handle
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 7c935d376f9..e6ee7139b24 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -2683,17 +2683,11 @@ public:
   void enter_locked_tables_mode(enum_locked_tables_mode mode_arg)
   {
     DBUG_ASSERT(locked_tables_mode == LTM_NONE);
-    DBUG_ASSERT(handler_tables_hash.records == 0);
 
     mdl_context.set_trans_sentinel();
     locked_tables_mode= mode_arg;
   }
-  void leave_locked_tables_mode()
-  {
-    locked_tables_mode= LTM_NONE;
-    /* Make sure we don't release the global read lock when leaving LTM. */
-    mdl_context.reset_trans_sentinel(global_read_lock.global_shared_lock());
-  }
+  void leave_locked_tables_mode();
   int decide_logging_format(TABLE_LIST *tables);
 private:
 
diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc
index 9f365d0cf2f..4a69b46ddb7 100644
--- a/sql/sql_handler.cc
+++ b/sql/sql_handler.cc
@@ -837,3 +837,27 @@ void mysql_ha_cleanup(THD *thd)
   DBUG_VOID_RETURN;
 }
 
+
+/**
+  Move tickets for metadata locks corresponding to open HANDLERs
+  after transaction sentinel in order to protect them from being
+  released at the end of transaction.
+
+  @param thd Thread identifier.
+*/
+
+void mysql_ha_move_tickets_after_trans_sentinel(THD *thd)
+{
+  TABLE_LIST *hash_tables;
+  DBUG_ENTER("mysql_ha_move_tickets_after_trans_sentinel");
+
+  for (uint i= 0; i < thd->handler_tables_hash.records; i++)
+  {
+    hash_tables= (TABLE_LIST*) my_hash_element(&thd->handler_tables_hash, i);
+    if (hash_tables->table && hash_tables->table->mdl_ticket)
+      thd->mdl_context.
+             move_ticket_after_trans_sentinel(hash_tables->table->mdl_ticket);
+  }
+  DBUG_VOID_RETURN;
+}
+
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 1906040d5c6..b0d8614dc84 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -3283,14 +3283,6 @@ end_with_restore_list:
     break;
   case SQLCOM_LOCK_TABLES:
     thd->locked_tables_list.unlock_locked_tables(thd);
-    /*
-      As of 5.5, entering LOCK TABLES mode implicitly closes all
-      open HANDLERs. Both HANDLER code and LOCK TABLES mode use
-      the sentinel mechanism in MDL subsystem and thus could not be
-      used at the same time. All HANDLER operations are prohibited
-      under LOCK TABLES anyway.
-    */
-    mysql_ha_cleanup(thd);
     /* we must end the trasaction first, regardless of anything */
     if (trans_commit_implicit(thd))
       goto error;