The function connect will create a new mysql connection or select a free connection from pool! If there are too many connection using, I don't want it to return immediately until another connection is released! How to do it?

why not call sock::close when read timeout like redis.lua did?

In redis.lua，the connection pool was destroyed when read timeout happens

SHA-1: c75af8fe7f71ca05e4bd3ccb3923f6a81d62a67a
bugfix: the memcached connection might enter a bad state when read timeout happens because ngx_lua's cosocket reading calls no longer automatically close the connection in this case.

so my question is why did not treat this as a bug in mysql.lua

thanks

there is a case that when i use this lib to connect mysql, a warning which says [unkown charset num:0, charset will be set as utf8] will occur on database server. so i think that connect function needs a parameter for charset encoding. i saw agenth's comment which said that u don't like to set names on client and there is default charset on server though.....just an advice~

765 local function read_result(self, est_nrows)
766 if self.state ~= STATE_COMMAND_SENT then
767 return nil, "cannot read result in the current context: " .. self.state
768 end

bug???
resty/mysql.lua:794: attempt to concatenate field 'state' (a nil value)

如何修改编码呢

local mysql = require "resty.mysql"
local db,err = mysql:new()
if not db then
ngx.say("failed to instantiate mysql: ",err)
return
end

db:set_timeout(1000)

local ok,err,errno,sqlstate = db:connect{
host = "127.0.0.1",
port = 3306,
database = "db_blog",
user = "root",
password = "admin",
max_package_size = 1024,
charset = "utf-8"
}
if not ok then
ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
return
end

res,err,errno,sqlstate = db:query("select * from wp_terms")
if not res then
ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
return
end

local cjson = require "cjson"
ngx.say(cjson.encode(res))

结果
[{"term_group":"0","name":"????","term_id":"1","slug":"help"},{"term_group":"0","name":"??","term_id":"2","slug":"novel"},{"term_group":"0","name":"??","term_id":"3","slug":"service"},{"term_group":"0","name":"main_menu","term_id":"4","slug":"main_menu"},{"term_group":"0","name":"??","term_id":"5","slug":"about"},{"term_group":"0","name":"????","term_id":"6","slug":"info"},{"term_group":"0","name":"????","term_id":"7","slug":"notice"},{"term_group":"0","name":"????","term_id":"8","slug":"job"},{"term_group":"0","name":"????","term_id":"9","slug":"cooperate"},{"term_group":"0","name":"????","term_id":"10","slug":"%e5%bc%82%e5%b8%b8%e5%a4%84%e7%90%86"}]

Do we have PDO style query in lua-resty-mysql
The query and params can be sent separately rather than a string and it takes care of sql injection etc.

local ok, err, errno, sqlstate = db:connect{
host = "my.mysql.domain.com",
port = 3306,
database = "test",
user = "test",
password = "test",
max_packet_size = 1024 * 1024 }
in development env.,host is "127.0.0.1", it is ok.
in product env. host is similar domain name,return is failed to connect ...

First, thanks your great job!

My question: It's not safe, how to escape data ?

local sql = "INSERT INTO `mytable` (`title`) VALUES (`" .. title .. "`)"

Aliyun RDS server require mysql_host using domain name. When I set
"mysql_host = xxxxxxxxxx.mysql.rds.aliyuncs.com", I found can not connect to mysql.

Error info: failed to connect: no resolver defined to resolve "xxxxxxxxxxxx.mysql.rds.aliyuncs.com".

How can I do about it.

hi, @agentzh

It's just a small & quick question.
Why you commented line below? ;-) Protocol::MYSQL_TYPE_LONGLONG should also be number?

-- converters[0x08] = tonumber  -- long long

