MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:Sergey Petrunia Date:July 30 2005 4:56am
Subject:bk commit into 5.0 tree (sergefp:1.1883) BUG#11126
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.1883 05/07/30 04:55:56 sergefp@stripped +23 -0
  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/t/sp-prelocking.test
    1.1 05/07/30 04:55:50 sergefp@stripped +235 -0

  mysql-test/r/sp-prelocking.result
    1.1 05/07/30 04:55:50 sergefp@stripped +218 -0

  sql/sql_trigger.h
    1.11 05/07/30 04:55:50 sergefp@stripped +3 -3
    Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt

  sql/sql_parse.cc
    1.452 05/07/30 04:55:50 sergefp@stripped +8 -11
    Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt

  sql/sql_lex.h
    1.191 05/07/30 04:55:50 sergefp@stripped +1 -0
    Non-prelocked SP execution: Add LEX::query_tables_non_prelocked_last, boundary between
    query 'own' tables + views' tables and tables used by routines this statement uses.

  sql/sql_lex.cc
    1.159 05/07/30 04:55:50 sergefp@stripped +1 -0
    Non-prelocked SP execution: More rigourous cleanup in st_lex::cleanup_after_one_table_open()

  sql/sql_class.h
    1.251 05/07/30 04:55:50 sergefp@stripped +4 -2
    Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt

  sql/sql_class.cc
    1.195 05/07/30 04:55:50 sergefp@stripped +1 -1
    Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt

  mysql-test/t/sp-prelocking.test
    1.0 05/07/30 04:55:50 sergefp@stripped +0 -0
    BitKeeper file /home/psergey/mysql-5.0-sp-no-lock-r5/mysql-test/t/sp-prelocking.test

  mysql-test/r/sp-prelocking.result
    1.0 05/07/30 04:55:50 sergefp@stripped +0 -0
    BitKeeper file /home/psergey/mysql-5.0-sp-no-lock-r5/mysql-test/r/sp-prelocking.result

  sql/sql_base.cc
    1.272 05/07/30 04:55:49 sergefp@stripped +49 -13
    Non-prelocked SP execution: Make open_tables() to
     * detect 'CALL proc(...)' and not to do prelocking for procedure body statements.
     * In thd->lex->query_tables_non_prelocked_last store a boundary in lex->query_tables 
       list where 'own' tables and views' tables end and added-for-prelocking tables begin.

  sql/sp_head.h
    1.63 05/07/30 04:55:49 sergefp@stripped +31 -1
    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/sp_head.cc
    1.160 05/07/30 04:55:49 sergefp@stripped +68 -1
    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_cache.h
    1.9 05/07/30 04:55:49 sergefp@stripped +13 -3
    Added comments

  sql/sp.h
    1.25 05/07/30 04:55:49 sergefp@stripped +4 -1
    Non-prelocked SP execution: Added support for skipping prelocking of procedure body for
    "CALL proc(...)" statements.

  sql/sp.cc
    1.84 05/07/30 04:55:49 sergefp@stripped +66 -18
    Non-prelocked SP execution: Added support for skipping prelocking of procedure body for
        "CALL proc(...)" statements.

  sql/item_func.cc
    1.236 05/07/30 04:55:49 sergefp@stripped +3 -3
    Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt

  sql/handler.cc
    1.182 05/07/30 04:55:49 sergefp@stripped +2 -2
    Rename: thd->transaction.in_sub_stmt -> thd->in_sub_stmt

  mysql-test/t/view.test
    1.90 05/07/30 04:55:49 sergefp@stripped +9 -9
     Enabled a test case that now works with prelocking-free SPs

  mysql-test/t/sp.test
    1.134 05/07/30 04:55:49 sergefp@stripped +22 -24
     Disabled a test that triggers BUG#11986, cleanup used tables when tests start.

  mysql-test/t/sp-security.test
    1.19 05/07/30 04:55:49 sergefp@stripped +1 -1
    Drop tables this test attempts to create

  mysql-test/r/view.result
    1.96 05/07/30 04:55:49 sergefp@stripped +5 -0
    Enabled a test case that now works with prelocking-free SPs

  mysql-test/r/sp.result
    1.139 05/07/30 04:55:49 sergefp@stripped +1 -28
    Disabled a test that triggers BUG#11986, cleanup used tables when tests start.

  mysql-test/r/sp-threads.result
    1.5 05/07/30 04:55:49 sergefp@stripped +1 -1
    Update test results

  mysql-test/r/sp-security.result
    1.17 05/07/30 04:55:49 sergefp@stripped +1 -1
    Drop tables this test attempts to create

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	sergefp
# Host:	newbox.mylan
# Root:	/home/psergey/mysql-5.0-sp-no-lock-r5

--- 1.181/sql/handler.cc	2005-07-21 04:35:55 +00:00
+++ 1.182/sql/handler.cc	2005-07-30 04:55:49 +00:00
@@ -598,7 +598,7 @@
   my_xid xid= thd->transaction.xid.get_my_xid();
   DBUG_ENTER("ha_commit_trans");
 
