WL#1366: Use the schema (db) associated with an SP.

Phase 4 (final): Remove associated stored procedures when a database is dropped.


mysql-test/r/sp-security.result:
  drop database now deletes associated SPs.
mysql-test/r/sp.result:
  drop database now deletes associated SPs.
mysql-test/t/sp-security.test:
  drop database now deletes associated SPs.
mysql-test/t/sp.test:
  drop database now deletes associated SPs.
sql/sp.cc:
  New function for deleting all SPs associated with a database.
sql/sp.h:
  New function for deleting all SPs associated with a database.
sql/sp_cache.cc:
  New function for just invalidating all SP caches (when dropping a database).
sql/sp_cache.h:
  New function for just invalidating all SP caches (when dropping a database).
sql/sql_db.cc:
  When dropping a database, also delete all associated SPs.

5 files changed, 80 insertions, 0 deletions

diff --git a/sql/sp.cc b/sql/sp.cc
index 389c627f5f3..ede6cfeb84b 100644
--- a/sql/sp.cc
+++ b/sql/sp.cc
@@ -589,6 +589,70 @@ done:
 }
 
 
+/* Drop all routines in database 'db' */
+int
+sp_drop_db_routines(THD *thd, char *db)
+{
+  TABLE *table;
+  byte key[64];			// db
+  uint keylen;
+  int ret;
+  DBUG_ENTER("sp_drop_db_routines");
+  DBUG_PRINT("enter", ("db: %s", db));
+
+  // Put the key used to read the row together
+  keylen= strlen(db);
+  if (keylen > 64)
+    keylen= 64;
+  memcpy(key, db, keylen);
+  memset(key+keylen, (int)' ', 64-keylen); // Pad with space
+  keylen= sizeof(key);
+
+  for (table= thd->open_tables ; table ; table= table->next)
+    if (strcmp(table->table_cache_key, "mysql") == 0 &&
+	strcmp(table->real_name, "proc") == 0)
+      break;
+  if (! table)
+  {
+    TABLE_LIST tables;
+
+    memset(&tables, 0, sizeof(tables));
+    tables.db= (char*)"mysql";
+    tables.real_name= tables.alias= (char*)"proc";
+    if (! (table= open_ltable(thd, &tables, TL_WRITE)))
+      DBUG_RETURN(SP_OPEN_TABLE_FAILED);
+  }
+
+  ret= SP_OK;
+  table->file->index_init(0);
+  if (! table->file->index_read(table->record[0],
+				key, keylen, HA_READ_KEY_EXACT))
+  {
+    int nxtres;
+    bool deleted= FALSE;
+
+    do {
+      if (! table->file->delete_row(table->record[0]))
+	deleted= TRUE;		/* We deleted something */
+      else
+      {
+	ret= SP_DELETE_ROW_FAILED;
+	break;
+      }
+    } while (! (nxtres= table->file->index_next_same(table->record[0],
+						     key, keylen)));
+    if (nxtres != HA_ERR_END_OF_FILE)
+      ret= SP_KEY_NOT_FOUND;
+    if (deleted)
+      sp_cache_invalidate();
+  }
+
+  close_thread_tables(thd);
+
+  DBUG_RETURN(ret);
+}
+
+
 /*****************************************************************************
   PROCEDURE
 ******************************************************************************/
diff --git a/sql/sp.h b/sql/sp.h
index ffe3f31c157..a4fec50aca2 100644
--- a/sql/sp.h
+++ b/sql/sp.h
@@ -28,6 +28,10 @@
 #define SP_PARSE_ERROR       -6
 #define SP_INTERNAL_ERROR    -7
 
+/* Drop all routines in database 'db' */
+int
+sp_drop_db_routines(THD *thd, char *db);
+
 sp_head *
 sp_find_procedure(THD *thd, sp_name *name);
 
diff --git a/sql/sp_cache.cc b/sql/sp_cache.cc
index e13fb2695e7..056ac6d7e96 100644
--- a/sql/sp_cache.cc
+++ b/sql/sp_cache.cc
@@ -115,6 +115,13 @@ sp_cache_remove(sp_cache **cp, sp_name *name)
   return found;
 }
 
+void
+sp_cache_invalidate()
+{
+  pthread_mutex_lock(&Cversion_lock); // LOCK
+  Cversion++;
+  pthread_mutex_unlock(&Cversion_lock); // UNLOCK
+}
 
 static byte *
 hash_get_key_for_sp_head(const byte *ptr, uint *plen,
diff --git a/sql/sp_cache.h b/sql/sp_cache.h
index 253e9b11588..754a987090e 100644
--- a/sql/sp_cache.h
+++ b/sql/sp_cache.h
@@ -40,6 +40,9 @@ sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name);
 /* Remove an SP from cache. Returns true if something was removed */
 bool sp_cache_remove(sp_cache **cp, sp_name *name);
 
+/* Invalidate a cache */
+void sp_cache_invalidate();
+
 
 /*
  *
diff --git a/sql/sql_db.cc b/sql/sql_db.cc
index bc6b30040d6..54265b58bd4 100644
--- a/sql/sql_db.cc
+++ b/sql/sql_db.cc
@@ -19,6 +19,7 @@
 
 #include "mysql_priv.h"
 #include "sql_acl.h"
+#include "sp.h"
 #include <my_dir.h>
 #include <m_ctype.h>
 #ifdef __WIN__
@@ -386,6 +387,7 @@ int mysql_rm_db(THD *thd,char *db,bool if_exists, bool silent)
   }
 
 exit:
+  (void)sp_drop_db_routines(thd, db); /* QQ Ignore errors for now  */
   start_waiting_global_read_lock(thd);
   /*
     If this database was the client's selected database, we silently change the