Speeding up MySQL Routines with C using UDF : Part 2 -> MySQL's Example udf_example.c

<- Part 1 : Intro

The first thing I did when researching UDF's was to head over to the MySQL Documentation and read about what was ahead of me. Makes sense, right? If only it were that simple :-)

This page in the MySQL Docs tells us that there's an example udf file that comes with the source code for MySQL.  So, I hopped over to their site, downloaded the source, and found the file.  sql/udf_example.c  Now, admittedly it has been a long time since I wrote any C, or even compiled any (with the exception of ./configure, make, make install type "install from source" stuff)... so I figured their example compile line would work :

dmclain@Percona:~$ gcc -shared -o udf_example.so udf_example.c
udf_example.c:127:23: error: my_global.h: No such file or directory
udf_example.c:128:20: error: my_sys.h: No such file or directory
udf_example.c:139:19: error: mysql.h: No such file or directory
udf_example.c:142: error: syntax error before ‘LOCK_hostname’
udf_example.c:142: warning: data definition has no type or storage class

.. Not even close.  As you can see, it was missing a bunch of include files, so I ended up adding -I/path/to/mysql/includes

dmclain@Percona:~$ gcc -shared -o udf_example.so udf_example.c -I/home/dmclain/mysql_5.1/include
In file included from ./udf_example2.c:127:
./my_global.h:80:23: error: my_config.h: No such file or directory
./my_global.h:602:26: error: my_attribute.h: No such file or directory
./my_global.h:639:21: error: my_dbug.h: No such file or directory
In file included from ./udf_example2.c:127:
./my_global.h:678: error: syntax error before ‘size_socket’
./my_global.h:678: warning: data definition has no type or storage class
./my_global.h:803:2: error: #error "please add -DSTACK_DIRECTION=1 or -1 to your CPPFLAGS"
./my_global.h:871:1: warning: "isnan" redefined
In file included from /usr/include/math.h:28,
 ------ Snip --------

Now keep in mind, I didn't actually install mysql from the source that I downloaded, I was just trying to compile the udf_example.c to use with my existing PerconaDB or MySQL installation.  I didn't want to mess up my existing installation.

So, after a few hours of banging my head against this.. I found this cool site : http://www.mysqludf.org .  This is a group of folks who are trying to being a bit of order to a set of C functions that can be added to your MySQL installation in terms of the parameters, and naming conventions, etc.  What caught my eye mostly tho was the Skeleton package : http://www.mysqludf.org/participation/lib_mysqludf_skeleton.tar.gz

I downloaded and uncompressed this package which seems to include everything I need to compile an example C function for use in UDF in MySQL, which just returns it's own name, using commands I recognize!

dmclain@Percona:~$ autoreconf
dmclain@Percona:~$ ./configure
  -- Snip --
dmclain@Percona:~$ make
  -- Snip --
dmclain@Percona:~$ ls lib_mysqludf_skeleton/.libs
lib_mysqludf_skeleton.a  lib_mysqludf_skeleton.la  lib_mysqludf_skeleton.lai  lib_mysqludf_skeleton_la-lib_mysqludf_skeleton.o  lib_mysqludf_skeleton_la-mysqludf.o  lib_mysqludf_skeleton.so

Now you can use installdb.sql to register it.  I like to know what those kinds of scripts do tho, since I will need to replicate the registration process during the WorkXpress engine rollout that includes this work.  First it drops the function incase it already exists in your database, then then it adds your new function :

CREATE FUNCTION lib_mysqludf_skeleton_info RETURNS STRING SONAME 'lib_mysqludf_skeleton.so';

Notice there's no full path on the front of the SONAME. That's because MySQL has a directory where it puts plugins. You can ask your MySQL Server where *it* stores plugins with :

mysql> show variables like '%plugin%';
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| plugin_dir    | /usr/lib/mysql/plugin |
+---------------+-----------------------+
1 row in set (0.00 sec)

So I added a sym link from /usr/lib/mysql/plugin for my SO in my working area :

dmclain@Percona:~$ sudo ln -s /home/dmclain/lib_mysqludf_skeleton/.libs/lib_mysqludf_skeleton.so  /usr/lib/mysql/plugin/lib_mysqludf_skeleton.so

Then I restarted MySQL so it would find the new plugin; and ran the command :

mysql> select lib_mysqludf_skeleton_info();
+------------------------------+
| lib_mysqludf_skeleton_info() |
+------------------------------+
| lib_mysqludf_skeleton 0.0.1  |
+------------------------------+
1 row in set (0.00 sec)

So, this is step by step to download, compile, register and run the "Skeleton Info" function from http://www.mysqludf.org.

Now that I have a C function running in MySQL.. it's time to make one that does something I need it to do :-)