-  if (thd->transaction.in_sub_stmt)
+  if (thd->in_sub_stmt)
   {
     /*
       Since we don't support nested statement transactions in 5.0,
@@ -717,7 +717,7 @@
   THD_TRANS *trans=all ? &thd->transaction.all : &thd->transaction.stmt;
   bool is_real_trans=all || thd->transaction.all.nht == 0;
   DBUG_ENTER("ha_rollback_trans");
-  if (thd->transaction.in_sub_stmt)
+  if (thd->in_sub_stmt)
   {
     /*
       If we are inside stored function or trigger we should not commit or

--- 1.235/sql/item_func.cc	2005-07-19 17:42:24 +00:00
+++ 1.236/sql/item_func.cc	2005-07-30 04:55:49 +00:00
@@ -4844,7 +4844,7 @@
   THD *thd= current_thd;
   ulong old_client_capabilites;
   int res= -1;
-  bool save_in_sub_stmt= thd->transaction.in_sub_stmt;
+  bool save_in_sub_stmt= thd->in_sub_stmt;
   my_bool save_no_send_ok;
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
   st_sp_security_context save_ctx;
@@ -4882,11 +4882,11 @@
   */
 
   tmp_disable_binlog(thd); /* don't binlog the substatements */
-  thd->transaction.in_sub_stmt= TRUE;
+  thd->in_sub_stmt= TRUE;
 
   res= m_sp->execute_function(thd, args, arg_count, itp);
 
-  thd->transaction.in_sub_stmt= save_in_sub_stmt;
+  thd->in_sub_stmt= save_in_sub_stmt;
   reenable_binlog(thd);
   if (res && mysql_bin_log.is_open() &&
       (m_sp->m_chistics->daccess == SP_CONTAINS_SQL ||

--- 1.271/sql/sql_base.cc	2005-07-21 05:38:38 +00:00
+++ 1.272/sql/sql_base.cc	2005-07-30 04:55:49 +00:00
@@ -391,6 +391,8 @@
 			LOCK_open
     skip_derived	Set to 1 (0 = default) if we should not free derived
 			tables.
+    stopper             When closing tables from thd->open_tables(->next)*, 
+                        don't close/remove tables starting from stopper.
 
   IMPLEMENTATION
     Unlocks tables and frees derived tables.
@@ -474,6 +476,7 @@
       We are in prelocked mode, so we have to leave it now with doing
       implicit UNLOCK TABLES if need.
     */
+    DBUG_PRINT("info",("thd->prelocked_mode= NON_PRELOCKED"));
     thd->prelocked_mode= NON_PRELOCKED;
 
     if (prelocked_mode == PRELOCKED_UNDER_LOCK_TABLES)
@@ -1792,6 +1795,7 @@
   DBUG_RETURN(1);
 }
 
+
 /*
   Open all tables in list
 
@@ -1825,6 +1829,8 @@
   MEM_ROOT new_frm_mem;
   /* Also used for indicating that prelocking is need */
   TABLE_LIST **query_tables_last_own;
+  TABLE_LIST ***last_non_prelocked= 
+    &(thd->lex->query_tables_non_prelocked_last);
   DBUG_ENTER("open_tables");
   /*
     temporary mem_root for new .frm parsing.
@@ -1843,10 +1849,6 @@
     statement for which table list for prelocking is already built, let
     us cache routines and try to build such table list.
 
-    NOTE: If we want queries with functions to work under explicit
-    LOCK TABLES we have to additionaly lock mysql.proc table in it.
-    At least until Monty will fix SP loading :)
-
     NOTE: We can't delay prelocking until we will met some sub-statement
     which really uses tables, since this will imply that we have to restore
     its table list to be able execute it in some other context.
@@ -1860,19 +1862,29 @@
     mode we will have some locked tables, because queries which use only
     derived/information schema tables and views possible. Thus "counter"
     may be still zero for prelocked statement...
+
+    NOTE: The above notes may be out of date. Please wait for psergey to 
+          document new prelocked behavior.
   */
-  if (!thd->prelocked_mode && !thd->lex->requires_prelocking() &&
-      thd->lex->sroutines.records)
+  *last_non_prelocked= thd->lex->query_tables_last;
+  
+  if (!thd->prelocked_mode && !thd->lex->requires_prelocking())
   {
-    TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
+    bool first_no_prelocking;
+    if (sp_need_cache_routines(thd, &thd->lex->sroutines_list,
+                               &first_no_prelocking))
+    {
+      TABLE_LIST **save_query_tables_last= thd->lex->query_tables_last;
 
-    DBUG_ASSERT(thd->lex->query_tables == *start);
+      DBUG_ASSERT(thd->lex->query_tables == *start);
 
-    if (sp_cache_routines_and_add_tables(thd, thd->lex) ||
-        *start)
-    {
-      query_tables_last_own= save_query_tables_last;
-      *start= thd->lex->query_tables;
+      if (sp_cache_routines_and_add_tables(thd, thd->lex,
+                                           first_no_prelocking) ||
+          *start)
+      {
+        query_tables_last_own= save_query_tables_last;
+        *start= thd->lex->query_tables;
+      }
     }
   }
 
@@ -1891,10 +1903,32 @@
       DBUG_RETURN(-1);
     }
     (*counter)++;
+    
+    /* 
+      tables->next_global list consists of two parts:
+      1) Query tables and underlying tables of views.
+      2) Tables used by all stored routines that this statement invokes on
+         execution.
+      We need to know where the bound between these two parts is. If we're
+      about to open the last table in part #1, save the first table in part
+      #2, and later adjust the bound if we've opened a view.
+    */
+    TABLE_LIST *first_prelocked;
+    if (&(tables->next_global) == *last_non_prelocked)
+      first_prelocked= tables->next_global;
+    else 
+      first_prelocked= NULL;
+    
     if (!tables->table &&
 	!(tables->table= open_table(thd, tables, &new_frm_mem, &refresh, 0)))
     {
       free_root(&new_frm_mem, MYF(MY_KEEP_PREALLOC));
+
+      if (first_prelocked)
+      {
+        while ((**last_non_prelocked) != first_prelocked)
+          *last_non_prelocked= &((**last_non_prelocked)->next_global);
+      }
       if (tables->view)
       {
         /* VIEW placeholder */
@@ -2323,6 +2357,7 @@
         and was marked as occupied during open_tables() as free for reuse.
       */
       mark_real_tables_as_free_for_reuse(first_not_own);
+      DBUG_PRINT("info",("prelocked_mode= PRELOCKED"));
       thd->prelocked_mode= PRELOCKED;
     }
   }
@@ -2346,6 +2381,7 @@
     if (thd->lex->requires_prelocking())
     {
       mark_real_tables_as_free_for_reuse(first_not_own);
+      DBUG_PRINT("info", ("thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES"));
       thd->prelocked_mode= PRELOCKED_UNDER_LOCK_TABLES;
     }
   }

--- 1.194/sql/sql_class.cc	2005-07-19 18:21:02 +00:00
+++ 1.195/sql/sql_class.cc	2005-07-30 04:55:50 +00:00
@@ -178,7 +178,7 @@
    rand_used(0), time_zone_used(0),
    last_insert_id_used(0), insert_id_used(0), clear_next_insert_id(0),
    in_lock_tables(0), bootstrap(0), derived_tables_processing(FALSE),
-   spcont(NULL)
+   spcont(NULL), in_sub_stmt(FALSE)
 {
   current_arena= this;
   host= user= priv_user= db= ip= 0;

--- 1.250/sql/sql_class.h	2005-07-19 18:21:02 +00:00
+++ 1.251/sql/sql_class.h	2005-07-30 04:55:50 +00:00
@@ -1132,6 +1132,10 @@
   thr_lock_type update_lock_default;
   delayed_insert *di;
   my_bool    tablespace_op;	/* This is TRUE in DISCARD/IMPORT TABLESPACE */
+  
+  /* TRUE if we are inside of trigger or stored function. */
+  bool in_sub_stmt;
+  
   /* container for handler's private per-connection data */
   void *ha_data[MAX_HA];
   struct st_transactions {
@@ -1139,8 +1143,6 @@
     THD_TRANS all;			// Trans since BEGIN WORK
     THD_TRANS stmt;			// Trans for current statement
     bool on;                            // see ha_enable_transaction()
-    /* TRUE if we are inside of trigger or stored function. */
-    bool in_sub_stmt;
     XID  xid;                           // transaction identifier
     enum xa_states xa_state;            // used by external XA only
     /*

--- 1.158/sql/sql_lex.cc	2005-07-14 20:41:52 +00:00
+++ 1.159/sql/sql_lex.cc	2005-07-30 04:55:50 +00:00
@@ -2008,6 +2008,7 @@
   time_zone_tables_used= 0;
   if (sroutines.records)
     my_hash_reset(&sroutines);
+  sroutines_list.empty();
 }
 
 

--- 1.190/sql/sql_lex.h	2005-07-19 16:06:43 +00:00
+++ 1.191/sql/sql_lex.h	2005-07-30 04:55:50 +00:00
@@ -841,6 +841,7 @@
   */
   TABLE_LIST **query_tables_own_last;
 
+  TABLE_LIST **query_tables_non_prelocked_last;
   /*
     Pointers to part of LOAD DATA statement that should be rewritten
     during replication ("LOCAL 'filename' REPLACE INTO" part).

--- 1.451/sql/sql_parse.cc	2005-07-19 23:34:27 +00:00
+++ 1.452/sql/sql_parse.cc	2005-07-30 04:55:50 +00:00
@@ -27,6 +27,7 @@
 
 #include "sp_head.h"
 #include "sp.h"
+#include "sp_cache.h"
 
 #ifdef HAVE_OPENSSL
 /*
@@ -124,7 +125,7 @@
 {
   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 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;
@@ -1343,8 +1340,9 @@
   /*
     QQ: May be it is better to simply prohibit COMMIT and ROLLBACK in
         stored routines as SQL2003 suggests?
+    ^ psergey ^ : comment!
   */
-  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 +4126,8 @@
        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 +4192,7 @@
 	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);

--- 1.95/mysql-test/r/view.result	2005-07-20 02:59:29 +00:00
+++ 1.96/mysql-test/r/view.result	2005-07-30 04:55:49 +00:00
@@ -581,6 +581,11 @@
 drop view v1;
 create view v1 (a,a) as select 'a','a';
 ERROR 42S21: Duplicate column name 'a'
+drop procedure if exists p1;
+create procedure p1 () begin declare v int; create view v1 as select v; end;//
+call p1();
+ERROR HY000: View's SELECT contains a variable or parameter
+drop procedure p1;
 create table t1 (col1 int,col2 char(22));
 insert into t1 values(5,'Hello, world of views');
 create view v1 as select * from t1;

--- 1.89/mysql-test/t/view.test	2005-07-20 02:59:29 +00:00
+++ 1.90/mysql-test/t/view.test	2005-07-30 04:55:49 +00:00
@@ -490,15 +490,15 @@
 #
 # SP variables inside view test
 #
-# QQ This can't be tested with the new table locking for functions,
-# QQ since views created in an SP can't be used within the same SP
-# QQ (just as for tables). Instead it fails with error 1146.
-#delimiter //;
-#create procedure p1 () begin declare v int; create view v1 as select v; end;//
-#delimiter ;//
-#-- error 1351
-#call p1();
-#drop procedure p1;
+--disable_warnings
+drop procedure if exists p1;
+--enable_warnings
+delimiter //;
+create procedure p1 () begin declare v int; create view v1 as select v; end;//
+delimiter ;//
+-- error 1351
+call p1();
+drop procedure p1;
 
 #
 # updatablity should be transitive

--- 1.10/sql/sql_trigger.h	2005-07-19 16:06:43 +00:00
+++ 1.11/sql/sql_trigger.h	2005-07-30 04:55:50 +00:00
@@ -78,7 +78,7 @@
 
     if (bodies[event][time_type])
     {
-      bool save_in_sub_stmt= thd->transaction.in_sub_stmt;
+      bool save_in_sub_stmt= thd->in_sub_stmt;
 #ifndef EMBEDDED_LIBRARY
       /* Surpress OK packets in case if we will execute statements */
       my_bool nsok= thd->net.no_send_ok;
@@ -107,11 +107,11 @@
         does NOT go into binlog.
       */
       tmp_disable_binlog(thd);
-      thd->transaction.in_sub_stmt= TRUE;
+      thd->in_sub_stmt= TRUE;
 
       res= bodies[event][time_type]->execute_function(thd, 0, 0, 0);
 
-      thd->transaction.in_sub_stmt= save_in_sub_stmt;
+      thd->in_sub_stmt= save_in_sub_stmt;
       reenable_binlog(thd);
 
 #ifndef EMBEDDED_LIBRARY

--- 1.16/mysql-test/r/sp-security.result	2005-07-15 21:17:01 +00:00
+++ 1.17/mysql-test/r/sp-security.result	2005-07-30 04:55:49 +00:00
@@ -1,7 +1,7 @@
 use test;
 grant usage on *.* to user1@localhost;
 flush privileges;
-drop table if exists t1;
+drop table if exists t1,t2;
 drop database if exists db1_secret;
 create database db1_secret;
 create procedure db1_secret.dummy() begin end;

--- 1.4/mysql-test/r/sp-threads.result	2005-07-13 09:48:02 +00:00
+++ 1.5/mysql-test/r/sp-threads.result	2005-07-30 04:55:49 +00:00
@@ -35,7 +35,7 @@
 show processlist;
 Id	User	Host	db	Command	Time	State	Info
 #	root	localhost	test	Sleep	#		NULL
-#	root	localhost	test	Query	#	Locked	call bug9486()
+#	root	localhost	test	Query	#	Locked	update t1, t2 set val= 1 where id1=id2
 #	root	localhost	test	Query	#	NULL	show processlist
 unlock tables;
 drop procedure bug9486;

--- 1.138/mysql-test/r/sp.result	2005-07-16 12:10:35 +00:00
+++ 1.139/mysql-test/r/sp.result	2005-07-30 04:55:49 +00:00
@@ -1,10 +1,9 @@
 use test;
-drop table if exists t1;
+drop table if exists t1,t2,t3,t4;
 create table t1 (
 id   char(16) not null default '',
 data int not null
 );
-drop table if exists t2;
 create table t2 (
 s   char(16),
 i   int,
@@ -3042,32 +3041,6 @@
 drop procedure if exists bug6063|
 drop procedure if exists bug7088_1|
 drop procedure if exists bug7088_2|
-create procedure bug6063()
-call bug6063()|
-show create procedure bug6063|
-Procedure	sql_mode	Create Procedure
-bug6063		CREATE PROCEDURE `test`.`bug6063`()
-l?bel: begin end
-set character set utf8|
-create procedure bug7088_1()
-label1: begin end label1|
-create procedure bug7088_2()
-läbel1: begin end|
-call bug7088_1()|
-call bug7088_2()|
-set character set default|
-show create procedure bug7088_1|
-Procedure	sql_mode	Create Procedure
-bug7088_1		CREATE PROCEDURE `test`.`bug7088_1`()
-label1: begin end label1
-show create procedure bug7088_2|
-Procedure	sql_mode	Create Procedure
-bug7088_2		CREATE PROCEDURE `test`.`bug7088_2`()
-drop procedure bug6063|
-drop procedure bug7088_1|
-drop procedure bug7088_2|
 drop procedure if exists bug9565_sub|
 drop procedure if exists bug9565|
 create procedure bug9565_sub()

--- 1.18/mysql-test/t/sp-security.test	2005-07-15 21:17:01 +00:00
+++ 1.19/mysql-test/t/sp-security.test	2005-07-30 04:55:49 +00:00
@@ -15,7 +15,7 @@
 flush privileges;
 
 --disable_warnings
-drop table if exists t1;
+drop table if exists t1,t2;
 drop database if exists db1_secret;
 --enable_warnings
 # Create our secret database

--- 1.133/mysql-test/t/sp.test	2005-07-16 12:10:35 +00:00
+++ 1.134/mysql-test/t/sp.test	2005-07-30 04:55:49 +00:00
@@ -22,15 +22,12 @@
 # t3 and up are created and dropped when needed.
 #
 --disable_warnings
-drop table if exists t1;
+drop table if exists t1,t2,t3,t4;
 --enable_warnings
 create table t1 (
 	id   char(16) not null default '',
         data int not null
 );
---disable_warnings
-drop table if exists t2;
---enable_warnings
 create table t2 (
 	s   char(16),
         i   int,
@@ -3812,26 +3809,27 @@
 drop procedure if exists bug7088_2|
 --enable_warnings
 
-create procedure bug6063()
-call bug6063()|
-# QQ Known bug: this will not show the label correctly.
-show create procedure bug6063|
-
-set character set utf8|
-create procedure bug7088_1()
-  label1: begin end label1|
-create procedure bug7088_2()
-  läbel1: begin end|
-call bug7088_1()|
-call bug7088_2()|
-set character set default|
-show create procedure bug7088_1|
-show create procedure bug7088_2|
-
-drop procedure bug6063|
-drop procedure bug7088_1|
-drop procedure bug7088_2|
+# psergey: temporarily disabled until Bar fixes BUG#11986
+# create procedure bug6063()
+# call bug6063()|
+# # QQ Known bug: this will not show the label correctly.
+# show create procedure bug6063|
+# 
+# set character set utf8|
+# create procedure bug7088_1()
+#   label1: begin end label1|
+# create procedure bug7088_2()
+#   läbel1: begin end|
+# call bug7088_1()|
+# call bug7088_2()|
+# set character set default|
+# show create procedure bug7088_1|
+# show create procedure bug7088_2|
+# 
+# drop procedure bug6063|
+# drop procedure bug7088_1|
+# drop procedure bug7088_2|
 
 #
 # BUG#9565: "Wrong locking in stored procedure if a sub-sequent procedure

--- 1.83/sql/sp.cc	2005-07-19 16:06:43 +00:00
+++ 1.84/sql/sp.cc	2005-07-30 04:55:49 +00:00
@@ -1176,6 +1176,44 @@
 
 
 /*
+  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 @@
 
   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 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 @@
     }
     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 @@
 
   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 @@
   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 @@
         }
 
     (void)sp_cache_routines_and_add_tables_aux(thd, lex,
-                                               *last_cached_routine_ptr);
+                                               *last_cached_routine_ptr, 
+                                               FALSE);
   }
 }
 

--- 1.24/sql/sp.h	2005-07-13 09:57:01 +00:00
+++ 1.25/sql/sp.h	2005-07-30 04:55:49 +00:00
@@ -79,10 +79,13 @@
   Procedures for pre-caching of stored routines and building table list
   for prelocking.
 */
+bool sp_need_cache_routines(THD *thd, SQL_LIST *routines_list, 
+                            bool *need_skip_first);
 void sp_add_used_routine(LEX *lex, Query_arena *arena,
                          sp_name *rt, char rt_type);
 void sp_update_sp_used_routines(HASH *dst, HASH *src);
-bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex);
+bool sp_cache_routines_and_add_tables(THD *thd, LEX *lex, 
+                                      bool first_no_prelock);
 void sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex,
                                                LEX *aux_lex);
 void sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,

--- 1.8/sql/sp_cache.h	2005-05-27 10:03:33 +00:00
+++ 1.9/sql/sp_cache.h	2005-07-30 04:55:49 +00:00
@@ -22,6 +22,12 @@
 #pragma interface			/* gcc class implementation */
 #endif
 
+/*
+  Stored procedures/functions cache. This is used as follows:
+   * Each thread has its own cache.
+   * When SP is used it is always in some thread's cache.
+*/
+
 class sp_head;
 class sp_cache;
 
@@ -31,16 +37,20 @@
 /* Clear the cache *cp and set *cp to NULL */
 void sp_cache_clear(sp_cache **cp);
 
-/* Insert an SP to cache. If 'cp' points to NULL, it's set to a new cache */
+/* Insert an SP into cache. If 'cp' points to NULL, it's set to a new cache */
 void sp_cache_insert(sp_cache **cp, sp_head *sp);
 
 /* Lookup an SP in cache */
 sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name);
 
-/* Remove an SP from cache. Returns true if something was removed */
+/* 
+  Remove an SP from cache, and also bump the Cversion number so all other 
+  caches are invalidated. 
+  Returns true if something was removed.
+*/
 bool sp_cache_remove(sp_cache **cp, sp_name *name);
 
-/* Invalidate a cache */
+/* Invalidate all existing SP caches by bumping Cversion number. */
 void sp_cache_invalidate();
 
 

--- 1.159/sql/sp_head.cc	2005-07-15 15:24:13 +00:00
+++ 1.160/sql/sp_head.cc	2005-07-30 04:55:49 +00:00
@@ -879,7 +879,10 @@
       octx= new sp_rcontext(csize, hmax, cmax);
       tmp_octx= TRUE;
     }
+
+    /* Evaluate SP arguments (i.e. get the values passed as parameters) */
     // QQ: Should do type checking?
+    DBUG_PRINT("info",(" %.*s: eval args", m_name.length, m_name.str));
     for (i = 0 ; (it= li++) && i < params ; i++)
     {
       sp_pvar_t *pvar= m_pcont->find_pvar(i);
@@ -916,6 +919,14 @@
       }
     }
 
