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 
#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.