Bug #12252 Stored Procedures: DROP DATABASE should delete all SPs

• Submitted: 28 Jul 2005 22:18
• Modified: 31 Jul 2005 7:16
• Reporter: Trudy Pelzer
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.10-beta-debug
• OS: Linux (SuSE 9.2)
• CPU Architecture: Any

[28 Jul 2005 22:18] Trudy Pelzer

Description:
When a database is dropped, all associated stored
procedures (both functions and procedures) should
also be dropped. This is not happening.

How to repeat:
-- make a stored procedure and a stored function in some 
database (e.g. tp2)

mysql> select routine_name,routine_type from information_schema.routines where routine_schema='tp2'\G
*************************** 1. row ***************************
routine_name: f1
routine_type: FUNCTION
*************************** 2. row ***************************
routine_name: p1
routine_type: PROCEDURE
2 rows in set (0.01 sec)

mysql> drop database tp2;
Query OK, 1 row affected (0.03 sec)

mysql> use tp;
Database changed

mysql> select routine_name,routine_type from information_schema.routines where routine_schema='tp2'\G
*************************** 1. row ***************************
routine_name: f1
routine_type: FUNCTION
*************************** 2. row ***************************
routine_name: p1
routine_type: PROCEDURE
2 rows in set (0.00 sec)
-- This is the incorrect response. Since database tp2 no
longer exists, all objects within that database should 
also have been dropped.

[28 Jul 2005 23:15] MySQL Verification Team

I was unable to repeat the behavior reported with server build from
BK source 2 days older:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.11-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

<cut>

mysql> select routine_name,routine_type from information_schema.routines where
    -> routine_schema='tp2'\G
*************************** 1. row ***************************
routine_name: f1
routine_type: FUNCTION
*************************** 2. row ***************************
routine_name: p1
routine_type: PROCEDURE
2 rows in set (0.02 sec)

mysql> delimiter ;
mysql> drop database tp2;    
Query OK, 0 rows affected (0.05 sec)

mysql> use tp;
Database changed
mysql> select routine_name,routine_type from information_schema.routines where
    -> routine_schema='tp2'\G
Empty set (0.00 sec)

mysql>

[29 Jul 2005 0:29] Trudy Pelzer

Hi Miguel,
I just did a fresh pull (at about 18:00 MT on 2005-07-28)
and build and still have the problem:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.11-beta-debug

mysql> create database tp2;
Query OK, 1 row affected (0.00 sec)

mysql> use tp2;
Database changed

mysql> create table t (col1 int);
Query OK, 0 rows affected (0.73 sec)

mysql> insert into t values (10);
Query OK, 1 row affected (0.00 sec)

mysql> create procedure p1 () insert into t values (100);
Query OK, 0 rows affected (0.23 sec)

mysql> create function f1 () returns decimal(3,2) return 3.14;
Query OK, 0 rows affected (0.07 sec)

mysql> drop database tp2;
Query OK, 1 row affected (0.11 sec)

mysql> use tp;
Database changed

mysql> select routine_name,routine_type from information_schema.routines where routine_schema='tp2'\G
*************************** 1. row ***************************
routine_name: f1
routine_type: FUNCTION
*************************** 2. row ***************************
routine_name: p1
routine_type: PROCEDURE
2 rows in set (0.00 sec)

[29 Jul 2005 1:05] MySQL Verification Team

Still I was unable to repeat with the very latest BK pull server:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.11-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database tp2;
Query OK, 1 row affected (0.00 sec)

mysql> use tp2;
Database changed
mysql> create table t (col1 int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t values (10);
Query OK, 1 row affected (0.01 sec)

mysql> create procedure p1 () insert into t values (100);
Query OK, 0 rows affected (0.01 sec)

mysql> create function f1 () returns decimal(3,2) return 3.14;
Query OK, 0 rows affected (0.01 sec)

mysql> drop database tp2;
Query OK, 1 row affected (0.01 sec)

mysql> use tp;
Database changed
mysql> select routine_name,routine_type from information_schema.routines where
    -> routine_schema='tp2'\G
Empty set (0.00 sec)

mysql> 

For to avoid any doubts about my environment I deleted the data 
directory and run mysql_install_db for a fresh mysql db.

[31 Jul 2005 7:16] Jorge del Conde

I was unable to reproduce w/FC4