lua-resty-mysql 数据库使用遇到的两个问题？

[tao244...@gmail.com]

本地环境是：linux  centos6.5  

软件包版本是：nginx version: openresty/1.9.7.5

问题一： mysql数据库频繁出现连接失败的情况，提示failed to receive packet header: 超时，并且出现lua tcp socket read timed out 问题，导致数据操作失败？

2016/07/08 10:07:08 [error] 4994#0: *52525 [lua] oldmysql.lua:25: connect(): failed to connect: failed to receive packet header: timeout: nil nil, client: 127.0.0.1, server: localhost, request: "POST /system/get_configs HTTP/1.1"

2016/07/08 10:07:08 [error] 4994#0: *52525 [lua] system.lua:118: format_prov_distance(): exec sql:select * from t_prov_distance failed, client: 127.0.0.1, server: localhost, request: "POST /system/get_configs HTTP/1.1"

2016/07/08 10:07:09 [error] 4994#0: *52525 lua tcp socket read timed out, client: 127.0.0.1, server: localhost, request: "POST /system/get_configs HTTP/1.1"

2016/07/08 10:07:09 [error] 4994#0: *52525 [lua] oldmysql.lua:25: connect(): failed to connect: failed to receive packet header: timeout: nil nil, client: 127.0.0.1, server: localhost, request: "POST /system/get_configs HTTP/1.1", 

2016/07/08 10:07:10 [error] 4994#0: *52525 lua tcp socket read timed out, client: 127.0.0.1, server: localhost, request: "POST /system/get_configs HTTP/1.1"

2016/07/08 10:07:10 [error] 4994#0: *52525 [lua] oldmysql.lua:25: connect(): failed to connect: failed to receive packet header: timeout: nil nil, client: 127.0.0.1, server: localhost, request: "POST /system/get_configs HTTP/1.1"

2016/07/08 10:07:10 [error] 4994#0: *52525 [lua] system.lua:143: format_schedule_mapped(): exec sql:select * from t_schedule_mapped failed, client: 127.0.0.1, server: localhost, request: "POST /system/get_configs HTTP/1.1"

问题二： mysql数据库设置keepalive失败，频繁报错：

2016/07/02 23:54:27 [error] 45900#0: *4448031 [lua] oldmysql.lua:40: close(): set keepalive failed,err:cannot be reused in the current connection state: 2, client: 122.228.21.7, server: localhost, request: "POST /system/get_configs HTTP/1.1",

2016/07/02 23:54:27 [error] 45900#0: *4448030 [lua] oldmysql.lua:40: close(): set keepalive failed,err:cannot be reused in the current connection state: 2, client: 122.228.21.7, server: localhost, request: "POST /system/get_configs HTTP/1.1",

2016/07/02 23:54:27 [error] 45900#0: *4448031 [lua] oldmysql.lua:40: close(): set keepalive failed,err:cannot be reused in the current connection state: nil, client: 122.228.21.7, server: localhost, request: "POST /system/get_configs HTTP/1.1"

2016/07/02 23:54:27 [error] 45900#0: *4448031 [lua] oldmysql.lua:40: close(): set keepalive failed,err:cannot be reused in the current connection state: 2, client: 122.228.21.7, server: localhost, request: "POST /system/get_configs HTTP/1.1",

2016/07/02 23:54:27 [error] 45900#0: *4448032 [lua] oldmysql.lua:40: close(): set keepalive failed,err:cannot be reused in the current connection state: nil, client: 122.228.21.7, server: localhost, request: "POST /system/get_configs HTTP/1.1",

数据库操作语句;

===============================================

function connect(dbname)

    local conn, err = mysql:new()

    if not conn then

        ngx.log(ngx.ERR, "fail to instantiate mysql,err:", err)

        return nil, err

    end

    conn:set_timeout(1000) -- 1 second

    local ok, err, errno, sqlstate = conn:connect({

        host = mysql_host, 

        port = mysql_port, 

        database = dbname, 

        user = mysql_user, 

        password = mysql_passwd, 

        max_packet_size = mysql_max_packet_size 

    })

    if not ok then 

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

        return nil, err

    end

    ngx.log(ngx.DEBUG, "connect to mysql success ...")

    return conn, nil

end

==================================================

function close(conn)

    if not conn then 

        return

    end

    local ok, err = conn:set_keepalive(10000, 20)

    if not ok then 

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

    end

end

是我的lua操作数据库语句写的不对么？求大神们指点；

[tao244...@gmail.com]

有人遇到过类似的问题么？

[tao244...@gmail.com]

第一个问题已经找到答案，是本地linux 内核连接跟踪表满了，出现丢包了，导致数据库连接失败；

nf_conntrack: table full, dropping packet.

数据库连接语句本身没啥问题。

在 2016年7月8日星期五 UTC+8上午11:41:53，tao244...@gmail.com写道：

[Yichun Zhang (agentzh)]

Hello!

On Thu, Jul 7, 2016 at 8:41 PM, tao244023483 wrote:
> 软件包版本是：nginx version: openresty/1.9.7.5
>

建议升级到最新的 OpenResty 1.9.15.1.

> 问题二： mysql数据库设置keepalive失败，频繁报错：
> 2016/07/02 23:54:27 [error] 45900#0: *4448031 [lua] oldmysql.lua:40:
> close(): set keepalive failed,err:cannot be reused in the current connection
> state: 2, client: 122.228.21.7, server: localhost, request: "POST
> /system/get_configs HTTP/1.1",

这是你的用法错误。常见的情况是你没有读取或者没有读取完当前 SQL 查询返回的所有结果集数据，就尝试把 mysql
连接放回到连接池复用（这样的连接显然是不能被其他请求安全复用的，因为读取缓存区里还有未读完的数据）。

Regards,
-agentzh

[tao244...@gmail.com]

确实是自己的用法问题，没有读取完当前的SQL返回结果。非常感谢agentzh！