+    /* 
+      Okay, got values for all arguments. Close tables that might be used by 
+      arguments evaluation.
+    */
+    if (!thd->in_sub_stmt)
+      close_thread_tables(thd, 0, 0, 0);
+
+    DBUG_PRINT("info",(" %.*s: eval args done", m_name.length, m_name.str));
     // The rest of the frame are local variables which are all IN.
     // Default all variables to null (those with default clauses will
     // be set by an set instruction).
@@ -1480,8 +1491,38 @@
            implemented at the same time as ability not to store LEX for
            instruction if it is not really used.
   */
-  reinit_stmt_before_use(thd, m_lex);
 
+  bool collect_prelocking_tail= FALSE;
+
+  if (thd->prelocked_mode == NON_PRELOCKED)
+  {
+    /*
+      This statement will enter/leave prelocked mode on its own.
+      Entering prelocked mode changes table list and related members
+      of LEX, so we'll need to restore them.
+    */
+    if (last_non_prelocked)
+    {
+      /*
+        We've already entered/left prelocked mode with this statement.
+        Attach the list of tables that need to be prelocked and mark m_lex
+        as having such list attached.
+      */
+      *last_non_prelocked= prelocking_tables;
+      m_lex->mark_as_requiring_prelocking(lex_query_tables_own_last);
+    }
+    else
+    {
+      /* 
+        Let open_tables_calculate list of tables that this statement needs
+        to have prelocked.
+      */
+      collect_prelocking_tail= TRUE;
+      m_lex->query_tables_non_prelocked_last= NULL;
+    }
+  }
+    
+  reinit_stmt_before_use(thd, m_lex);
   /*
     If requested check whenever we have access to tables in LEX's table list
     and open and lock them before executing instructtions core function.
@@ -1499,6 +1540,32 @@
   thd->proc_info="closing tables";
   close_thread_tables(thd);
 
+  if (thd->prelocked_mode == NON_PRELOCKED)
+  {
+    if (!last_non_prelocked)
+      last_non_prelocked= thd->lex->query_tables_non_prelocked_last;
+    if (last_non_prelocked)
+    {
+      if (collect_prelocking_tail)
+      {
+        /*
+          This is the first time this statement has entered/left prelocked
+          mode on its own. open_tables() has calculated the set of tables this
+          statement needs to have prelocked and added them to the end of
+          m_lex->query_tables(->next_global)*.
+          Save this "tail" for subsequent calls (and restore original list 
+          below)
+        */
+        lex_query_tables_own_last= m_lex->query_tables_own_last;
+        prelocking_tables= *last_non_prelocked;
+      }
+      /*
+        The table list now has list of tables that need to be prelocked
+        when this statement executes, chop it off.
+      */
+      *last_non_prelocked= NULL;
+    }
+  }
   thd->rollback_item_tree_changes();
 
   /*

--- 1.62/sql/sp_head.h	2005-07-09 17:55:09 +00:00
+++ 1.63/sql/sp_head.h	2005-07-30 04:55:49 +00:00
@@ -282,6 +282,10 @@
   /*
     Multi-set representing optimized list of tables to be locked by this
     routine. Does not include tables which are used by invoked routines.
+
+    Note: for prelocking-free SPs this multiset is constructed too.
+    We do so because the same instance of sp_head may be called both
+    in prelocked mode and in non-prelocked mode.
   */
   HASH m_sptabs;
 
