MySQL and memcached

Get

• libmemcache-1.3.x from http://people.freebsd.org/~seanc/libmemcache/ and install it.
• udf_memcache.c
• create-function-memcache.sql

Compile it with:

$ gcc -DDBUG_OFF -shared -o udf_memcache.so udf_memcache.c /
   -g -Wall -O2 -lmemcache `mysql_config --cflags`

Copy udf_memcache.so into a directory which contains the your shared libs (e.g. /usr/local/lib).

Install the functions into MySQL by executing the create-function script:

$ mysql < create-function-memcache.sql

UDF_LUA

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.

functions

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(io.open(params[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
        end
end
f:close()

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)

Aggregation

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
        end

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

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)

Installation

Requirements:

• lua 5.1

... and the usual UDF requirements

$ wget http://jan.kneschke.de/projects/mysql/udf/UDF-mysql_udf_lua-0.9.2.tar.gz
$ 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/mysql_udf_lua.so /usr/lib/
$ mysql
mysql> create function lua returns string soname 'mysql_udf_lua.so';
mysql> create function lua_file returns string soname 'mysql_udf_lua.so';
mysql> create function lua_aggr_file returns string soname 'mysql_udf_lua.so';

Thanks to Hartmuts CodeGenMySQLUDF 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");

        free(prg);

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


        fwrite(args->args[1], args->lengths[1], 1, p);
        fclose(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 udf_mail.so mail.c -Wall
$ cp udf_mail.so /usr/lib/

at mysql side:

mysql> create function mail returns integer soname 'udf_mail.so';
mysql> select mail('jan@kneschke.de',
           'Subject: UDF Mail/r/n/r/nYou got mail');