Mysql UDF is installed (but doesn't exist?)

Question

Here's a crazy one:

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.... that's actually quite funny....

The real problem is that the function is no longer recognized in queries. Tried re-compiling, re-installing, re-starting, etc. -- no joy. UDF is from here: http://www.mysqludf.org/lib_mysqludf_preg/index.php

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

Question is: How do I get the PREG UDF to work after upgrading from mysql to percona 5.5?

ANSWER: Here's the answer based on Baron's tip below:

From the 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 install.

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

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

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

Viola! -- everything loads just fine now.

Answer 1

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

DELETE FROM mysql.func WHERE name='PREG_REPLACE'

And then proceed with your CREATE FUNCTION... statement.