*454 lua entry thread aborted: runtime error: /usr/local/openresty/lualib/resty/mysql.lua:300: attempt to perform arithmetic on local 'len' (a boolean value) stack traceback: coroutine 0: /usr/local/openresty/lualib/resty/mysql.lua: in function '_from_length_coded_str' /usr/local/openresty/lualib/resty/mysql.lua:423: in function '_parse_row_data_packet' /usr/local/openresty/lualib/resty/mysql.lua:870: in function 'query'

This error appeared after we migrate to MYSQL 5.7.14, in previous versions everything was ok.

currently we use:
openresty/1.11.2.1
lua-resty-mysql-0.16

Is anyone else seeing aborted mysql connections when using this? We are using percona mysql, and I am calling the db:close() as shown below.
local ok, err = db:close()
if not ok then
ngx.log(ngx.DEBUG, 'failed to close: ', err)
end

实际在ngx_lua_model中使用时
没注意到有escape过滤用户输入函数
agrs_id = ngx.req.get_post_args()[''id] --

sql = "select * from tb1 where id=" .. agrs_id';'
db:query(sql)

此处应该有id参数是否sql注入可能？

In PHP you have 2 part, execute query and then fetch the result one by one, this is save resources and very fast.

I am comparing fetching 100000records and php is very slow if i buffer the result first then have loop to process the data. But if we use PHP way, execute query and then fetch one by one, its 10x faster.

I hope there is solution (i think its easy).

PHP CODE

$xmtime = microtime(TRUE);
$res = queryi("select * from transaksi limit 100000");
echo '<br>';
echo microtime(TRUE)-$xmtime;
echo 'Seconds';
echo "<hr>";
$xmtime = microtime(TRUE);
$ftotalprice=4;
$fjumlah=3;
$fidmtr=1;
while ($r=$res->fetch_row()) {
  echo $r[$ftotalprice],$r[$fjumlah],$r[$fidmtr];
}
echo '<br>';
echo microtime(TRUE)-$xmtime;
echo 'Seconds';
echo "<hr>";

MOONSCRIPT

x=os.clock()
res = queryi("select * from transaksi limit 100000",10000)
raw '<br>'
raw os.clock()-x
raw 'Seconds'
raw "<hr>"
x=os.clock()
ftotalprice=5
fjumlah=4
fidmtr=2
for r in *res
  raw r[ftotalprice],r[fjumlah],r[fidmtr]

raw '<br>'
raw os.clock()-x
raw 'Seconds'
raw "<hr>"

ubuntu server 14.04 (512M, Aliyun ECS),lua-resty-mysql (0.17) and openresty (1.11.2.2). When I attempt to insert about 100K tiems, the database failed about 7k items, some are due to too many connections, the other are out of memory.

I set the pool size to 50 in lua layer. and use the default settting in mysql server layer. I also wrap the query logic to a module and use it via require:

local driver = require "mysql"

local CONNECT_TABLE = { 
        host     = "127.0.0.1", 
        port     = 3306, 
        database = "test", 
        user     = 'root', 
        password = '', }
local CONNECT_TIMEOUT = 1000
local IDLE_TIMEOUT = 10000
local POOL_SIZE = 50

local function query(statement, rows)
    -- utils.loger(statement)
    local db, err = driver:new()
    if not db then
        return nil, err
    end
    db:set_timeout(CONNECT_TIMEOUT) 
    local res, err, errno, sqlstate = db:connect(CONNECT_TABLE)
    if not res then
        return nil, err, errno, sqlstate
    end
    res, err, errno, sqlstate =  db:query(statement, rows)
    if res ~= nil then
        local ok, err = db:set_keepalive(IDLE_TIMEOUT, POOL_SIZE)
        if not ok then
            return nil, err
        end
    end
    return res, err, errno, sqlstate
end

return query

In _parse_row_data_packet(), it does not convert value of column contains null(ngx.null). It causes compatibility issue with generic lua modules like JSON. Could you explain why it returns ngx.null instead of nil? I can fix it on my needs, but I hope that it will be fixed officially if there's no any other issues related on that change.

