List:Commits« Previous MessageNext Message »
From:marc.alff Date:October 23 2007 11:23pm
Subject:bk commit into 5.0 tree (malff:1.2543) BUG#12093
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of malff. When malff 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@stripped, 2007-10-23 17:23:18-06:00, malff@stripped. +5 -0
  Bug#12093 (SP not found on second PS execution if another thread drops other
  SP in between)
  
  TEMPORARY PATCH FOR INTERNAL DISCUSION
  
  NOT READY FOR PRODUCTION
  
  (reposted, with test cases)
  
  In particular, while fix patch fixes the issue reported,
  this patch also expose the following existing problem:
  - PREPARE
  - DDL on Stored Functions or Stored Procedures,
    affecting the SF/SP used in the prepared statement
  - EXECUTE
    executes code with an invalid pre-locked set.
  
  See related bug 27430

  mysql-test/r/marc.result@stripped, 2007-10-23 17:23:16-06:00, malff@stripped. +41 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSION
    
    NOT READY FOR PRODUCTION
    

  mysql-test/r/marc.result@stripped, 2007-10-23 17:23:16-06:00, malff@stripped. +0 -0

  mysql-test/t/marc.test@stripped, 2007-10-23 17:23:16-06:00, malff@stripped. +65 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSION
    
    NOT READY FOR PRODUCTION
    

  mysql-test/t/marc.test@stripped, 2007-10-23 17:23:16-06:00, malff@stripped. +0 -0

  sql/sp.cc@stripped, 2007-10-23 17:23:15-06:00, malff@stripped. +129 -45
    TEMPORARY PATCH FOR INTERNAL DISCUSION
    
    NOT READY FOR PRODUCTION

  sql/sp.h@stripped, 2007-10-23 17:23:15-06:00, malff@stripped. +1 -0
    TEMPORARY PATCH FOR INTERNAL DISCUSION
    
    NOT READY FOR PRODUCTION

  sql/sql_prepare.cc@stripped, 2007-10-23 17:23:15-06:00, malff@stripped. +18 -4
    TEMPORARY PATCH FOR INTERNAL DISCUSION
    
    NOT READY FOR PRODUCTION

diff -Nrup a/mysql-test/r/marc.result b/mysql-test/r/marc.result
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/r/marc.result	2007-10-23 17:23:16 -06:00
@@ -0,0 +1,41 @@
+drop table if exists table_12093;
+drop function if exists func_12093;
+drop function if exists func_12093_unrelated;
+drop procedure if exists proc_12093;
+create table table_12093(a int);
+create function func_12093()
+returns int
+begin
+return (select count(*) from table_12093);
+end//
+create procedure proc_12093(a int)
+begin
+select * from table_12093;
+end//
+create function func_12093_unrelated() returns int return 2;
+create procedure proc_12093_unrelated() begin end;
+prepare stmt_sf from 'select func_12093();';
+prepare stmt_sp from 'call proc_12093(func_12093())';
+execute stmt_sf;
+func_12093()
+0
+execute stmt_sp;
+a
+drop function func_12093_unrelated;
+drop procedure proc_12093_unrelated;
+flush tables;
+execute stmt_sf;
+func_12093()
+0
+execute stmt_sp;
+a
+execute stmt_sf;
+func_12093()
+0
+execute stmt_sp;
+a
+deallocate prepare stmt_sf;
+deallocate prepare stmt_sp;
+drop table table_12093;
+drop function func_12093;
+drop procedure proc_12093;
diff -Nrup a/mysql-test/t/marc.test b/mysql-test/t/marc.test
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/t/marc.test	2007-10-23 17:23:16 -06:00
@@ -0,0 +1,65 @@
+
+--disable_warnings
+drop table if exists table_12093;
+drop function if exists func_12093;
+drop function if exists func_12093_unrelated;
+drop procedure if exists proc_12093;
+--enable_warnings
+
+connect (con1,localhost,root,,);
+
+connection default;
+
+create table table_12093(a int);
+
+delimiter //;
+
+create function func_12093()
+returns int
+begin
+  return (select count(*) from table_12093);
+end//
+
+create procedure proc_12093(a int)
+begin
+  select * from table_12093;
+end//
+
+delimiter ;//
+
+create function func_12093_unrelated() returns int return 2;
+create procedure proc_12093_unrelated() begin end;
+
+prepare stmt_sf from 'select func_12093();';
+prepare stmt_sp from 'call proc_12093(func_12093())';
+
+execute stmt_sf;
+execute stmt_sp;
+
+connection con1;
+
+drop function func_12093_unrelated;
+drop procedure proc_12093_unrelated;
+flush tables;
+
+connection default;
+
+# previously, failed with --error 1305
+execute stmt_sf;
+# previously, failed with --error 1305
+execute stmt_sp;
+
+# previously, failed with --error 1305
+execute stmt_sf;
+# previously, failed with --error 1305
+execute stmt_sp;
+
+deallocate prepare stmt_sf;
+deallocate prepare stmt_sp;
+
+disconnect con1;
+
+drop table table_12093;
+drop function func_12093;
+drop procedure proc_12093;
+
diff -Nrup a/sql/sp.cc b/sql/sp.cc
--- a/sql/sp.cc	2007-10-09 17:45:30 -06:00
+++ b/sql/sp.cc	2007-10-23 17:23:15 -06:00
@@ -1559,6 +1559,129 @@ static void sp_update_stmt_used_routines
 }
 
 
