UDF - Function both exists and doesn't exist

[Zolcos]

This is on my Gentoo box.
Recently, a MySQL upgrade seems to have broken my UDFs, specifically those in lib_mysqludf_preg. I first noticed errors like:
FUNCTION tablename.PREG_RLIKE does not exist
I figured, OK, I must have to reinstall them. I try the config command for lib_mysqludf_preg and get this:
ERROR 1125 (HY000) at line 17: Function 'lib_mysqludf_preg_info' already exists
I figured, OK, maybe I have to uninstall them then reinstall them, but that didn't work either. Here is a SQL script that illustrates the problem:

USE mysql;
DROP FUNCTION lib_mysqludf_preg_info;
CREATE FUNCTION lib_mysqludf_preg_info RETURNS STRING SONAME 'lib_mysqludf_preg.so';

and the output:

FUNCTION mysql.lib_mysqludf_preg_info does not exist
Function 'lib_mysqludf_preg_info' already exists

I made sure to update the tables but it didn't matter.
I've tried rolling back the MySQL version but that didn't help.
I even tried using the MySQL version that's still in beta stage in the Portage tree but I get the same result with any version of MySQL I try to use now, even if I unmerge the existing one first.
And of course I've tried unmerging lib_mysqludf_preg and remerging it for each new MySQL version, as well as restarting mysqld (or not) between every step. Same result every time.
Even running these queries as root gives the same errors so I don't think it's a permissions issue.

How can I fix this, and how can I avoid making the same mistake in the future?

[smantscheff]

I don't have a clue, but if this was my problem I would dump the database content and all configuration files and look for the function name. Maybe this provides a hint.
The mysql manual says that you get into trouble if your server runs with the option --skip-grant-tables (http://dev.mysql.com/doc/refman/5.0/en/udf-compiling.html). Maybe you activated this switch during debugging and it is still active?

[Zolcos]

I found the problem. When I re-merged it, portage asked me to run a emerge --config command, and when running that I got this error:

Can't open shared library 'lib_mysqludf_preg.so' (errno: 22 /usr/lib64/mysql/plugin/lib_mysqludf_preg.so: cannot open shared object file: No such file or directory)

But looking at the last bit of compile output, I see the following:

strip: x86_64-pc-linux-gnu-strip --strip-unneeded -R .comment
usr/lib64/lib_mysqludf_preg.so
usr/lib64/lib_mysqludf_preg.a
removing executable bit: usr/lib64/lib_mysqludf_preg.la

So I moved those files to /usr/lib64/mysql/plugin and then the config command worked, and then the functions worked again.

This seems to be a problem with the compile script since it places the libs in the wrong place. It also possibly explains why this broke on a mysql upgrade (changed mysql lib location?). I'd report it on the official bug tracker but that still has unanswered high priority bug rpeorts from 2008 so I don't think (whoever maintains this lib) actually checks it.

[smantscheff]

Thanks for the update. It's always good to know where doom looms.