resty.mysql fails to db:set_keepalive() after concatenated SQL-query sent

vadim · 2018-02-01T03:45:31+00:00

I tried to do the following./configure --prefix=/home/tim/openresty --without-http_gzip_module --without-http_ssl_moduleI got the error./configure: error: SS...

resty.mysql fails to db:set_keepalive() after concatenated SQL-query sent

vadim

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. Here is the code (your slightly modified sanity test-15):

location /t {
content_by_lua '
            local ljson = require "ljson"

            local mysql = require "resty.mysql"
            local db = mysql:new()

            db:set_timeout(1000) -- 1 sec

            local ok, err, errno, sqlstate = db:connect({
                path = "$TEST_NGINX_MYSQL_PATH",
                database = "ngx_test",
                user = "ngx_test",
                password = "ngx_test"})

            if not ok then
                ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
                return
            end

            ngx.say("connected to mysql: ", db:get_reused_times())

            local ok, err = db:set_keepalive()
            if not ok then
                ngx.say("failed to set keepalive: ", err)
                return
            end

            ok, err, errno, sqlstate = db:connect({
                path = "$TEST_NGINX_MYSQL_PATH",
                database = "ngx_test",
                user = "ngx_test",
                password = "ngx_test"})

            ngx.say("connected to mysql: ", db:get_reused_times())

            res, err, errno, sqlstate =
                db:query("select * from cats order by id asc; select NOW(); ")
            if not res then
                ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
                return
            end

            ngx.say("result: ", ljson.encode(res), ", err:", err)

            local ok, err = db:set_keepalive()
            if not ok then
                ngx.say("failed to set keepalive: ", err)
                return
            end
    ';
    }

rpaprocki

Hi,

Please pay attention to the following when dealing with multi result sets:https://github.com/openresty/lua-resty-mysql/blob/master/README.markdown#multi-resultset-support

On Jan 31, 2018, at 11:45, Vadim Korchagin <va...@altana.ru> wrote:

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. Here is the code (your slightly modified sanity test-15):

location /t { content_by_lua ' local ljson = require "ljson" local mysql = require "resty.mysql" local db = mysql:new() db:set_timeout(1000) -- 1 sec local ok, err, errno, sqlstate = db:connect({ path = "$TEST_NGINX_MYSQL_PATH", database = "ngx_test", user = "ngx_test", password = "ngx_test"}) if not ok then ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate) return end ngx.say("connected to mysql: ", db:get_reused_times()) local ok, err = db:set_keepalive() if not ok then ngx.say("failed to set keepalive: ", err) return end ok, err, errno, sqlstate = db:connect({ path = "$TEST_NGINX_MYSQL_PATH", database = "ngx_test", user = "ngx_test", password = "ngx_test"}) ngx.say("connected to mysql: ", db:get_reused_times()) res, err, errno, sqlstate = db:query("select * from cats order by id asc; select NOW(); ") if not res then ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".") return end ngx.say("result: ", ljson.encode(res), ", err:", err) local ok, err = db:set_keepalive() if not ok then ngx.say("failed to set keepalive: ", err) return end '; }

.

vadim

Thanks, but I'm not dealing with multiple results set, I'm dealing with multiple inserts by one request and need to place current connection to the pool by set_keepalive function, which is failing in that case of use.

среда, 31 января 2018 г., 23:38:40 UTC+3 пользователь rpaprocki написал:

Hi,

Please pay attention to the following when dealing with multi result sets:https://github.com/openresty/lua-resty-mysql/blob/master/README.markdown#multi-resultset-support

On Jan 31, 2018, at 11:45, Vadim Korchagin <va...@altana.ru> wrote:

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. Here is the code (your slightly modified sanity test-15):

location /t { content_by_lua ' local ljson = require "ljson" local mysql = require "resty.mysql" local db = mysql:new() db:set_timeout(1000) -- 1 sec local ok, err, errno, sqlstate = db:connect({ path = "$TEST_NGINX_MYSQL_PATH", database = "ngx_test", user = "ngx_test", password = "ngx_test"}) if not ok then ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate) return end ngx.say("connected to mysql: ", db:get_reused_times()) local ok, err = db:set_keepalive() if not ok then ngx.say("failed to set keepalive: ", err) return end ok, err, errno, sqlstate = db:connect({ path = "$TEST_NGINX_MYSQL_PATH", database = "ngx_test", user = "ngx_test", password = "ngx_test"}) ngx.say("connected to mysql: ", db:get_reused_times()) res, err, errno, sqlstate = db:query("select * from cats order by id asc; select NOW(); ") if not res then ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".") return end ngx.say("result: ", ljson.encode(res), ", err:", err) local ok, err = db:set_keepalive() if not ok then ngx.say("failed to set keepalive: ", err) return end '; }

