Monday, March 15, 2010

[Level 3] Create MySQL User Define Function in CentOS.

If you want to create user define function.
You have following steps to do:
1. Compile MySQL source code with option "--with-mysqld-ldflags=--rdynamic"
ex.
  # ./configure --with-mysqld-ldflags=--rdynamic
PS.
mysql> show plugins;
+------------+--------+----------------+---------+---------+
| Name       | Status | Type           | Library | License |
+------------+--------+----------------+---------+---------+
| binlog     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| CSV        | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MEMORY     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MyISAM     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL    | GPL     |
+------------+--------+----------------+---------+---------+

  a. for partitioning: --with-partition # unrecognized
                                 --with-plugins=partition
  b. for innodb: --with-plugins=innobase 
  c. for libmysqld: One difference between the embedded server and the standalone server is that for the embedded server, authentication for connections is disabled by default. To use authentication for the embedded server, specify the --with-embedded-privilege-control

2. prepare UDF function source written in C/C++, and compile it.
ref:
  http://dev.mysql.com/doc/refman/5.0/en/adding-udf.html
My sample compile command for CentOS:
# gcc -shared -o ./udf_example.so ./udf_example.c -I/usr/local/include/mysql -fPIC

3. copy library file to MySQL folder and start MySQL server
# cp ./udf_example.so /usr/local/mysql/lib/mysql/plugin
# mysqld_safe

4. create UDF function by SQL DDL command.

When I try above steps seems fine, except step 4.
I use the SQL command:
mysql> CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so';

But I got the error message like below:
ERROR 1126 (HY000): Can't open shared library 'udf_example.so' (errno: 22 /usr/local/lib/mysql/plugin/udf_example.so: cannot restore segment prot after reloc: Permission denied)

After I google it, I found out that is because of SELinux won't let MySQL to access the linking library.
So I use the command to remove the limitration.
# chcon -t texrel_shlib_t /usr/local/lib/mysql/plugin/udf_example.so

And redo step 4 again, it works.

5. test it
mysql> select metaphon('SD');
+----------------+
| metaphon('SD') |
+----------------+
| ST             |
+----------------+
1 row in set (0.00 sec)

Wish this helps.

regards,
Stanley Huang