@@ -383,7 +387,8 @@
 public:
 
   sp_lex_keeper(LEX *lex, bool lex_resp)
-    : m_lex(lex), m_lex_resp(lex_resp)
+    : m_lex(lex), m_lex_resp(lex_resp), 
+      last_non_prelocked(NULL)
   {
     lex->sp_lex_in_use= TRUE;
   }
@@ -418,6 +423,31 @@
     for LEX deletion.
   */
   bool m_lex_resp;
+
+  /*
+    Support for being able to execute this statement in two modes:
+    a) inside prelocked mode set by the calling procedure or its ancestor.
+    b) outside of prelocked mode, when this statement enters/leaves
+       prelocked mode itself.
+  */
+  
+  /*
+    Pointer to last table in m_lex->query_tables. prelocking_tables list
+    is attached here.
+  */
+  TABLE_LIST **last_non_prelocked;
+  
+  /*
+    List of additional tables this statement needs to lock when it
+    enters/leaves prelocked mode on its own.
+  */
+  TABLE_LIST *prelocking_tables;
+
+  /*
+    The value m_lex->query_tables_own_last should be set to when this
+    statement enters/leaves prelocked mode on its own.
+  */
+  TABLE_LIST **lex_query_tables_own_last;
 };
 
 
--- New file ---
+++ mysql-test/r/sp-prelocking.result	05/07/30 04:55:50
drop database if exists testdb;
drop table if exists t1, t2, t3;
drop procedure if exists sp1;
drop procedure if exists sp2;
drop procedure if exists sp3;
drop procedure if exists sp4;
drop function if exists f1;
drop function if exists f2;
create database testdb;
use testdb//
create procedure sp1 () 
begin
drop table if exists t1;
select 1 as "my-col";
end;
//
select database();
database()
testdb
call sp1();
my-col
1
Warnings:
Note	1051	Unknown table 't1'
select database();
database()
testdb
use test;
select database();
database()
test
call testdb.sp1();
my-col
1
Warnings:
Note	1051	Unknown table 't1'
select database();
database()
test
drop procedure testdb.sp1;
drop database testdb;
create procedure sp1() 
begin 
create table t1 (a int); 
insert into t1 values (10); 
end//
create procedure sp2()
begin
create table t2(a int);
insert into t2 values(1);
call sp1();
end//
create function f1() returns int
begin 
return (select max(a) from t1);
end//
create procedure sp3()
begin 
call sp1();
select 'func', f1();
end//
call sp1();
select 't1',a from t1;
t1	a
t1	10
drop table t1;
call sp2();
select 't1',a from t1;
t1	a
t1	10
select 't2',a from t2;
t2	a
t2	1
drop table t1, t2;
call sp3();
func	f1()
func	10
select 't1',a from t1;
t1	a
t1	10
drop table t1;
drop procedure sp1;
drop procedure sp2;
drop procedure sp3;
drop function f1;
create procedure sp1()
begin
create temporary table t2(a int);
insert into t2 select * from t1;
end//
create procedure sp2()
begin
create temporary table t1 (a int);
insert into t1 values(1);
call sp1();
select 't1', a from t1;
select 't2', b from t2;
drop table t1;
drop table t2;
end//
call sp2();
t1	a
t1	1
drop procedure sp1;
drop procedure sp2;
create table t1 (a int);
insert into t1 values(1),(2);
create table t2 as select * from t1;
create table t3 as select * from t1;
create table t4 as select * from t1;
create procedure sp1(a int)
begin
select a;
end //
create function f1() returns int
begin
return (select max(a) from t1);
end //
CALL sp1(f1());
a
2
create procedure sp2(a int)
begin
select * from t3;
select a;
end //
create procedure sp3()
begin 
select * from t1;
call sp2(5);
end //
create procedure sp4()
begin 
select * from t2;
call sp3();
end //
call sp4();
a
1
1
1
2
a
1
1
2
a
1
1
2
a
5
drop temporary table t1;
drop temporary table t2;
drop procedure sp1;
drop procedure sp2;
drop procedure sp3;
drop procedure sp4;
drop function f1;
drop view if exists v1;
create function f1(ab int) returns int
begin
declare i int;
set i= (select max(a) from t1 where a < ab) ;
return i;
end //
create function f2(ab int) returns int
begin
declare i int;
set i= (select max(a) from t2 where a < ab) ;
return i;
end //
create view v1 as 
select t3.a as x, t4.a as y, f2(3) as z
from t3, t4 where t3.a = t4.a //
create procedure sp1()
begin
declare a int;
set a= (select f1(4) + count(*) A from t1, v1);
end //
create function f3() returns int
begin
call sp1();
return 1;
end //
call sp1() //
select f3() //
f3()
1
select f3() //
f3()
1
call sp1() //
drop procedure sp1//
drop function f3//
create procedure sp1() 
begin 
declare x int;
declare c cursor for select f1(3) + count(*) from v1;
open c;
fetch c into x;
end;//
create function f3() returns int
begin
call sp1();
return 1;
end //
call sp1() //
call sp1() //
select f3() //
f3()
1
call sp1() //
drop table t1,t2,t3;
drop function f1;
drop function f2;
drop function f3;
drop procedure sp1;

