Bug #16303 erroneus stored procedures and functions should be droppable
Submitted: 9 Jan 2006 15:06 Modified: 6 Feb 2006 23:57
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.0.18 OS:Linux (Linux)
Assigned to: Bugs System CPU Architecture:Any

[9 Jan 2006 15:06] Domas Mituzas
Description:
If a procedure was valid in previous versions, new versions should allow to drop it, rather than complaining about contents and doing nothing.

How to repeat:
5.0.16:
mysql> show create function test.f1 \G
*************************** 1. row ***************************
       Function: f1
       sql_mode: 
Create Function: CREATE FUNCTION `f1`(a decimal(5,1)) RETURNS decimal(5,1)
begin
DECLARE tc     DECIMAL(3, 9);
return tc;
end
1 row in set (0.00 sec)

5.0.18: 
mysql> show create function f1;
ERROR 1427 (42000): For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column '').
mysql> drop function f1;
ERROR 1427 (42000): For float(M,D), double(M,D) or decimal(M,D), M must be >= D (column '').

Suggested fix:
do not do syntax checks on DROP
[25 Jan 2006 14:41] Konstantin Osipov
Bug#14977 has been marked as a duplicate of this bug.
[26 Jan 2006 10:22] Per-Erik Martin
I think this is more serious than P4, as it might prevent upgrading from older versions.

A workaround (until this is fixed):

  delete from mysql.proc where db='mydb' and name='myproc' and type='type';

where 'type' is 'FUNCTION' or 'PROCEDURE'.

Note: Using the system table mysql.proc directly like this is normally not supported, it's a workaround only. It (of course) requires root privileges.
[26 Jan 2006 12:29] 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/1673
[2 Feb 2006 12:38] Miguel Solorzano
Bug http://bugs.mysql.com/bug.php?id=17012 was marked as duplicate
of this bug.
[2 Feb 2006 13:23] Andrey Hristov
Check if this is ok also for ALTER PROCEDURE/FUNCTION ?
[2 Feb 2006 13:25] Andrey Hristov
+    if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK)
+      ret= SP_OK;
+    else
+      ret= SP_KEY_NOT_FOUND;

could be
+    if ((ret= db_find_routine_aux(thd, type, name, table)) != SP_OK)
+      ret= SP_KEY_NOT_FOUND;
[2 Feb 2006 14:29] Per-Erik Martin
>Check if this is ok also for ALTER PROCEDURE/FUNCTION ?

No, not altering a routine that can't be parsed is meaningless, it can never be used. (Since the body can't be altered.)
[2 Feb 2006 14:36] 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/2074
[3 Feb 2006 18:57] Konstantin Osipov
Approved by email.
[6 Feb 2006 17:08] Per-Erik Martin
Pushed to bk 5.0.19
[6 Feb 2006 23:57] Mike Hillyer
Documented in 5.0.19 changelog:

      <listitem>
        <para>
          An invalid stored routine could not be dropped. (Bug #16303)
        </para>
      </listitem>