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 (\u201cshow variables like \u2018%host%\u2019), 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\u2026<\/p>\n
\r\n[root@linux3 lib]# cat foo.c\r\n#include \r\n#include \r\n#include \r\n#include \r\n#include \r\n#include \r\n#include \r\n\r\ntypedef unsigned long long ulonglong;\r\ntypedef long long longlong;\r\n\r\n#ifdef __cplusplus\r\nextern \"C\" {\r\n#endif\r\n\r\n\/\/init function is required, and a deinit function is optional.\r\n\/\/These can be used to setup for the function call, and then clean\r\n\/\/up afterwards.\r\n\r\nmy_bool my_machine_name_init(UDF_INIT *initid,\r\n UDF_ARGS *args,\r\n char *message){\r\n return 0;\r\n}\r\n\r\nchar* my_machine_name(UDF_INIT *initid,\r\n UDF_ARGS *args,\r\n char* result,\r\n unsigned long* length,\r\n char *is_null,\r\n char *error){\r\n gethostname(result,1000);\r\n *length = strlen(result);\r\n return result;\r\n}\r\n<\/pre>\nWe 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 \u201cknows\u201d about)\u2026<\/p>\n
\r\n[root@linux3 lib]# gcc -shared -o foo.so foo.c<\/pre>\n\u2026and then create a function in the database that references this library.<\/p>\n
\r\nmysql> drop function my_machine_name;\r\nQuery OK, 0 rows affected (0.00 sec)\r\nmysql> create function my_machine_name returns string soname 'foo.so';\r\nQuery OK, 0 rows affected (0.00 sec)\r\nmysql> select my_machine_name();\r\n+--------------------------+\r\n| my_machine_name() |\r\n+--------------------------+\r\n| linux3 |\r\n+--------------------------+\r\n1 row in set (0.01 sec)\r\n\r\nmysql> exit\r\nBye\r\n-bash-3.00$\r\n<\/pre>\nLike 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.<\/p>\n
You may have to download the MySQL header files.\u00a0 On Red Hat, simply issue a yum install mysql-devel.\u00a0 You may also have to adjust your include file search path to find them (under \/usr\/include\/mysql by default).<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n