Bug #18344 DROP DATABASE does not drop associated routines
Submitted: 19 Mar 2006 20:37 Modified: 27 Apr 2006 2:50
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.20-bk OS:Linux (Linux)
Assigned to: Bugs System CPU Architecture:Any

[19 Mar 2006 20:37] Kolbe Kegel
Description:
DROP DATABASE will not drop associated stored routines if the database name is greater than 21 characters in length.

How to repeat:
create database `d23456789012345678901`; 
use `d23456789012345678901`;
create procedure testproc() BEGIN END;

create database `d234567890123456789012`; 
use `d234567890123456789012`;
create procedure testproc() BEGIN END;

select schema_name from information_schema.schemata where 
schema_name like 'd2345678901234567890%';
select routine_name,routine_schema from information_schema.routines where routine_schema like 'd2345678901234567890%';

drop database `d23456789012345678901`; 
drop database `d234567890123456789012`; 

select schema_name from information_schema.schemata where 
schema_name like 'd2345678901234567890%';
select routine_name,routine_schema from information_schema.routines where routine_schema like 'd2345678901234567890%';

delete from mysql.proc where db='d234567890123456789012';

mysql> create database `d23456789012345678901`;
Query OK, 1 row affected (0.02 sec)

mysql> use `d23456789012345678901`;
Database changed
mysql> create procedure testproc() BEGIN END;
Query OK, 0 rows affected (0.01 sec)

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

mysql> use `d234567890123456789012`;
Database changed
mysql> create procedure testproc() BEGIN END;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select schema_name from information_schema.schemata where
    -> schema_name like 'd2345678901234567890%';
+------------------------+
| schema_name            |
+------------------------+
| d23456789012345678901  |
| d234567890123456789012 |
+------------------------+
2 rows in set (0.00 sec)

mysql> select routine_name,routine_schema from information_schema.routines where routine_schema like 'd2345678901234567890%';+--------------+------------------------+
| routine_name | routine_schema         |
+--------------+------------------------+
| testproc     | d23456789012345678901  |
| testproc     | d234567890123456789012 |
+--------------+------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> drop database `d23456789012345678901`;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database `d234567890123456789012`;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select schema_name from information_schema.schemata where
    -> schema_name like 'd2345678901234567890%';
Empty set (0.00 sec)

mysql> select routine_name,routine_schema from information_schema.routines where routine_schema like 'd2345678901234567890%';+--------------+------------------------+
| routine_name | routine_schema         |
+--------------+------------------------+
| testproc     | d234567890123456789012 |
+--------------+------------------------+
1 row in set (0.01 sec)

mysql>
mysql> delete from mysql.proc where db='d234567890123456789012';
Query OK, 1 row affected (0.00 sec)

Suggested fix:
Stored routines should be dropped when the database with which they're associated is dropped.
[3 Apr 2006 11:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/4410
[18 Apr 2006 14:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5087
[19 Apr 2006 15:54] Konstantin Osipov
Approved by email.
[21 Apr 2006 10:00] Per-Erik Martin
Pushed to mysql-5.0-runtime.

The problem was an error in the key construction when dropping routines associated with a database, wich made the search fail if the database name was long enough.
[21 Apr 2006 12:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5299
[25 Apr 2006 21:33] Dmitry Lenev
Fixed in 5.0.21 and 5.1.10
[27 Apr 2006 2:50] Paul DuBois
Noted in 5.0.21, 5.1.10 changelogs.

<literal>DROP DATABASE</literal> did not drop stored routines
associated with the database if the database name was longer
than 21 characters. (Bug #18344)