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.

I'm having the same problem. I believe the problem is that we (at least I did) remove the shared .so library before dropping the function or while mysqld was still running. Don't know how to solve this, someone? SELECT * FROM mysql.func lists the function I want to use. — juanmirocks
– juanmirocks, Commented Nov 10, 2011 at 10:17

Treffynnon · Accepted Answer · 2013-04-30 12:05:14Z

6

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:

Attempt to delete the function and then reinstall it

Delete the function row from the mysql.func table and then reinstall it

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

edited Apr 30, 2013 at 12:05

answered Jan 31, 2012 at 11:50

Treffynnon

21.6k6 gold badges68 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kfmfe04 Over a year ago

+1 2. deleting the function row from mysql.func, restarting the server, and then re-creating the function worked for me.

Brian Leishman Over a year ago

This is not working for me, seems like my percona xtradb cluster makes this a bit more complicated

juanmirocks · Accepted Answer · 2011-11-10 14:09:31Z

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.

dkloke · Accepted Answer · 2013-05-19 21:26:26Z

-1

Got some of these errors when swapping out (not dropping) the .so library before dropping the function(s).

Restarting the server eliminated the error messages, whether the functions were successfully dropped or not. Mysqld just looked at the mysql.func table and loaded functions from the (new) .so. I know restarting the server is not always an option, but if it is, it's fast and complete.

answered May 19, 2013 at 21:26

dkloke

4365 silver badges12 bronze badges

Collectives™ on Stack Overflow

Cannot use a MySQL UDF function

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related