Description:
In MySQL 5.1, shared object files containing udf's should be placed in the plugin directory. However, a MySQL installed in a non-default basedir will not look in the plugin dir for the shared object file. Rather, it tries to locate the file in /lib/
How to repeat:
My mysql server is located here:
/opt/mysql/5.1/16/mysql-5.1.16-beta-linux-i686-glibc23/
(entire tree from mysql on owned by user mysql, in group mysql)
Use udf_example.c from the sql subdirectory in a source distribution. Move to a convenient location. Compile using:
gcc -I/opt/mysql/5.1/16/mysql-5.1.16-beta-linux-i686-glibc23/include -shared udf_example.c -oudf_example.so
Find the plugin dir:
mysql> show variables like 'plugin_dir';
+---------------+------------------------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------------------------+
| plugin_dir | /opt/mysql/5.1/16/mysql-5.1.16-beta-linux-i686-glibc23/lib/mysql |
+---------------+------------------------------------------------------------------+
Find directory "mysql" not present under "lib", create one manually:
sudo mkdir /opt/mysql/5.1/16/mysql-5.1.16-beta-linux-i686-glibc23/lib/mysql
Copy the .so file there:
sudo cp udf_example.so /opt/mysql/5.1/16/mysql-5.1.16-beta-linux-i686-glibc23/lib/mysql
set permissions:
sudo chown -R mysql /opt/mysql/5.1/16/mysql-5.1.16-beta-linux-i686-glibc23/lib/mysql
sudo chgrp -R mysql /opt/mysql/5.1/16/mysql-5.1.16-beta-linux-i686-glibc23/lib/mysql
create a function from the library:
mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';
ERROR 1126 (HY000): Can't open shared library 'udf_example.so' (errno: 2 udf_example.so: cannot open shared object file: No such file or directory)
moving .so to /opt/mysql/5.1/16/mysql-5.1.16-beta-linux-i686-glibc23/lib/mysql/lib; /opt/mysql/5.1/16/mysql-5.1.16-beta-linux-i686-glibc23/lib/mysql; /opt/mysql/5.1/16/mysql-5.1.16-beta-linux-i686-glibc23/lib/mysql/bin or the data directory does not work either, and gives
ERROR 1126 (HY000): Can't open shared library 'udf_example.so' (errno: 22 udf_example.so: cannot open shared object file: No such file or directory)
Moving the .so to /lib does work.
(For most moves I had to use sudo mv, which would change owner and group; in each case, owner and group where reset to mysql)
Suggested fix:
Not sure really, I suspect it has to do with the non-default location of the mysql server. Have heard people for which it did seem to work with the server located in
/usr/local/mysql (and the plugin dir in /usr/local/mysql/lib/mysql)