.

rpaprocki

Hi,

The same logic still applies. Consider the following snippet:

poprocks@ubuntu:~$ cat t.lua

local cjson = require "cjson"

local mysql = require "resty.mysql"

local db = mysql:new()

local ok, err, errcode, sqlstate = db:connect({

host = "127.0.0.1",

port = 3306,

database = "foo",

user = "test_user",

password = "password"})

if not ok then

ngx.log(ngx.ERR, "failed to connect: ", err, ": ", errcode, " ", sqlstate)

return ngx.exit(500)

end

res, err, errcode, sqlstate = db:query("insert into bar values (1); insert into bar values (2); insert into bar values(3); select * from bar;")

if not res then

ngx.log(ngx.ERR, "bad result #1: ", err, ": ", errcode, ": ", sqlstate, ".")

return ngx.exit(500)

end

ngx.say("result #1: ", cjson.encode(res))

local i = 2

while err == "again" do

res, err, errcode, sqlstate = db:read_result()

if not res then

ngx.log(ngx.ERR, "bad result #", i, ": ", err, ": ", errcode, ": ", sqlstate, ".")

return ngx.exit(500)

end

ngx.say("result #", i, ": ", cjson.encode(res))

i = i + 1

end

local ok, err = db:set_keepalive(10000, 50)

if not ok then

ngx.log(ngx.ERR, "failed to set keepalive: ", err)

ngx.exit(500)

end

poprocks@ubuntu:~$ ./openresty/bin/resty ./t.lua

result #1: {"insert_id":0,"affected_rows":1,"server_status":10,"warning_count":0}

result #2: {"insert_id":0,"affected_rows":1,"server_status":10,"warning_count":0}

result #3: {"insert_id":0,"affected_rows":1,"server_status":10,"warning_count":0}

result #4: [{"t":1},{"t":1},{"t":1},{"t":2},{"t":3},{"t":1},{"t":2},{"t":3}]

On Wed, Jan 31, 2018 at 1:54 PM, Vadim Korchagin <va...@altana.ru> wrote:

Thanks, but I'm not dealing with multiple results set, I'm dealing with multiple inserts by one request and need to place current connection to the pool by set_keepalive function, which is failing in that case of use.

среда, 31 января 2018 г., 23:38:40 UTC+3 пользователь rpaprocki написал:
Hi,

Please pay attention to the following when dealing with multi result sets:https://github.com/openresty/lua-resty-mysql/blob/master/README.markdown#multi-resultset-support

On Jan 31, 2018, at 11:45, Vadim Korchagin <va...@altana.ru> wrote:

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. Here is the code (your slightly modified sanity test-15):

location /t { content_by_lua ' local ljson = require "ljson" local mysql = require "resty.mysql" local db = mysql:new() db:set_timeout(1000) -- 1 sec local ok, err, errno, sqlstate = db:connect({ path = "$TEST_NGINX_MYSQL_PATH", database = "ngx_test", user = "ngx_test", password = "ngx_test"}) if not ok then ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate) return end ngx.say("connected to mysql: ", db:get_reused_times()) local ok, err = db:set_keepalive() if not ok then ngx.say("failed to set keepalive: ", err) return end ok, err, errno, sqlstate = db:connect({ path = "$TEST_NGINX_MYSQL_PATH", database = "ngx_test", user = "ngx_test", password = "ngx_test"}) ngx.say("connected to mysql: ", db:get_reused_times()) res, err, errno, sqlstate = db:query("select * from cats order by id asc; select NOW(); ") if not res then ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".") return end ngx.say("result: ", ljson.encode(res), ", err:", err) local ok, err = db:set_keepalive() if not ok then ngx.say("failed to set keepalive: ", err) return end '; }

.