Cannot use a MySQL UDF function

Question

I have loaded an UDF function into MySQL (without having selected any particular DB). It used to work well during my session but now I get the error "ERROR 1305 (42000): FUNCTION currentdatabase.myfunction does not exist" when I try to use the function with the following sql statement :

select myfunction('aaa');

I then tried to drop the function and I got the same error code :

mysql> drop function myfunction;
ERROR 1305 (42000): FUNCTION database.myfunction does not exist

if a DB is selected.

Another error code otherwise :

ERROR 1046 (3D000): No database selected

So I decided to specify again the function and I got the following error code :

CREATE FUNCTION myfunction RETURNS INT SONAME 'myfunction.so';
ERROR 1125 (HY000): Function 'myfunction' already exists

My question is: how to use again my function ?

Thanks in advance.

Note: there is no space problem like ("select myfunction ('aaa');") as reported on several other websites.

Answer 1

I recently encountered this issue my self and I put together a quick blog post about it:

When installing a UDF recently I got an annoying error message, which didn't seem to want to go away. Deleting the function before attempting to remove it did not work so I used the following set of escalating commands to attempt to get it to install.

But back to the error for a moment:

bash > mysql -u user -p < installdb.sql
Enter password:
ERROR 1125 (HY000) at line 7: Function 'lib_mysqludf_ssdeep_info' already exists

This can be solved really simply with the following options:

1. Attempt to delete the function and then reinstall it
2. Delete the function row from the mysql.func table and then reinstall it
3. Stop the MySQL server (after trying option 2), start it again and then reinstall it

From my testing you do not need to have backups of your binary database files as @jmcejuela suggests in his answer.

Source: http://simonholywell.com/post/2012/01/mysql-udf-install-error-function-already-exists.html

Answer 2

I believe the problem comes from removing the .so library before dropping the function/s. The server still believes it has the functions since they appear in SELECT * FROM mysql.func but of course calling these functions fail. Somehow DROP doesn't simply remove entries in this table but also checks whether the libraries are found which makes this fail. CREATE supposedly just check first the func table which makes this fail...

The solution is to restore the func table (func.MYI, func.MYD, func.frm files under your mysql's data/mysql) to the point everything matches.

Luckily I had a backup of these files. Otherwise you will have to backup them from a new mysql installation (you could simply install locally a new server not to remove your current one)

Bottom line: do not remove the .so libraries before dropping the functions.