MyLua is a UDF (User-Defined Function) of MySQL. It makes possible to fast query on the access pattern not optimized in SQL.

MyLua requires Lua (or LuaJIT), Lua CJSON, and MySQL source code.

URL

www.lua.org

tested version

5.1.4

cd mylua
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
tar zxvf lua-5.1.4.tar.gz
mv lua-5.1.4 lua
cd lua
sed -i 's/MYCFLAGS=-DLUA_USE_POSIX/MYCFLAGS="-DLUA_USE_POSIX -fPIC"/g' src/Makefile
make posix
make local

URL

luajit.org

tested versoin

2.0.0-beta9

cd mylua
wget http://luajit.org/download/LuaJIT-2.0.0-beta9.tar.gz
tar zxvf LuaJIT-2.0.0-beta9.tar.gz
mv LuaJIT-2.0.0-beta9 luajit
cd luajit
sed -i 's/STATIC_CC = $(CROSS)$(CC)/STATIC_CC = $(CROSS)$(CC) -fPIC/g' src/Makefile
make
make install PREFIX=`pwd`

if using luajit, ‘lua execution timeout` does not work correctly.

URL

www.kyne.com.au/~mark/software/lua-cjson.php

tested version

1.0.4

cd mylua
wget http://www.kyne.com.au/~mark/software/download/lua-cjson-1.0.4.tar.gz
tar zxvf lua-cjson-1.0.4.tar.gz
mv lua-cjson-1.0.4 lua-cjson
cd lua-cjson
env LUA_INCLUDE_DIR=../lua/include LUA_LIB_DIR=../lua/lib make
# (if failed, then do `make clean` before retry.)
# (if locale error has occured, then comment out -DUSE_POSIX_USELOCALE and retry make)
# sed -i 's/CFLAGS_EXTRA =      -DUSE_POSIX_USELOCALE/#CFLAGS_EXTRA =      -DUSE_POSIX_USELOCALE/g' Makefile
ar rv cjson.a lua_cjson.o strbuf.o

REF: www.hi-ho.ne.jp/babaq/linux/libtips.html (japanese)

URL

www.mysql.com

tested version

5.1.41

cd mylua
wget http://downloads.mysql.com/archives/mysql-5.1/mysql-5.1.41.tar.gz
tar zxvf mysql-5.1.41.tar.gz
mv mysql-5.1.41 mysql
cd mysql
./configure
cp include/config.h include/my_config.h

check directory tree is like this.

+ mylua/
 + lua/
 + luajit/
 + lua-cjson/
 + mysql/
 ... and etc

cd mylua

# if use lua,
g++ -O2 -lm -ldl -Wall -nostartfiles -shared -fPIC -L /usr/lib \
 -I ./lua/include/ -I ./mysql/include -I ./mysql/sql -I ./mysql/regex \
 src/mylua.cc lua/lib/liblua.a lua-cjson/cjson.a \
 -o mylua.so

# else if use luajit,
g++ -O2 -lm -ldl -Wall -nostartfiles -shared -fPIC -L /usr/lib \
 -I ./luajit/include/luajit-2.0 -I ./mysql/include -I ./mysql/sql -I ./mysql/regex \
 src/mylua.cc luajit/lib/libluajit-5.1.a lua-cjson/cjson.a \
 -D MYLUA_USE_LUAJIT \
 -o mylua.so

sudo cp mylua.so /usr/lib/mysql/plugin/
mysql -u root -e "create function mylua returns string soname 'mylua.so'" -p

$ mysql -u localuser localuser -e '
  CREATE TABLE timeline (
    user_id   int unsigned NOT NULL,
    status_id int unsigned NOT NULL,
    PRIMARY KEY (status_id),
    KEY user_id (user_id, status_id)
  ) ENGINE=InnoDB;

  INSERT timeline (user_id, status_id)
  VALUES (1,11),(1,12),(2,21),(3,31),(3,32),(3,33),(5,51);
'

$ mysql -u localuser -e '
  SELECT mylua('\''
    local t = {};
    mylua.init_table("localuser", "timeline", "user_id", "user_id", "status_id");
    mylua.index_read_map(mylua.HA_READ_KEY_OR_NEXT, mylua.arg.uid, mylua.arg.sid);
    table.insert(t, { mylua.val_int("user_id"), mylua.val_int("status_id") });
    mylua.index_next();
    table.insert(t, { mylua.val_int("user_id"), mylua.val_int("status_id") });
    return t;
  '\'', '\''{"uid":3,"sid":32}'\'');
' -p

[[3,32],[3,33]]

execute lua_code with argument json_arg.

returned json structure:

ok

if no error, then assigned true.

message

if error, then assigned error message.

data

if no error, then assgined value returned from lua_code.

# sample
mysql> SELECT mylua('return mylua.arg.foo', '{"foo":3}') AS json;
+-----------------------------+
| json                        |
+-----------------------------+
| {"ok":true,"data":3}        |
+-----------------------------+
1 row in set (0.00 sec)

init table for index search. field(N) must be part of index.

init extra field for get value. field(N) can be not part of index.

index search. rkey is below.

• mylua.HA_READ_KEY_EXACT

• mylua.HA_READ_KEY_OR_NEXT

• mylua.HA_READ_KEY_OR_PREV

• mylua.HA_READ_AFTER_KEY

• mylua.HA_READ_BEFORE_KEY

• mylua.HA_READ_PREFIX

• mylua.HA_READ_PREFIX_LAST

• mylua.HA_READ_PREFIX_LAST_OR_PREV

• mylua.HA_READ_MBR_CONTAIN

• mylua.HA_READ_MBR_INTERSECT

• mylua.HA_READ_MBR_WITHIN

• mylua.HA_READ_MBR_DISJOINT

• mylua.HA_READ_MBR_EQUAL

if matched, then returns 0.

if not matched, then returns mylua.HA_ERR_END_OF_FILE (== 137).

if other errors, then returns not 0 integer.

search previous row from index.

if no errors, then returns 0.

if not found, then returns mylua.HA_ERR_END_OF_FILE (== 137).

if other errors, then returns not 0 integer.

search next row from index.

if no errors, then returns 0.

if not found, then returns mylua.HA_ERR_END_OF_FILE (== 137).

if other errors, then returns not 0 integer.

get current value of field.

# sample

mysql> SHOW CREATE TABLE mylua_test\G
*************************** 1. row ***************************
       Table: mylua_test
Create Table: CREATE TABLE `mylua_test` (
  `uid` int(11) NOT NULL,
  `sid` int(11) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `uid` (`uid`,`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SELECT * FROM mylua_test ORDER BY uid, sid LIMIT 10;