--- New file ---
+++ mysql-test/t/sp-prelocking.test	05/07/30 04:55:50
--disable_warnings
drop database if exists testdb;
drop table if exists t1, t2, t3;
drop procedure if exists sp1;
drop procedure if exists sp2;
drop procedure if exists sp3;
drop procedure if exists sp4;
drop function if exists f1;
drop function if exists f2;
--enable_warnings

# BUG#8072 

create database testdb;
delimiter //;
use testdb//
create procedure sp1 () 
begin
  drop table if exists t1;
  select 1 as "my-col";
end;
//
delimiter ;//

select database();
call sp1();
select database();

use test;
select database();
call testdb.sp1();
select database();

drop procedure testdb.sp1;
drop database testdb;

# BUG#8766

delimiter //;
create procedure sp1() 
begin 
  create table t1 (a int); 
  insert into t1 values (10); 
end//

create procedure sp2()
begin
  create table t2(a int);
  insert into t2 values(1);
  call sp1();
end//

create function f1() returns int
begin 
  return (select max(a) from t1);
end//

create procedure sp3()
begin 
  call sp1();
  select 'func', f1();
end//

delimiter ;//

call sp1();
select 't1',a from t1;

drop table t1;
call sp2();
select 't1',a from t1;
select 't2',a from t2;
drop table t1, t2;

