Mysql UDF installed (but does not exist?)

Question

Here's crazy:

mysql> CREATE FUNCTION PREG_REPLACE RETURNS STRING SONAME 'lib_mysqludf_preg.so'; ERROR 1125 (HY000): Function 'PREG_REPLACE' already exists mysql> DROP FUNCTION preg_replace; ERROR 1305 (42000): FUNCTION (UDF) preg_replace does not exist 

Um ... which is actually pretty funny ....

The real problem is that the function is no longer recognized in queries. Tried to recompile, reinstall, restart, etc. - without joy. UDF from here: http://www.mysqludf.org/lib_mysqludf_preg/index.php

This came after switching to percona 5.5 from mysql. UDF worked fine in mysql.

Question: How to get PREG UDF to work after upgrading from mysql to percona 5.5?

ANSWER: Here the answer is based on the advice of the baron below:

From mysql error.log:

 120319 9:32:06 Percona XtraDB (http://www.percona.com) 1.1.8-rel24.1 started; log sequence number 1547303885 120319 9:32:06 [ERROR] Can't open shared library 'lib_mysqludf_preg.so' (errno: 0 /usr/lib/plugin/lib_mysqludf_preg.so: cannot open shared object file: No such file or directory) 

Percona seems to look in a different directory than my standard MySql installation.

MySql looks for all plugins in / usr / lib / mysql / plugin. Percona browsed / usr / lib / plugin

The solution was simple - I just created a symbolic link in the / usr / lib directory in the / usr / lib / mysql / plugin directory as follows:

 me@host:/usr/lib/plugin$ sudo ln -s /usr/lib/mysql/plugin ./plugin 

Viola! - Now everything loads fine.

Answer 1

I get it a lot. To solve this problem you should:

 DELETE FROM mysql.func WHERE name='PREG_REPLACE' 

And then proceed to the CREATE FUNCTION... statement.