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 <a href="/downloads/mysql/UDF-mysql_udf_lua-0.9.2.tar.gz">http://jan.kneschke.de/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/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 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");
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');
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.
Comments