call sp3();
select 't1',a from t1;

drop table t1;

drop procedure sp1;
drop procedure sp2;
drop procedure sp3;
drop function f1;

delimiter //;
create procedure sp1()
begin
  create temporary table t2(a int);
  insert into t2 select * from t1;
end//

create procedure sp2()
begin
  create temporary table t1 (a int);
  insert into t1 values(1);
  call sp1();
  select 't1', a from t1;
  select 't2', b from t2;
  drop table t1;
  drop table t2;
end//

delimiter ;//
call sp2();

drop procedure sp1;
drop procedure sp2;

# Miscelaneous tests
create table t1 (a int);
insert into t1 values(1),(2);
create table t2 as select * from t1;
create table t3 as select * from t1;
create table t4 as select * from t1;
delimiter //;
create procedure sp1(a int)
begin
  select a;
end //

create function f1() returns int
begin
  return (select max(a) from t1);
end //

delimiter ;//

CALL sp1(f1());

#############
delimiter //;
create procedure sp2(a int)
begin
  select * from t3;
  select a;
end //

create procedure sp3()
begin 
  select * from t1;
  call sp2(5);
end //

create procedure sp4()
begin 
  select * from t2;
  call sp3();
end //

delimiter ;//
call sp4();

drop temporary table t1;
drop temporary table t2;
drop procedure sp1;
drop procedure sp2;
drop procedure sp3;
drop procedure sp4;
drop function f1;

