A pre-requisite patch for the fix for Bug#52044.

This patch also fixes Bug#55452 "SET PASSWORD is
replicated twice in RBR mode".

The goal of this patch is to remove the release of 
metadata locks from close_thread_tables().
This is necessary to not mistakenly release
the locks in the course of a multi-step
operation that involves multiple close_thread_tables()
or close_tables_for_reopen().

On the same token, move statement commit outside 
close_thread_tables().

Other cleanups:
Cleanup COM_FIELD_LIST.
Don't call close_thread_tables() in COM_SHUTDOWN -- there
are no open tables there that can be closed (we leave
the locked tables mode in THD destructor, and this
close_thread_tables() won't leave it anyway).

Make open_and_lock_tables() and open_and_lock_tables_derived()
call close_thread_tables() upon failure.
Remove the calls to close_thread_tables() that are now
unnecessary.

Simplify the back off condition in Open_table_context.

Streamline metadata lock handling in LOCK TABLES 
implementation.

Add asserts to ensure correct life cycle of 
statement transaction in a session.

Remove a piece of dead code that has also become redundant
after the fix for Bug 37521.

1 files changed, 8 insertions, 8 deletions

diff --git a/sql/sp.cc b/sql/sp.cc
index 5328471f4c0..0265ef45a2a 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -450,10 +450,7 @@ static TABLE *open_proc_table_for_update(THD *thd)
   if (!proc_table_intact.check(table, &proc_table_def))
     DBUG_RETURN(table);
 
-  close_thread_tables(thd);
-
   DBUG_RETURN(NULL);
-
 }
 
 
@@ -856,6 +853,7 @@ db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp,
   }
 
 end:
+  thd->lex->sphead= NULL;
   lex_end(thd->lex);
   thd->lex= old_lex;
   return ret;
@@ -1159,8 +1157,6 @@ sp_create_routine(THD *thd, int type, sp_head *sp)
 done:
   thd->count_cuted_fields= saved_count_cuted_fields;
   thd->variables.sql_mode= saved_mode;
-
-  close_thread_tables(thd);
   /* Restore the state of binlog format */
   DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row());
   if (save_binlog_row_based)
@@ -1239,8 +1235,6 @@ sp_drop_routine(THD *thd, int type, sp_name *name)
         sp_cache_flush_obsolete(spc, &sp);
     }
   }
-
-  close_thread_tables(thd);
   /* Restore the state of binlog format */
   DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row());
   if (save_binlog_row_based)
@@ -1348,7 +1342,6 @@ sp_update_routine(THD *thd, int type, sp_name *name, st_sp_chistics *chistics)
     sp_cache_invalidate();
   }
 err:
-  close_thread_tables(thd);
   /* Restore the state of binlog format */
   DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row());
   if (save_binlog_row_based)
@@ -1370,6 +1363,7 @@ sp_drop_db_routines(THD *thd, char *db)
   TABLE *table;
   int ret;
   uint key_len;
+  MDL_ticket *mdl_savepoint= thd->mdl_context.mdl_savepoint();
   DBUG_ENTER("sp_drop_db_routines");
   DBUG_PRINT("enter", ("db: %s", db));
 
@@ -1410,6 +1404,11 @@ sp_drop_db_routines(THD *thd, char *db)
   table->file->ha_index_end();
 
   close_thread_tables(thd);
+  /*
+    Make sure to only release the MDL lock on mysql.proc, not other
+    metadata locks DROP DATABASE might have acquired.
+  */
+  thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
 
 err:
   DBUG_RETURN(ret);
@@ -2142,6 +2141,7 @@ sp_load_for_information_schema(THD *thd, TABLE *proc_table, String *db,
   newlex.current_select= NULL; 
   sp= sp_compile(thd, &defstr, sql_mode, creation_ctx);
   *free_sp_head= 1;
+  thd->lex->sphead= NULL;
   lex_end(thd->lex);
   thd->lex= old_lex;
   return sp;