+/**
+  This helper ensures that a given Stored Function or Stored Procedure
+  is present in the corresponding per session cache,
+  and loads the Stored Function/Procedure from storage into the cache,
+  if necessary.
+  @param [in] thd Current thread
+  @param [in] type Type of stored object to lookup/load
+  @param [in] name Name of the stored object
+  @param [out] sphp Stored object refreshed in the cache (only for SP_OK)
+  @return An error status
+    @retval SP_OK Success, sphp will contain the stored object fetched
+    @retval SP_KEY_NOT_FOUND The stored object does not exist
+    @retval Others Fatal error
+*/
+static int
+sp_cache_load(THD *thd, int type, sp_name *name, sp_head **sphp)
+{
+  sp_cache **cp;
+  sp_head *sp;
+  int ret;
+
+  cp= (type == TYPE_ENUM_FUNCTION ? &thd->sp_func_cache : &thd->sp_proc_cache);
+
+  sp= sp_cache_lookup(cp, name);
+
+  if (! sp)
+  {
+    switch ((ret= db_find_routine(thd, type, name, &sp)))
+    {
+    case SP_OK:
+      sp_cache_insert(cp, sp);
+      *sphp= sp;
+      break;
+    case SP_KEY_NOT_FOUND:
+      break;
+    default:
+      /*
+        Any error when loading an existing routine is either some problem
+        with the mysql.proc table, or a parse error because the contents
+        has been tampered with (in which case we clear that error).
+      */
+      if (ret == SP_PARSE_ERROR)
+        thd->clear_error();
+      /*
+        If we cleared the parse error, or when db_find_routine() flagged
+        an error with it's return value without calling my_error(), we
+        set the generic "mysql.proc table corrupt" error here.
+       */
+      if (!thd->net.report_error)
+      {
+        /*
+          SP allows full NAME_LEN chars thus he have to allocate enough
+          size in bytes. Otherwise there is stack overrun could happen
+          if multi-byte sequence is `name`. `db` is still safe because the
+          rest of the server checks against NAME_LEN bytes and not chars.
+          Hence, the overrun happens only if the name is in length > 32 and
+          uses multi-byte (cyrillic, greek, etc.)
+
+          !! Change 3 with SYSTEM_CHARSET_MBMAXLEN when it's defined.
+        */
+        char n[NAME_LEN*3*2+2];
+
+        DBUG_ASSERT(name->m_qname.length < sizeof(n));
+
+        /* m_qname.str is not always \0 terminated */
+        memcpy(n, name->m_qname.str, name->m_qname.length);
+        n[name->m_qname.length]= '\0';
+        my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0), n, ret);
+      }
+      break;
+    }
+  }
+  else
+  {
+    ret= SP_OK;
+    *sphp= sp;
+  }
+
+  return ret;
+}
+
+
+/**
+  Refresh all the routines used by a statement in the session cache.
+  Note that routines that can be found are reloaded,
+  while routines that can not be found are ignored.
+  @param [in] thd Current thread
+  @param [in] lex Statement semantic tree
+  @return An error status
+    @retval SP_OK Success
+    @retval Others Fatal error
+*/
+int
+sp_refresh_routines(THD *thd, LEX *lex)
+{
+  Sroutine_hash_entry *start;
+  int ret= 0;
+
+  DBUG_ENTER("sp_recache_routines");
+  start= (Sroutine_hash_entry *)lex->sroutines_list.first;
+
+  for (Sroutine_hash_entry *rt= start; rt; rt= rt->next)
+  {
+    sp_name name(thd, rt->key.str, rt->key.length);
+    int type= rt->key.str[0];
+    sp_head *sp;
+
+    ret= sp_cache_load(thd, type, &name, &sp);
+
+    switch(ret)
+    {
+      case SP_OK:
+      case SP_KEY_NOT_FOUND: /* Ignore routines not found. */
+        ret= SP_OK;
+        break;
+      default:
+        DBUG_RETURN(ret);
+    }
+  }
+  DBUG_RETURN(ret);
+}
+
+
 /*
   Cache sub-set of routines used by statement, add tables used by these
   routines to statement table list. Do the same for all routines used
@@ -1597,60 +1720,21 @@ sp_cache_routines_and_add_tables_aux(THD
   {
     sp_name name(thd, rt->key.str, rt->key.length);
     int type= rt->key.str[0];
-    sp_head *sp;
+    sp_head *sp= NULL;
 
-    if (!(sp= sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
-                              &thd->sp_func_cache : &thd->sp_proc_cache),
-                              &name)))
+    ret= sp_cache_load(thd, type, &name, &sp);
+
+    switch(ret)
     {
-      switch ((ret= db_find_routine(thd, type, &name, &sp)))
-      {
       case SP_OK:
-        {
-          if (type == TYPE_ENUM_FUNCTION)
-            sp_cache_insert(&thd->sp_func_cache, sp);
-          else
-            sp_cache_insert(&thd->sp_proc_cache, sp);
-        }
         break;
       case SP_KEY_NOT_FOUND:
         ret= SP_OK;
         break;
       default:
-        /*
-          Any error when loading an existing routine is either some problem
-          with the mysql.proc table, or a parse error because the contents
-          has been tampered with (in which case we clear that error).
-        */
-        if (ret == SP_PARSE_ERROR)
-          thd->clear_error();
-        /*
-          If we cleared the parse error, or when db_find_routine() flagged
-          an error with it's return value without calling my_error(), we
-          set the generic "mysql.proc table corrupt" error here.
-         */
-        if (!thd->net.report_error)
-        {
-          /*
-            SP allows full NAME_LEN chars thus he have to allocate enough
-            size in bytes. Otherwise there is stack overrun could happen
-            if multibyte sequence is `name`. `db` is still safe because the
-            rest of the server checks agains NAME_LEN bytes and not chars.
-            Hence, the overrun happens only if the name is in length > 32 and
-            uses multibyte (cyrillic, greek, etc.)
-
-            !! Change 3 with SYSTEM_CHARSET_MBMAXLEN when it's defined.
-          */
-          char n[NAME_LEN*3*2+2];
-
-          /* m_qname.str is not always \0 terminated */
-          memcpy(n, name.m_qname.str, name.m_qname.length);
-          n[name.m_qname.length]= '\0';
-          my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0), n, ret);
-        }
-        break;
-      }
+        return ret;
     }
