OpenResty / Lua - Maintain mysql database connection in worker threads

Question

I have a simple module called "firewall.lua" that I wrote that has a function firewall.check_ip(ip) which connects to localhost mysql and performs a query and returns the result. The function gets called from within Location / blocks in nginx sites via access_by_lua_block . The module gets initialized by init_worker_by_lua firewall.init().

Everything works as expected.

What I'd like to do however is maintain the database connection on the worker thread(s) so that I don't have to re-connect every time the function is called but instead re-use the existing connection established by the worker during initialization.

I'm not quite sure how to do this or if its actually doable in openresty/lua. I tried initializing the database connection variables outside of the function to give them scope within the module instead of function and I get various API errors that did not point me in the right direction.

Thank you!

Answer 1

This is possible using the OpenResty cosocket API, which gives you the ability to use a pool of non-blocking connections. There's already one MySQL driver (lua-resty-mysql) which uses the cosocket API. Since you didn't provide a code sample, I'm assuming you're not using it.

Example of a connection and query using lua-resty-mysql (untested):

access_by_lua_block {
    local mysql = require "resty.mysql";
    local db, err = mysql:new()

    db:set_timeout(1000) -- 1 second

    local ok, err, errcode, sqlstate = db:connect{
        host = "127.0.0.1",
        port = 3306,
        database = "my_db",
        user = "my_user",
        password = "my_pwd",
    }

    if not ok then
        ngx.say("Connection to MySQL failed: ", err)
        return
    end

    result, err, errcode, sqlstate = db:query("select ...")
    if not result then
        ngx.say("MySQL error: ", err, ".")
            return
    end

    db:close()
}

In case, e.g., you want to control the pool name or use other options, you can pass on additional parameters to connect:

...
    local ok, err, errcode, sqlstate = db:connect{
        host = "127.0.0.1",
        port = 3306,
        database = "my_db",
        user = "my_user",
        password = "my_pwd",
        pool = "my_connection_pool",
    }
...

You can find more information in the official docs: lua-resty-mysql: https://github.com/openresty/lua-resty-mysql

Cosocket API: https://openresty-reference.readthedocs.io/en/latest/Lua_Nginx_API/#ngxsockettcp