It would be great if this library could support cursors. Is there a plan to add this or is there a reason not to do it?

luasql has this support but of course it's a blocking library so it's not as useful with openresty.

Thanks for lua-resty-mysql -- it's a great libary!

Related issue: #24

error:
"Incorrect datetime value: 'Tue Jan 12 18:31:10 2016' for column 'createTime' at row 1"

table:
CREATE TABLE jctx_group_members (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
groupId int(20) unsigned NOT NULL,
uid int(11) NOT NULL,
createTime datetime NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

code:
local current_time = os.date()

local sql_create = "insert into jctx_group_info"
    .."(creatorUid, groupName, createTime) values ("
    ..groupCreator..",\'"..groupName.."\',\'"..current_time.."\')"

local res, err, errno, sqlstate = self.database:query(sql_create)

能给一个把mysql封装成一个模块的例子吗，传一个sql语句，返回结果的。自己测试了很久达不到那个吞吐量，又不知道问题出在哪里

del

I add below code after connect, but it always return zero on every request.
local times, err = db:get_reused_times()
if not times then
ngx.say("failed to get get_reused_times: ", err)
return
end
ngx.say("connection reused ", times," times.")

I want to use transaction in mysql sql.

such as:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;

Such like select filename,md5,dfspath from dfs_mapping where uri like '%player2116702552%',i found it not working.

I suggest to add tonumber converter NEWDECIMAL field type (0xf6). Otherwise results of aggregate functions (such as SUM) are treated as strings.

see also

where can I get that cert/key ?

https://github.com/openresty/lua-resty-mysql/blob/master/t/ssl.t#L96

should I generate it on the fly ?

When I query data from DB, I found I got string for DECIMAL from online DB but number from test DB. I checked the code, I think it maybe do not handle 0x00(FIELD_TYPE_DECIMAL). Could you tell me why? Thanks!

bytes, err = db:send_query(query)
res, err, errcode, sqlstate = db:read_result()
res, err, errcode, sqlstate = db:read_result(nrows)

res, err, errcode, sqlstate = db:query(query)
res, err, errcode, sqlstate = db:query(query, nrows)

db:query() may return 2 values, if we access the 3rd return value, we may get nil, but we expect errcode.

Hello,

I get a strange error from time to time:

lua entry thread aborted: runtime error: /resty/mysql.lua:63: attempt to call local 'new_tab' (a table value)
stack traceback:
coroutine 0:
	[C]: in function 'require'

And after that this error:

2017/03/29 22:05:16 [error] 65691#0: *7148887 lua entry thread aborted: runtime error: search16.lua:1877: loop or previous error loading module 'resty.mysql'
stack traceback:
coroutine 0:
	[C]: in function 'require'

Of cource I load module only one time. I tried to load module at the beginning of the script - there is no difference. This error happens.

I use mysql.lua to work with sphinx db. Could you please help me to understand what I'm doing wrong?

Do not support transaction
Right?

I'm getting "cannot be reused in the current connection state: 2" error when trying to set_keepalive() mysql connection after concatenated SQL-query (multiple sql queries in one request) sent.

https://travis-ci.org/chipitsine/lua-resty-mysql/jobs/134985756#L1214

test complains on missing world.City, where can I take sql script for that table ?

Is it possible to get the last inserted row id without making an extra query?

In perl DBI you can do i.e. $db->{mysql_insertid}

First of all, cosocket is cool :) Thanks for the excellent work @agentzh and other contributors.
I could never imagine writing an async mysql / postgres driver in lua within 1k lines of codes.

Currently when we fetch a result set, we call db:query, which returns all the rows:

local row = _parse_row_data_packet(packet, cols, compact)
i = i + 1
rows[i] = row
-- ......
return rows

In many (or at lease some) cases however we don't need to keep all records in memory.
It would be nice if we could do something like this (take json output as an example):

