Bug #8936 Stored Procedure: Procedures can be dropped unexpectedly
Submitted: 4 Mar 2005 1:17 Modified: 4 Mar 2005 3:54
Reporter: Shuichi Tamagawa Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.3-alpha-20050216 OS:Linux (SuSE Linux 9.2)
Assigned to: CPU Architecture:Any

[4 Mar 2005 1:17] Shuichi Tamagawa
Description:
Existing Stored Procesure can be dropped by DROP PROCEDURE statement even if the statement specifies non-existing procedure name.

This happens only when mysql is compiled by using multi-byte character set option such as "--with-characterset=ujis".

How to repeat:
mysql>drop procedure if exists sp1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>drop procedure if exists sp2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>delimiter //
mysql>create procedure sp1()
    -> begin
    -> select * from t1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql>drop procedure sp2//
Query OK, 0 rows affected (0.00 sec)

mysql>call sp1()//
ERROR 1305 (42000): PROCEDURE test.sp1 does not exist

Any procedure name in DROP PROCEDURE statement can drop the procedure sp2.

Suggested fix:
Procedure sp1 shouldn't be dropped unless the sp1 is specified in the DROP PROCEDURE statement.
[4 Mar 2005 3:54] MySQL Verification Team
mysql> drop procedure if exists sp1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop procedure if exists sp2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> delimiter //

mysql> create procedure sp1()
    -> begin
    -> select * from t1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> drop procedure sp2//
ERROR 1305 (42000): PROCEDURE test.sp2 does not exist

mysql> call sp1()//
Empty set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)