开发者

Cannot use a MySQL UDF function

开发者 https://www.devze.com 2023-03-09 23:37 出处:网络
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.myfu

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 prob开发者_如何学JAVAlem like ("select myfunction ('aaa');") as reported on several other websites.


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


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.


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消