function row_event_hook(row)
  -- 'row' is returned by '_parse_row_data_packet' function, a lua table
  -- you can do something like ngx.say(cjson.encode(row)) or do whatever you want
  ngx.say(cjson.encode(row))
  -- the above is just an arbitrary example without details like commas etc.
end

ngx.say('[')
-- if row_event_hook is provided, records are not saved in the memory by default
-- if row_event_hook is not provided, return the array of records as usual
db:query('SELECT foo FROM bar', row_event_hook)
ngx.say(']')

I do understand it's probably not worth it in the above example because it was mentioned in another issue that ngx.say doesn't buffer (it would be nice to have ngx.write_to_buffer function :) ). But there are other scenarios where a row_event_hook does make sense to prevent the request cycle from unnecessary memory allocations. Like writing BLOB data (blob_data_event_hook) through some IO stream (or HTTP response) etc.

So to wrap up, it would be nice to have some event hook support since we can potentially save some memory per request cycle, which is very important for a high concurrency web api / app.

Any suggestions / comments welcome.

When i tried the query method as the readme said:
res,err,errcode,sqlstate=db:query("select * from account where id="..id..";")The correct query was OK.
And to test the condition of bad result("empty set"),i use an id that does not exits in the MySQL table.However,unexpectedly,the if statement following the query:
if not res thennever excuted,which means res is not nil......
And i tried if not res[1] then,it works as i expected(Though err errcode sqlstate were all nil )
I want to know what's wrong with it :)

I get "lua tcp socket read timed out" errors every so often and wanted to see if maybe if my syntax is not used correctly for lua mysql before i assume a bug exists? it's designed to use mysql to return docroot for each hosting customer based on what subdomain they use.

detailed error:

2015/02/21 20:49:19 [error] 22343#0: *281222 lua tcp socket read timed out, client: x.x.x.x, server: *.mydomain.com, request: "GET /administration/ping?=1424573359691 HTTP/1.1", host: "jshah4722b.mydomain.com", referrer: "https://jshah4722b.mydomain.com/patients/index/task:edit/patient_id:3630"
2015/02/21 20:49:19 [error] 22343#0: *281222 [lua] rewrite_by_lua:78: MySQL bad result: failed to receive packet header: timeout: nil: nil., client: x.x.x.x, server: *.mydomain.com, request: "GET /administration/ping?=1424573359691 HTTP/1.1", host: "jshah4722b.mydomain.com", referrer: "https://jshah4722b.mydomain.com/patients/index/task:edit/patient_id:3630"

here's what i have:

it's called using this method:

