在ngx.thread.spawn中使用resty.mysql库对mysql进行并发查询，不时会获取到查询状态是SENT的sock，导致查询不成功以及无法setkeppalive

13 views

Skip to first unread message

waiban lueng

unread,

May 19, 2023, 6:23:00 AM5/19/23

to openresty

连接并获取db实例的方法如下：

local mysql = require "resty.mysql"

function _M.connect(self, config)
    local db = setmetatable({ conn = mysql:new(), config = config }, mt)

    local conn = db.conn

    conn:set_timeout(config.timeout)

    local ok, err, errno, sqlstate = conn:connect({
        host            = config.host,
        port            = config.port,
        database        = config.database,
        user            = config.user,
        password        = config.password,
        max_packet_size = config.max_packet_size
    })

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

    _M.query(db, "set names " .. config.charset)
    return db
end

spawn的大概方法如下:

function _M.get_res(box)

    local function _get_by_uniq(table_name, keys)
        local db = _M.connect({host="127.0.0.1",}) -- 相关配置
        local res, err = db.query("select * from " .. table_name .. ' where keys in (' .. table.concat(keys, ',') ..')')
        return res, err
    end


    local threads = {}
    for k, v in pairs(box) do
        threads[#threads + 1] = ngx.thread.spawn(_get_by_uniq, k, v)
    end

    for _, t in pairs(threads) do
        local ok, res = ngx.thread.wait(t)
        -- 处理res
    end
end

错误如下：

bad result: ,
failed to receive packet header: closed,
sql:,
set names utf8mb4

bad result: ,
failed to send query: cannot send query in the current context: 2

mysql库使用了可连接池，默认连接池空间是30.

看了resty.mysql的源码。current context: 2 表示此时sock的状态是SENT，不是CONNECTED。

我想知道：

1、我已经每次都重新调用socket的connet()方法获取一个新的连接，但是为什么还是会获取到一个SENT状态的mysq连接？

2、我可以通过什么方法确定具体的问题，以及有没有解决建议