+
     if (sp)
     {
       if (!(first && first_no_prelock))
diff -Nrup a/sql/sp.h b/sql/sp.h
--- a/sql/sp.h	2006-12-23 12:04:26 -07:00
+++ b/sql/sp.h	2007-10-23 17:23:15 -06:00
@@ -86,6 +86,7 @@ void sp_add_used_routine(LEX *lex, Query
                          sp_name *rt, char rt_type);
 void sp_remove_not_own_routines(LEX *lex);
 void sp_update_sp_used_routines(HASH *dst, HASH *src);
+int sp_refresh_routines(THD *thd, LEX *lex);
 int sp_cache_routines_and_add_tables(THD *thd, LEX *lex,
                                      bool first_no_prelock,
                                      bool *tabs_changed);
diff -Nrup a/sql/sql_prepare.cc b/sql/sql_prepare.cc
--- a/sql/sql_prepare.cc	2007-07-12 13:22:14 -06:00
+++ b/sql/sql_prepare.cc	2007-10-23 17:23:15 -06:00
@@ -2922,6 +2922,7 @@ bool Prepared_statement::execute(String 
   Statement stmt_backup;
   Query_arena *old_stmt_arena;
   bool error= TRUE;
+  int sp_ret;
 
   statistic_increment(thd->status_var.com_stmt_execute, &LOCK_status);
 
@@ -3004,10 +3005,23 @@ bool Prepared_statement::execute(String 
   reinit_stmt_before_use(thd, lex);
 
   thd->protocol= protocol;                      /* activate stmt protocol */
-  error= (open_cursor ?
-          mysql_open_cursor(thd, (uint) ALWAYS_MATERIALIZED_CURSOR,
-                            &result, &cursor) :
-          mysql_execute_command(thd));
+
+  /*
+    For tables, the TABLE_SHARE cache automatically detects outdated
+    or missing tables, and transparently reload them.
+    For Stored Procedures and Sored Functions, sp_cache does not
+    provide this functionality, so that the list of routines used
+    by this prepared statement has to be refreshed in the sp_cache
+    explicitly.
+  */
+  sp_ret= sp_refresh_routines(thd, thd->lex);
+  if (sp_ret == SP_OK)
+  {
+    error= (open_cursor ?
+            mysql_open_cursor(thd, (uint) ALWAYS_MATERIALIZED_CURSOR,
+                              &result, &cursor) :
+            mysql_execute_command(thd));
+  }
   thd->protocol= &thd->protocol_simple;         /* use normal protocol */
 
   /* Assert that if an error, no cursor is open */
Thread
bk commit into 5.0 tree (malff:1.2543) BUG#12093marc.alff24 Oct