+-----+-------+
| uid | sid   |
+-----+-------+
|   1 |     1 |
|   1 |    10 |
|   1 |   100 |
|   1 |  1000 |
|   1 | 10000 |
|   2 |     2 |
|   2 |    20 |
|   2 |   200 |
|   2 |  2000 |
|   2 | 20000 |
+-----+-------+
10 rows in set (0.00 sec)

mysql> SELECT mylua('
  local t = {}
  mylua.init_table("localuser", "mylua_test", "uid", "uid", "sid")
  mylua.index_read_map(mylua.HA_READ_KEY_OR_NEXT, 1, 900)
  table.insert(t, { mylua.val_int("uid"), mylua.val_int("sid") })
  mylua.index_next()
  table.insert(t, { mylua.val_int("uid"), mylua.val_int("sid") })
  mylua.index_next()
  table.insert(t, { mylua.val_int("uid"), mylua.val_int("sid") })
  return t
', '{}') AS json;
+------------------------------------------------------+
| json                                                 |
+------------------------------------------------------+
| {"ok":true,"data":[[1,1000],[1,10000],[2,2]]}        |
+------------------------------------------------------+
1 row in set (0.00 sec)

set lua memory limit to limit. default is 1MB.

get current lua memory limit.

os.clock() result at the time of starting query execution.

mylua.startclock + timeout seconds.

default timeout seconds is 60.

mylua.timeout is compared to os.clock() in lua count hook.

if os.clock() greater than mylua.timeout, error is occured.

if using luajit, it does not work correctly.

New BSD License.

Copyright (c) 2011, Atsumu Tanaka <[email protected]>
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

   * Redistributions of source code must retain the above copyright notice,
     this list of conditions and the following disclaimer.

   * Redistributions in binary form must reproduce the above copyright notice,
     this list of conditions and the following disclaimer in the documentation
     and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.