server {

    server_name  *.mydomain.com;

    set $httphost $http_host; # create and initialize var
    set $docroot "";
    include /etc/nginx/lua.conf;
    # docroot is defined from above LUA script
    root           $docroot/app/webroot/;

/etc/nginx/lua.conf contents:

begin LUA scripting

    lua_socket_log_errors on;
    lua_check_client_abort on;
    lua_socket_read_timeout 120s;

    rewrite_by_lua '

                    -- make sure http host is defined
                    if not ngx.var.httphost then
                      ngx.log(ngx.ERR,"ERROR - no httphost defined")
                      return
                    end

                    local host=ngx.var.httphost

                    -- make sure more than 1 dot in the HOST name
                    local _, pcount = string.gsub(host, "%.", "")
                    if pcount < 2 then
                      ngx.log(ngx.ERR,"ERROR HOST has 1 dot! -> " .. host)
                    end

                    local client,tld=host:match"([^.]*).(.*)"

                    if not client then
                      ngx.log(ngx.ERR,"ERROR - no client defined")
                      return
                    end

                    local mysql = require "resty.mysql"
                    local db, err = mysql:new()
                    if not db then
                      ngx.log(ngx.ERR,"MySQL failed to initiate: ", err)
                      return
                    end

                    db:set_timeout(10000) -- 10 sec
                    local ok, err2, errno, sqlstate = db:connect
                    {
                            host = "10.80.18.211",
                            port = 3306,
                            database = "onetouch_admin",
                            user = "docroot",
                            password = "0netouch3MR",
                            max_packet_size = 1024 * 1024 * 7
                    }
                    if not ok then
                      ngx.log(ngx.ERR,"MySQL failed to connect: ", err2, ": ", errno, " ", sqlstate)
                      return
                    end

                    local hname = ngx.unescape_uri(client)
                    local quoted_name = ngx.quote_sql_str(hname)

                    local sql = "SELECT `version` FROM `users` WHERE `customer` =" .. quoted_name
                    local result,err,errno,sqlstate = db:query(sql,1)
                    if not result then
                       ngx.log(ngx.ERR,"MySQL bad result: ", err, ": ", errno, ": ", sqlstate, ".")
                       return
                    end

                    if result[1] == nil then
                      -- ngx.log(ngx.ERR,"MySQL  - no docroot was returned for host:",host)
                      ngx.var.docroot = "/var/www/production"
                      return
                    end

                    local ok3, err3 = db:set_keepalive(15000, 400)
                    if not ok3 then
                            ngx.log(ngx.ERR, "failed to set keepalive: ", err3)
                    end

                    ngx.var.docroot = result[1].version
            ';

I want to insert 3 SQL statements are inserted into the 3 table, but the performance is too low, I was so written, how do I do to make the performance higher？
the code like this:
example1:

     local db,err = mysql:new()
     local ok,err,errno,sqlstate = db:connect(dbconf)   
 if not ok then
    ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
    return
 end

res, err, errno, sqlstate = db:query("BEGIN;")
if not res then
        ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
        return
end

   res, err, errno, sqlstate = db:query("insert1;")
   if not res then
        ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
        return
    end 
  res, err, errno, sqlstate = db:query("insert2;")
   if not res then
       ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
       return
  end
 res, err, errno, sqlstate = db:query("insert3;")
 if not res then
       ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
       return
 end

 res, err, errno, sqlstate = db:query("COMMIT;")
 if not res then
        ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
        return
 end

 local ok, err = db:set_keepalive(10000, 1000)
 if not ok then
  ngx.log(ngx.ERR,"failed to set keepalive: ",err)
      --ngx.exit(500)
 end

the performance of examnple1 like that:

example2:

local db,err = mysql:new()
local ok,err,errno,sqlstate = db:connect(dbconf)    
if not ok then
 ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
 return
 end


 res, err, errno, sqlstate = db:query("START TRANSACTION;insert sql1;insert sql2;insert   sql3;COMMIT;")
if not res then
       ngx.log(ngx.ERR, "bad result #",   ": ", err, ": ", errno, ": ", sqlstate, ".")
       return
end

local ok, err = db:set_keepalive(10000, 1000)
if not ok then
  ngx.log(ngx.ERR,"failed to set keepalive: ",err)
      --ngx.exit(500)
end

the performance of example2 like that:
./wrk -t10 -c 100 -d1m "url"

How can i apply the transaction to improve the performance? The way of appling the transcation is right?But lack the ROLLBACK ,what's the right way?

我是用 lua-resty-mysql 从mysql取数据， 每次改sql 都需要重新reload，比较麻烦, 請問有什么配置可以不需要reload吗。

Like an example,is each http request creates a new connection, or to check is have a pool. If have a pool,use it .if not create a pool

local ok, err, errno, sqlstate = db:connect{
                    host = "127.0.0.1",
                    port = 3306,
                    database = "ngx_test",
                    user = "ngx_test",
                    password = "ngx_test",
                    max_packet_size = 1024 * 1024 }  

My server chatsert is utf8mb4, and also i query "set names 'utf8mb4'" after connect

Hello!

I have 2 identical virtual servers. When I have connected in lua script to local MySQL at first of these servers - all OK. But when I try to do this at second server my browser broke connection to local MySQL. If I try to connect from second server (via lua) to first MySQL I get this error:

"failed to connect: Host '' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts': 1129 nil"

I don't understand what difference inter my servers.....I have already copy my.cnf, test.lua script, etc from first server to second but it has no effect. One server works, another - not work.....Mystic....

I'd appreciate any help!

春哥,我用直接用中文了.大环境:win10 64 + openresty-1.9.7.4-win32.
我自己做了一个基于lua-resty-mysql查询的包装函数query, 主要是为了方便,不用去管连接的建立和释放.比如在content_by_lua_block中这样用:

local query=require"mylib.query"
local res1=query('SELET * FROM table1')
local res2=query('SELET name,age FROM table1 WHERE id=1')

这个函数在以下情况都表现正确:
1.只提交了一个sql 查询语句,无论合法还是出错的.
2.提交了多个合法的sql查询语句,
但是,如果提交多个查询,且其中一个查询出错(比如statement的语法有错),那么随后的最后一个合法查询就会异常.比如说我先后提交了两个查询:

query('UPDATE WHERE (id = 1)') --语法错误
query('SELET * FROM table1') --正常  
query('SELET * FROM table1') --这个合法语句将异常,返回nil,nil,nil,nil

这是我定义的query函数:

local mysql = require "resty.mysql"

local connect_table = {
    host = '127.0.0.1', port = 3306,
    user = 'root', password = '',
    database = 'test',
}

local function query(sql_statements)
    local db = ngx.ctx._db
    local res, err, errno, sqlstate;
    -- 如果第一次建立连接或上一个链接出错, 则新建
    if not db or db._query_err~=nil then
        db, err = mysql:new()
        if not db then
            return db, err
        end
        db:set_timeout(10000) 
        res, err, errno, sqlstate = db:connect(connect_table)
        if not res then
            return res, err
        end
        -- 将db绑定到ctx中, 方便共用
        ngx.ctx._db = db
    else
        --这里表示连接复用(上一次连接是正常的)
    end
    -- 开始使用连接进行查询
    res, err, errno, sqlstate =  db:query(sql_statements)
    -- 将err值绑定到db上, 以供下次提交查询语句时判断是否需要新建连接
    db._query_err = err
    return res, err, errno, sqlstate
end
return query

this part in the readme is a bit unclear to me, does this mean we can't do mysql = require "resty.mysql" at the module level or we can't do mysql::new() at the module level?

The resty.mysql object instance cannot be stored in a Lua variable at the Lua module level, because it will then be shared by all the concurrent requests handled by the same nginx worker process (see http://wiki.nginx.org/HttpLuaModule#Data_Sharing_within_an_Nginx_Worker ) and result in bad race conditions when concurrent requests are trying to use the same resty.mysql instance. You should always initiate resty.mysql objects in function local variables or in the ngx.ctx table. These places all have their own data copies for each request.

I found lua-nginx-websocket in Debian's repository that is maintained by Laurent Arnoud which refers to lua-resty-websocket. Just wondering if there is a way to install packages like lua-resty-mysql/redis/etc with aptitude or luarocks ?

MySQL ping method/query could let us know in advance before executing a query if the server is reachable and in good state. The feature request is to add this ping method on a db object which returns err to say whether it was success or failure.

local function _get_byte8(data, i)
local a, b, c, d, e, f, g, h = strbyte(data, i, i + 7)

-- XXX workaround for the lack of 64-bit support in bitop:
local lo = bor(a, lshift(b, 8), lshift(c, 16), lshift(d, 24))
local hi = bor(e, lshift(f, 8), lshift(g, 16), lshift(h, 24))
return lo + hi * 4294967296, i + 8

end

current resty.mysql code is above when read 8byte integer.
but LuaJIT use a boxed int64 for 64bit ineger support, so the "lo + hi * 4294967296" upper is 2^53, exceed will lost some bits

Hello, When I query an statement like update user set username='bbbbbb' where id=2;, will get result like (as json){"insert_id":0,"server_status":2,"warning_count":0,"affected_rows":1,"message":"(Rows matched: 1 Changed: 0 Warnings: 0"}. As you see, the first character of message is (, which is a little wired. When in mysql client shell, it's like

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

I send restful req to lua-resty-mysql. In the key-value parameter, value is urlencoded.
response is error.

[HEADER_OUT]POST /rest HTTP/1.1
Host: xxx.xxxxxx.com
Accept: /
Content-Length: 497
Content-Type: application/x-www-form-urlencoded
............
[HEADER_IN]HTTP/1.1 500 Internal Server Error
[HEADER_IN]Server: ngx_openresty
[HEADER_IN]Date: Wed, 20 Nov 2013 04:57:57 GMT
[HEADER_IN]Content-Type: text/html
[HEADER_IN]Content-Length: 186
[HEADER_IN]Connection: close
[DATA_IN]

Hello,
at first sorry for this question... I am newbie in Lua.

When I use cjson.encode(res), I'll get something like this:

[{
	"id":	1,
	"col1":	"value1",
	"col2":	"value2",
	"col3":	null
}]

Can you tell me how can I get value of specific column?
I tried res[0].col2, res[0][col2], res[0]["col2"], but it doesn't work.

Hi,
first, thanks for all this work on openresty!, it is very useful :). And sorry for the large comment :)

I'm considering using it for replacing some parts of our REST API, and could implement it in short time, with straightforward code, and that was able to outperform by a significant margin our current implementation that do have a lot of quirks on the code to gain speed, so: great :)

It would be even better, for our use case, if we could use prepared statements instead of building the query string each time. We tend to have a small number of different queries, that runs thousands time per second in response to client requests. In that scenario server-side prepared statements are great:

• The mysql sever doesn't need to parse the query each time
• We don't need to generate the query, nor to escape the parameters each time
• To retrieve the results the binary protocol is used, that is lighter to both us and mysql

I implemented support for prepare/execute/close prepared statements, that implementation itself was not difficult, mainly a new function to parse rows, and set it or the text-based one as callback depending if the response is for a textual query or for a prepared_statement execute() one.

1. Would you be interesting in adding this to lua-resty-mysql ? I can work on this and prepare a patch

2. How the API for this should looks like?. For testing I used:
   st = db:prepare("select . from .. where col1 = ? and col2 = ?")
   result = st:call("Value1", "Value2")
   st:close()

3. And this is the important one: I don't want to prepare/execute/close the statement each time. That is likely more resource intensive than just sending a plain text query. Instead, want to somehow keep with each mysql connection the set of prepared statements on it, so we could do things like:
   local st = db:prepared("st_1")
   if not st then
   st = db:prepare("st_1", "some query....")
   end

Mysql assign an id to each prepared statement, that you use for calling it. And that id is per-connection, so statement "st_1" could be 7 on one connection, and 3 on another one.

I failed to find an easy way to "attach" info to a mysql connection. I'm right that probably need to be actually attached to the cosocket object?. I couldn’t find any obvious way to do that neither, as new tables are created and returned each time, no matter the socket is comming back from the pool. I tried to cheat and use the c-data in the cosocket as key in a weak table, but that didn't work , although not really sure I was using it right.

Without 3) , adding prepared statement is not that useful (might be for security, as you can't forget to escape a parameter, or might be preferable too if the result set is big enough than the cost of prepare/execute/close is less than the speed gained by using binary rather than textual protocol for results)
Cheating, just for testing, and using only 1 prepared statement, and assuming it will be assigned id "1" on all sessions by mysql, I was able to test how well this behave, and by reusing the prepared statement for a simple query that returns 1 row, the throughput increased significantly (>50% ), while mysql server used less CPU.

So, what's your opinion?, would be interested?, if so, how you think the API should looks like?. It is point 3) possible with current cosocket/mysql design?