diff options
| -rw-r--r-- | mysql-test/r/sp-security.result | 12 | ||||
| -rw-r--r-- | mysql-test/r/sp.result | 16 | ||||
| -rw-r--r-- | mysql-test/t/sp-security.test | 8 | ||||
| -rw-r--r-- | mysql-test/t/sp.test | 26 | ||||
| -rw-r--r-- | sql/sp.cc | 64 | ||||
| -rw-r--r-- | sql/sp.h | 4 | ||||
| -rw-r--r-- | sql/sp_cache.cc | 7 | ||||
| -rw-r--r-- | sql/sp_cache.h | 3 | ||||
| -rw-r--r-- | sql/sql_db.cc | 2 |
9 files changed, 134 insertions, 8 deletions
diff --git a/mysql-test/r/sp-security.result b/mysql-test/r/sp-security.result index 25bceb0f54f..60adad0181c 100644 --- a/mysql-test/r/sp-security.result +++ b/mysql-test/r/sp-security.result @@ -97,11 +97,15 @@ s1 0 2 2 -drop procedure db1_secret.stamp; -drop function db1_secret.db; -drop procedure db2.p; -drop procedure db2.q; use test; +select type,db,name from mysql.proc; +type db name +FUNCTION db1_secret db +PROCEDURE db1_secret stamp +PROCEDURE db2 p +PROCEDURE db2 q drop database db1_secret; drop database db2; +select type,db,name from mysql.proc; +type db name delete from mysql.user where user='user1' or user='user2'; diff --git a/mysql-test/r/sp.result b/mysql-test/r/sp.result index f1fa1735ead..620f22aca68 100644 --- a/mysql-test/r/sp.result +++ b/mysql-test/r/sp.result @@ -786,6 +786,22 @@ select @c1, @c2| 12 3 delete from t1| drop procedure modes| +create database sp_db1| +drop database sp_db1| +create database sp_db2| +use sp_db2| +create table t3 ( s char(4), t int )| +insert into t3 values ("abcd", 42), ("dcba", 666)| +use test| +drop database sp_db2| +create database sp_db3| +use sp_db3| +create procedure dummy(out x int) +set x = 42| +use test| +drop database sp_db3| +select type,db,name from mysql.proc where db = 'sp_db3'| +type db name create procedure bug822(a_id char(16), a_data int) begin declare n int; diff --git a/mysql-test/t/sp-security.test b/mysql-test/t/sp-security.test index ae977684129..8fc51357bc4 100644 --- a/mysql-test/t/sp-security.test +++ b/mysql-test/t/sp-security.test @@ -163,11 +163,11 @@ select * from t2; # Clean up connection con1root; -drop procedure db1_secret.stamp; -drop function db1_secret.db; -drop procedure db2.p; -drop procedure db2.q; use test; +select type,db,name from mysql.proc; drop database db1_secret; drop database db2; +# Make sure the routines are gone +select type,db,name from mysql.proc; +# Get rid of the users delete from mysql.user where user='user1' or user='user2'; diff --git a/mysql-test/t/sp.test b/mysql-test/t/sp.test index 5dae97b371e..943e150ce1f 100644 --- a/mysql-test/t/sp.test +++ b/mysql-test/t/sp.test @@ -905,6 +905,32 @@ delete from t1| drop procedure modes| +# Check that dropping a database without routines works. +# (Dropping with routines is tested in sp-security.test) +# First an empty db. +create database sp_db1| +drop database sp_db1| + +# Again, with a table. +create database sp_db2| +use sp_db2| +# Just put something in here... +create table t3 ( s char(4), t int )| +insert into t3 values ("abcd", 42), ("dcba", 666)| +use test| +drop database sp_db2| + +# And yet again, with just a procedure. +create database sp_db3| +use sp_db3| +create procedure dummy(out x int) + set x = 42| +use test| +drop database sp_db3| +# Check that it's gone +select type,db,name from mysql.proc where db = 'sp_db3'| + + # # Test cases for old bugs # 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 ******************************************************************************/ @@ -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 |