Date Tags mysql / udf


You are tired of writing your UDFs for MySQL in C or never wanted to write them in C at all ? How about writing them in lua ?

LUA is - easy to learn - easy to embed - easy to use

and not to forget lua has extensions for everything: - LuaSocket - how about some HTTP requests ? - LuaSQL - connecting back to the MySQL Server ? - ...

UDF_Lua is registered in the MySQL Forge.


As example we want to read the real startup time of the errorlog.

-- we are run with the permissions of the mysqld
-- let's try the read the "ready for connections" from the errorlog and look
-- for the last [Note]:
-- 061124 17:28:39 [Note] /usr/sbin/mysqld-max: ready for connections.

local f = assert([1], "r"))
local readysince = nil

while true do
        local line = f:read()

        if not line then break end

        local match = string.match(line, "^([0-9]+ [0-9:]+) %[Note%]")

        if match then
                readysince = match

return readysince

Let's execute it:

> select lua_file(".../ready-since.lua", "/var/lib/mysql/jan-dev.err") as ready_since;
| ready_since     |
| 061125 18:10:48 |

Yeah, that matches the string from the errorlog.

Without a script-file

If shorter script you can also just pass the lua-code around. As usual a string is returned and the parameters are available via the params table.

> select lua("return params[1]", 1) as echo;
| echo |
| 1    |
1 row in set (0.02 sec)


For Aggregation functions like SUM() you have to provide a function which is called for each row (mysql_udf_aggr) and one which returns the final result (mysql_udf_result).

_G["mysql_udf_aggr"] = function (params)
        if nil == rows then
                rows = 0

        rows = rows + params[1]
_G["mysql_udf_result"] = function (params)
        return rows

Running it against the world-database:

root@localhost [world]> select lua_aggr_file("/home/jan/sum.lua", population) from City;
| lua_aggr_file("/home/jan/sum.lua", population) |
| 1429559884                                     |
1 row in set (0.02 sec)

root@localhost [world]> select sum(population) from City;
| sum(population) |
|      1429559884 |
1 row in set (0.01 sec)


Requirements: - lua 5.1

... and the usual UDF requirements

$ wget <a href="/downloads/mysql/UDF-mysql_udf_lua-0.9.2.tar.gz"></a>
$ gzip -cd UDF-mysql_udf_lua-0.9.2.tar.gz | tar xf -
$ cd UDF-mysql_udf_lua-0.9.2
$ ./configure
$ make
## copy to a directory which is checked by dlopen()
# cp ./.libs/ /usr/lib/
$ mysql
mysql> create function lua returns string soname '';
mysql> create function lua_file returns string soname '';
mysql> create function lua_aggr_file returns string soname '';

Thanks to Hartmuts CodeGen_MySQL_UDF the UDF is packaged with automake/autoconf.

parameter handling

The parameters of the SELECT call are passed as a lua-table to the functions. The indexing for this table is starting a 1 as it is expected by lua-devs. The type between MySQL and LUA are mapped as expected

return value

Whatever you return is either nil or converted into a string. In case of a execution error NULL will be return and the error will be logged to the mysqld-error-log.

My MySQL Server sends Mail !

For the UDF talk in a few hours I wanted to have a nice and hopefully useless example. It should show how the internals work and what has to be setup even to those who never have used a C-Compiler.

I chose to implement a function to send mail directly from the database which I hope will never be used. Really. It is not a good idea to move application code into the database.

#include <mysql.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>

my_bool mail_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
        initid->maybe_null = 0;

        return 0;

long long mail(UDF_INIT *initid, UDF_ARGS *args,
               char *is_null, char *error) {
        char *prg;
        FILE *p;
        int i;

        if (args->arg_count != 2) {
                strcpy(error, "MAIL() needs receipient and body");
                return 1;

        if (args->arg_type[0] != STRING_RESULT ||
            args->arg_type[1] != STRING_RESULT) {
                strcpy(error, "MAIL() needs receipient and body as string");
                return 1;

        for (i = 0; i < args->lengths[0]; i++) {
                char c = args->args[0][i];

                if (!((c >= 'a' && c <= 'z') ||
                      (c >= 'A' && c <= 'Z') ||
                      (c == '@') ||
                      (c == '.') ||
                      (c >= '0' && c <= '9') ||
                      (c == '-' ) ||
                      (c == '_' ))) {
                        strcpy(error, "the receipient contains a illegal char");

                        return -1;

        prg = malloc(strlen("/usr/lib/sendmail ") + args->lengths[0] + 1);
        strcpy(prg, "/usr/lib/sendmail ");
        strcat(prg, args->args[0]);

        p = popen(prg, "w");


        if (NULL == p) {
                strcpy(error, "opening pipe failed");
                return -1;

        fwrite(args->args[1], args->lengths[1], 1, p);

        *is_null = 0;
        *error = 0;

        return 0;

That's the code which has to be compiled with:

$ gcc -I /usr/local/mysql/include/ -shared -o mail.c -Wall
$ cp /usr/lib/

at mysql side:

mysql> create function mail returns integer soname '';
mysql> select mail('',
           'Subject: UDF Mail\r\n\r\nYou got mail');

MySQL and memcached

Some time ago I wrote a Proof of Concept for a Memcache UDF based on Sean's libmemcache:

It is not meant for production use as libmemcache uses exit(), assert() and other "shutdown now" calls which zap the whole server if they are hit.


Enable javascript to load comments.