# Test that prelocking state restoration works with cursors
--disable_warnings
drop view if exists v1;
--enable_warnings
delimiter //;

create function f1(ab int) returns int
begin
  declare i int;
  set i= (select max(a) from t1 where a < ab) ;
  return i;
end //

create function f2(ab int) returns int
begin
  declare i int;
  set i= (select max(a) from t2 where a < ab) ;
  return i;
end //

create view v1 as 
  select t3.a as x, t4.a as y, f2(3) as z
  from t3, t4 where t3.a = t4.a //

create procedure sp1()
begin
  declare a int;
  set a= (select f1(4) + count(*) A from t1, v1);
end //


create function f3() returns int
begin
  call sp1();
  return 1;
end //

call sp1() //

select f3() //
select f3() //

call sp1() //

---------------
drop procedure sp1//
drop function f3//

create procedure sp1() 
begin 
  declare x int;
  declare c cursor for select f1(3) + count(*) from v1;
  open c;
  fetch c into x;
end;//

create function f3() returns int
begin
  call sp1();
  return 1;
end //

call sp1() //
call sp1() //

select f3() //
call sp1() //

delimiter ;//
drop table t1,t2,t3;
drop function f1;
drop function f2;
drop function f3;
drop procedure sp1;


Thread
bk commit into 5.0 tree (sergefp:1.1883) BUG#11126Sergey Petrunia30 Jul