Added Non-prelocked SP execution: Now a PROCEDURE doesn't enter/leave prelocked mode for

its body, but lets each statement to get/release its own locks. This allows a broader set
of statements to be executed inside PROCEDUREs (but breaks replication)
This patch should fix BUG#8072, BUG#8766, BUG#9563, BUG#11126


mysql-test/r/sp-security.result:
  Drop tables this test attempts to create
mysql-test/r/sp-threads.result:
  Update test results
mysql-test/r/sp.result:
  Disabled a test that triggers BUG#11986, cleanup used tables when tests start.
mysql-test/r/view.result:
  Enabled a test case that now works with prelocking-free SPs
mysql-test/t/sp-security.test:
  Drop tables this test attempts to create
mysql-test/t/sp.test:
  Disabled a test that triggers BUG#11986, cleanup used tables when tests start.
mysql-test/t/view.test:
  Enabled a test case that now works with prelocking-free SPs
sql/handler.cc:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt
sql/item_func.cc:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt
sql/sp.cc:
  Non-prelocked SP execution: Added support for skipping prelocking of procedure body for
  "CALL proc(...)" statements.
sql/sp.h:
  Non-prelocked SP execution: Added support for skipping prelocking of procedure body for
  "CALL proc(...)" statements.
sql/sp_cache.h:
  Added comments
sql/sp_head.cc:
  Non-prelocked SP execution:
  * Try to unlock tables after PROCEDURE arguments have been evaluated.
  * Make sp_lex_keeper be able to execute in 2 modes: A) when already in prelocked mode
    B) when its statement enters/leaves prelocked mode itself.
sql/sp_head.h:
  Non-prelocked SP execution:  Make sp_lex_keeper to additionally keep list of tables it 
  needs to prelock when its statement enters/leaves prelocked mode on its own.
sql/sql_base.cc:
  Non-prelocked SP execution: Make open_tables() to
   * detect 'CALL proc(...)' and not to do prelocking for procedure body statements.
   * Make lex->query_tables_last to point precisely to a boundary in lex->query_tables 
     list where 'own' tables and views' tables end and added-for-prelocking tables begin.
     (it was not true before - view's tables could end up after query_tables_own_last)
sql/sql_class.cc:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt
sql/sql_class.h:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt
sql/sql_lex.cc:
  Non-prelocked SP execution: More rigourous cleanup in st_lex::cleanup_after_one_table_open()
sql/sql_parse.cc:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt, remove outdated comments
sql/sql_trigger.h:
  Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt

1 files changed, 66 insertions, 18 deletions

diff --git a/sql/sp.cc b/sql/sp.cc
index a277c6bd253..3d513b16d07 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -1176,6 +1176,44 @@ extern "C" byte* sp_sroutine_key(const byte *ptr, uint *plen, my_bool first)
 
 
 /*
+  Check if routines in routines_list require sp_cache_routines_and_add_tables
+  call.
+
+  SYNOPSIS
+    sp_need_cache_routines()
+      thd
+      routines 
+      need_skip_first  OUT TRUE - don't do prelocking for the 1st element in 
+                                  routines list.
+                           FALSE- otherwise
+  NOTES 
+    This function assumes that for any "CALL proc(...)" statement routines_list 
+    will have 'proc' as first element (it may have several, consider e.g.
+    "proc(sp_func(...)))". This property is currently guaranted by the parser.
+
+  RETURN
+    TRUE  Need to sp_cache_routines_and_add_tables call for this statement.
+    FALSE Otherwise.
+*/
+
+bool sp_need_cache_routines(THD *thd, SQL_LIST *routines_list, bool *need_skip_first)
+{
+  Sroutine_hash_entry *routine;
+  routine= (Sroutine_hash_entry*)routines_list->first;
+
+  *need_skip_first= FALSE;
+  if (!routine)
+    return FALSE;
+
+  if (routine->key.str[0] != TYPE_ENUM_PROCEDURE)
+    return TRUE;
+
+  *need_skip_first= TRUE;
+  return TRUE;
+}
+
+
+/*
   Auxilary function that adds new element to the set of stored routines
   used by statement.
 
@@ -1312,11 +1350,13 @@ static void sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src)
 
   SYNOPSIS
     sp_cache_routines_and_add_tables_aux()
-      thd   - thread context
-      lex   - LEX representing statement
-      start - first routine from the list of routines to be cached
-              (this list defines mentioned sub-set).
-
+      thd              - thread context
+      lex              - LEX representing statement
+      start            - first routine from the list of routines to be cached
+                         (this list defines mentioned sub-set).
+      first_no_prelock - If true, don't add tables or cache routines used by
+                         the body of the first routine (i.e. *start)
+                         will be executed in non-prelocked mode.
   NOTE
     If some function is missing this won't be reported here.
     Instead this fact will be discovered during query execution.
@@ -1328,10 +1368,11 @@ static void sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src)
 
 static bool
 sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
-                                     Sroutine_hash_entry *start)
+                                     Sroutine_hash_entry *start, 
+                                     bool first_no_prelock)
 {
   bool result= FALSE;
-
+  bool first= TRUE;
   DBUG_ENTER("sp_cache_routines_and_add_tables_aux");
 
   for (Sroutine_hash_entry *rt= start; rt; rt= rt->next)
@@ -1367,9 +1408,13 @@ sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
     }
     if (sp)
     {
-      sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines);
-      result|= sp->add_used_tables_to_table_list(thd, &lex->query_tables_last);
+      if (!(first && first_no_prelock))
+      {
+        sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines);
+        result|= sp->add_used_tables_to_table_list(thd, &lex->query_tables_last);
+      }
     }
+    first= FALSE;
   }
   DBUG_RETURN(result);
 }
@@ -1382,20 +1427,22 @@ sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
 
   SYNOPSIS
     sp_cache_routines_and_add_tables()
-      thd   - thread context
-      lex   - LEX representing statement
-
+      thd              - thread context
+      lex              - LEX representing statement
+      first_no_prelock - If true, don't add tables or cache routines used by
+                         the body of the first routine (i.e. *start)
+                         
   RETURN VALUE
     TRUE  - some tables were added
     FALSE - no tables were added.
 */
 
 bool
-sp_cache_routines_and_add_tables(THD *thd, LEX *lex)
+sp_cache_routines_and_add_tables(THD *thd, LEX *lex, bool first_no_prelock)
 {
-
   return sp_cache_routines_and_add_tables_aux(thd, lex,
-           (Sroutine_hash_entry *)lex->sroutines_list.first);
+           (Sroutine_hash_entry *)lex->sroutines_list.first,
+           first_no_prelock);
 }
 
 
@@ -1417,8 +1464,8 @@ sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, LEX *aux_lex)
   Sroutine_hash_entry **last_cached_routine_ptr=
                           (Sroutine_hash_entry **)lex->sroutines_list.next;
   sp_update_stmt_used_routines(thd, lex, &aux_lex->sroutines);
-  (void)sp_cache_routines_and_add_tables_aux(thd, lex,
-                                             *last_cached_routine_ptr);
+  (void)sp_cache_routines_and_add_tables_aux(thd, lex, 
+                                             *last_cached_routine_ptr, FALSE);
 }
 
 
@@ -1453,7 +1500,8 @@ sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
         }
 
     (void)sp_cache_routines_and_add_tables_aux(thd, lex,
-                                               *last_cached_routine_ptr);
+                                               *last_cached_routine_ptr, 
+                                               FALSE);
   }
 }