BUG#21414: SP: Procedure undroppable, to some extent

The problem was that if after FLUSH TABLES WITH READ LOCK the user
issued DROP/ALTER PROCEDURE/FUNCTION the operation would fail (as
expected), but after UNLOCK TABLE any attempt to execute the same
operation would lead to the error 1305 "PROCEDURE/FUNCTION does not
exist", and an attempt to execute any stored function will also fail.

This happened because under FLUSH TABLES WITH READ LOCK we couldn't open
and lock mysql.proc table for update, and this fact was erroneously
remembered by setting mysql_proc_table_exists to false, so subsequent
statements believed that mysql.proc doesn't exist, and thus that there
are no functions and procedures in the database.

As a solution, we remove mysql_proc_table_exists flag completely.  The
reason is that this optimization didn't work most of the time anyway.
Even if open of mysql.proc failed for some reason when we were trying to
call a function or a procedure, we were setting mysql_proc_table_exists
back to true to force table reopen for the sake of producing the same
error message (the open can fail for number of reasons).  The solution
could have been to remember the reason why open failed, but that's a lot
of code for optimization of a rare case.  Hence we simply remove this
optimization.


mysql-test/r/sp.result:
  Add result for bug#21414: SP: Procedure undroppable, to some extent.
mysql-test/t/sp.test:
  Remove no longer relevant comment.
  Add test case for bug#21414: SP: Procedure undroppable, to some extent.
sql/mysql_priv.h:
  Remove declaration of mysql_proc_table_exists.
sql/sp.cc:
  Remove references to mysql_proc_table_exists.
sql/sql_acl.cc:
  Remove reference to mysql_proc_table_exists.

1 files changed, 0 insertions, 27 deletions

diff --git a/sql/sp.cc b/sql/sp.cc
index fc72822c15e..43eff39a463 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -66,8 +66,6 @@ enum
   MYSQL_PROC_FIELD_COUNT
 };
 
-bool mysql_proc_table_exists= 1;
-
 /* Tells what SP_DEFAULT_ACCESS should be mapped to */
 #define SP_DEFAULT_ACCESS_MAPPING SP_CONTAINS_SQL
 
@@ -119,13 +117,6 @@ TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup)
   bool not_used;
   DBUG_ENTER("open_proc_table");
 
-  /*
-    Speed up things if mysql.proc doesn't exists. mysql_proc_table_exists
-    is set when we create or read stored procedure or on flush privileges.
-  */
-  if (!mysql_proc_table_exists)
-    DBUG_RETURN(0);
-
   thd->reset_n_backup_open_tables_state(backup);
 
   bzero((char*) &tables, sizeof(tables));
@@ -135,7 +126,6 @@ TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup)
                           MYSQL_LOCK_IGNORE_FLUSH)))
   {
     thd->restore_backup_open_tables_state(backup);
-    mysql_proc_table_exists= 0;
     DBUG_RETURN(0);
   }
 
@@ -184,15 +174,6 @@ static TABLE *open_proc_table_for_update(THD *thd)
 
   table= open_ltable(thd, &tables, TL_WRITE);
 
-  /*
-    Under explicit LOCK TABLES or in prelocked mode we should not
-    say that mysql.proc table does not exist if we are unable to
-    open and lock it for writing since this condition may be
-    transient.
-  */
-  if (!(thd->locked_tables || thd->prelocked_mode) || table)
-    mysql_proc_table_exists= test(table);
-
   DBUG_RETURN(table);
 }
 
@@ -1610,14 +1591,6 @@ sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
       case SP_KEY_NOT_FOUND:
         ret= SP_OK;
         break;
-      case SP_OPEN_TABLE_FAILED:
-        /*
-          Force it to attempt opening it again on subsequent calls;
-          otherwise we will get one error message the first time, and
-          then ER_SP_PROC_TABLE_CORRUPT (below) on subsequent tries.
-        */
-        mysql_proc_table_exists= 1;
-        /* Fall through */
       default:
         /*
           Any error when loading an existing routine is either some problem