C callouts from MySQL

While I was trying to find a way to use the statistical functions built into MySQL for performance measurement, I discovered the ability to create user defined functions that use C callouts from within the database. I knew you could create your own functions using SQL inside the database, but this allows us to expose OS functionality/information if we need it. This is pretty cool, because we can extend the functionality of MySQL (subject to limitations) without changing the source code. Below is a very simple example of getting the hostname on which the server runs. This is already this functionality in MySQL (“show variables like ‘%host%’), but as noted this is just a simple example. We may have other ways that we could use this technology, such as sending mail from within the database…

[root@linux3 lib]# cat foo.c
#include 
#include 
#include 
#include 
#include 
#include 
#include 

typedef unsigned long long ulonglong;
typedef long long longlong;

#ifdef  __cplusplus
extern "C" {
#endif

//init function is required, and a deinit function is optional.
//These can be used to setup for the function call, and then clean
//up afterwards.

my_bool my_machine_name_init(UDF_INIT *initid,
                                  UDF_ARGS *args,
                                  char *message){
  return 0;
}

char* my_machine_name(UDF_INIT *initid,
                      UDF_ARGS *args,
                      char* result,
                      unsigned long* length,
                      char *is_null,
                      char *error){
  gethostname(result,1000);
  *length = strlen(result);
  return result;
}

We then compile the C code above into a shared library, visible in /usr/lib so mySQL is already aware of it (in the LD_LIBRARY_PATH, but could be anywhere mySQL “knows” about)…

[root@linux3 lib]# gcc -shared -o foo.so foo.c

…and then create a function in the database that references this library.

mysql> drop function my_machine_name;
Query OK, 0 rows affected (0.00 sec)
mysql> create function my_machine_name returns string soname 'foo.so';
Query OK, 0 rows affected (0.00 sec)
mysql> select my_machine_name();
+--------------------------+
| my_machine_name() |
+--------------------------+
| linux3                   |
+--------------------------+
1 row in set (0.01 sec)

mysql> exit
Bye
-bash-3.00$

Like I said, what is above is pretty simple, but I am excited about the possibilities. The code has to be thread safe, and well tested, but it does open some doors for us if the need arises.

You may have to download the MySQL header files.  On Red Hat, simply issue a yum install mysql-devel.  You may also have to adjust your include file search path to find them (under /usr/include/mysql by default).

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.