Bug #18344 DROP DATABASE does not drop associated routines

• Submitted: 19 Mar 2006 20:37
• Modified: 27 Apr 2006 2:50
• Reporter: Kolbe Kegel
• Status: Closed
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.20-bk
• OS: Linux (Linux)
• Assigned to: Per-Erik Martin
• 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)