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, 7 insertions, 15 deletions

diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 23403e6e00a..6a0b5586598 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -27,6 +27,7 @@
 
 #include "sp_head.h"
 #include "sp.h"
+#include "sp_cache.h"
 
 #ifdef HAVE_OPENSSL
 /*
@@ -124,7 +125,7 @@ static bool end_active_trans(THD *thd)
 {
   int error=0;
   DBUG_ENTER("end_active_trans");
-  if (unlikely(thd->transaction.in_sub_stmt))
+  if (unlikely(thd->in_sub_stmt))
   {
     my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
     DBUG_RETURN(1);
@@ -147,11 +148,7 @@ static bool end_active_trans(THD *thd)
 static bool begin_trans(THD *thd)
 {
   int error=0;
-  /*
-    QQ: May be it is better to simply prohibit COMMIT and ROLLBACK in
-        stored routines as SQL2003 suggests?
-  */
-  if (unlikely(thd->transaction.in_sub_stmt))
+  if (unlikely(thd->in_sub_stmt))
   {
     my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
     return 1;
@@ -1340,11 +1337,7 @@ int end_trans(THD *thd, enum enum_mysql_completiontype completion)
   int res= 0;
   DBUG_ENTER("end_trans");
 
-  /*
-    QQ: May be it is better to simply prohibit COMMIT and ROLLBACK in
-        stored routines as SQL2003 suggests?
-  */
-  if (unlikely(thd->transaction.in_sub_stmt))
+  if (unlikely(thd->in_sub_stmt))
   {
     my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
     DBUG_RETURN(1);
@@ -4128,9 +4121,8 @@ end_with_restore_list:
        goto error;
 
       /*
-        By this moment all needed SPs should be in cache so no need
-        to look into DB. Moreover we may be unable to do it becuase
-        we may don't have read lock on mysql.proc
+        By this moment all needed SPs should be in cache so no need to look 
+        into DB. 
       */
       if (!(sp= sp_find_procedure(thd, lex->spname, TRUE)))
       {
@@ -4195,7 +4187,7 @@ end_with_restore_list:
 	select_limit= thd->variables.select_limit;
 	thd->variables.select_limit= HA_POS_ERROR;
 
-	thd->row_count_func= 0;
+        thd->row_count_func= 0;
         tmp_disable_binlog(thd); /* don't binlog the substatements */
 	res= sp->execute_procedure(thd, &lex->value_list);
         reenable_binlog(thd);