MySQL Bugs: #17012: DROP function not working

Bug #17012	DROP function not working
Submitted:	1 Feb 2006 16:11	Modified:	2 Feb 2006 12:50
Reporter:	Tom De Man	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0	OS:	Windows (Windows 2000)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I have a function I want to drop, but it says it doesn't exist.
The problem I have is that SQLyog doesn't want to continue exporting my database because it stops on this error, which makes it critical.
Below is a copy of 2 lines that were executed shortly after each other, the first showing the function and the second trying to drop it. I logged on as root on the machine itself to do this, so I should have all priviledges.

How to repeat:
mysql> show function status where db = 'vote2006';
+----------+-------------------------------------+----------+----------------+---------------------+---------------------+---------------+---------+
| Db       | Name                                | Type     | Definer        | Modified            | Created             | Security_type | Comment |
+----------+-------------------------------------+----------+----------------+---------------------+---------------------+---------------+---------+
| Vote2006 | FuncCountVariableInTableItemResults | FUNCTION | root@localhost | 2006-01-06 14:13:11 | 2006-01-06 14:13:11 | DEFINER       |         |
+----------+-------------------------------------+----------+----------------+---------------------+---------------------+---------------+---------+
1 row in set (0.00 sec)

mysql> DROP FUNCTION vote2006.FuncCountVariableInTableItemResults;
ERROR 1305 (42000): FUNCTION vote2006.FuncCountVariableInTableItemResults does not exist

Suggested fix:
Could it be the length of my function name that's a bit too long ?

I was unable to repeat this issue with server version 5.0.18. However
we've had already reported bugs with the drop function failing when
was done upgrade without to dump/restore the database from early
versions. If that your case? Which version of server are you using?

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database db7;
Query OK, 1 row affected (0.01 sec)

mysql> use db7
Database changed
mysql> CREATE FUNCTION FuncCountVariableInTableItemResults (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql.proc\G
*************************** 1. row ***************************
              db: db7
            name: FuncCountVariableInTableItemResults
            type: FUNCTION
   specific_name: FuncCountVariableInTableItemResults
        language: SQL
 sql_data_access: CONTAINS_SQL
is_deterministic: NO
   security_type: DEFINER
      param_list: s CHAR(20)
         returns: char(50)
            body: RETURN CONCAT('Hello, ',s,'!')
         definer: root@localhost
         created: 2006-02-01 18:36:31
        modified: 2006-02-01 18:36:31
        sql_mode:
         comment:
1 row in set (0.00 sec)

mysql> SHOW FUNCTION STATUS LIKE 'FuncCountVariableInTableItemResults'\G
*************************** 1. row ***************************
           Db: db7
         Name: FuncCountVariableInTableItemResults
         Type: FUNCTION
      Definer: root@localhost
     Modified: 2006-02-01 18:20:30
      Created: 2006-02-01 18:20:30
Security_type: DEFINER
      Comment:
1 row in set (0.02 sec)

mysql> DROP FUNCTION FuncCountVariableInTableItemResults\G
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW FUNCTION STATUS LIKE 'FuncCountVariableInTableItemResults'\G
Empty set (0.00 sec)

Hi Miguel,
If I make a new function (f1) I can delete it also, so it's specific to this function. I don't know how I got it in there specifically, it was one of my first tests with functions but I stayed with procedures so I want to get rid of it. I didn't upgrade from a previous version I think, I just installed the latest stable downloadable version from the website (5.0) but there were some actions I performed while learning to work with MySQL, I can't tell everythin I did. I've always used SQLyog as an interface, but that's probably no issue ?

Version is 5.0.16

show function status

retrieves information from information_schema/routines no ?

I can see the function there:

select * from information_schema.routines where routine_type="function";

FuncCountVariableInTableItemResults	\N	Vote2006	FuncCountVariableInTableItemResults	FUNCTION	int(11)	SQL	BEGIN
DECLARE iCount INTEGER;
SELECT SUM(tableitemresults.iVotes) FROM tableitemresults WHERE strVariable LIKE paramVariable INTO iCount;
RETURN iCount;
END	\N	\N	SQL	NO	CONTAINS SQL	\N	DEFINER	2006-01-06 14:13:11	2006-01-06 14:13:11			root@localhost
f1	\N	vote2006	f1	FUNCTION	decimal(10,0)	SQL	BEGIN
return "";
END	\N	\N	SQL	NO	CONTAINS SQL	\N	DEFINER	2006-02-01 17:33:52	2006-02-01 17:33:52			root@localhost

f1 I can drop, FuncCountVariableInTableItemResults not

Is the information about this somewhere else also ? I tried to delete the line in information_schema.routines but I got access denied (even as root@localhost). Is there a way to get rid of this function somehow ? If it's gone I can use sqlyog to export my database (if it's still in there sqlyog stops the database)

If you want to reproduce it I can send you all the files in my database, it can't be that large...

FYI: I upgraded to 5.0.18 now but it's still the same.

Try to create a dump using the mysqldump tool with the --force option
and then try to restore it.

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump vote2006 --u=root --routines --force >vote2006.SQL
mysqldump: mysqldump: Couldn't execute 'SHOW CREATE FUNCTION `FuncCountVariableInTableItemResults`': FUNCTION FuncCountVariableInTableItemResults does not exist
 (1305)

Of course, as a workaround I can skip the "--routines" and export all my other stored procedures one by one and import them again.

That will do for now, but there's probably some issue left. If I zip my data folder I get a file of 7.7MB You should be able to reproduce it with that no ? Can I upload that for you ?

Ok, then please provided your database how you commented before
you can upload the file at: ftp://ftp.mysql.com/pub/mysql/upload/
with a file name like bug17012.zip.

Thanks in advance.

I uploaded the file to the ftp server as bug17012.zip

Indeed I got the behavior of one bug already reported, as workaround I
did:

c:\mysql\bin>mysqldump -uroot --routines vote2006 > vote2006.sql
mysqldump: mysqldump: Couldn't execute 'SHOW CREATE FUNCTION `FuncCountVariableInTableItemResults`': FU
ION FuncCountVariableInTableItemResults does not exist (1305)

c:\mysql\bin>mysql -uroot mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.18-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delete from proc where name="FuncCountVariableInTableItemResults";
Query OK, 1 row affected (0.01 sec)

mysql> exit
Bye

c:\mysql\bin>mysqldump -uroot --routines vote2006 > vote2006.sql

c:\mysql\bin>

Changing to duplicate of bug: http://bugs.mysql.com/bug.php?id=16303

with db = sql:

delete from proc where name="FuncCountVariableInTableItemResults";

made it disappear from my original database (without dump and import). That's a good workaround for me, thank you very much !