| 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: | |
| Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
| Version: | 5.0.18 | OS: | Linux (Linux) |
| Assigned to: | Per-Erik Martin | CPU Architecture: | Any |
[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]
MySQL Verification Team
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>
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