[example][tutorial] openresty insert data into MySQL with MySQL connection pool.

gistfile1.txt

lua_package_path '/usr/local/openresty/lualib/?.lua;;';
lua_package_cpath '/usr/local/openresety/lualib/?.so;;';

server {
    listen       7020;

    access_log  logs/tredis.access.log  main;

    lua_need_request_body on;

    location /mysql {

        content_by_lua_block {
                local cjson = require "cjson"
                local cjson2 = cjson.new()
                local cjson_safe = require "cjson.safe"

                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)

                -- or connect to a unix domain socket file listened
                -- by a redis server:
                --     local ok, err = red:connect("unix:/path/to/redis.sock")

                local ok, err, errcode, sqlstate = db:connect{
                    host = "127.0.0.1",
                    port = 3306,
                    database = "redis_bench",
                    user = "redis_bench",
                    password = "******",
                    charset = "utf8",
                    pool = 1,
                    max_packet_size = 1024 * 1024,
                }

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

                method_name = ngx.req.get_method()
                if (method_name == 'POST') then
                    local data = ngx.req.get_body_data()
                    data = cjson.decode(data)
                    local sql = "INSERT INTO t_error_log (task_id, app_type, app_version, err_date, user_name, res_errorCode, server_ip, server_port, server_type, ext) VALUES ("
                                .. ngx.quote_sql_str(data.task_id) .. ',' .. ngx.quote_sql_str(data.app_type) .. ','
                                .. ngx.quote_sql_str(data.app_version) .. ',' .. ngx.quote_sql_str(data.err_date) .. ','
                                .. ngx.quote_sql_str(data.user_name) .. ',' .. ngx.quote_sql_str(data.res_errorCode)  .. ','
                                .. ngx.quote_sql_str(data.server_ip) .. ',' .. ngx.quote_sql_str(data.server_port) .. ','
                                .. ngx.quote_sql_str(data.server_type) .. ',' .. ngx.quote_sql_str(data.ext)
                                .. " )"
                    res, err, errno, sqlstate = db:query(sql)
                    if not res then
                        ngx.log(ngx.ERR, err)
                        ngx.say("POST failed to set ", err)
                        return
                    end
                end
                local ok, err = db:set_keepalive(10000, 100)
                if not ok then
                    ngx.say("failed to set keepalive: ", err)
                    return
                end

                return
            }
        }
    location /tokens {

        content_by_lua_block {
                local cjson = require "cjson"
                local cjson2 = cjson.new()
                local cjson_safe = require "cjson.safe"

                local redis = require "resty.redis"
                local red = redis:new()

                red:set_timeout(1000) -- 1 sec

                -- or connect to a unix domain socket file listened
                -- by a redis server:
                --     local ok, err = red:connect("unix:/path/to/redis.sock")

                local ok, err = red:connect("127.0.0.1", 6379)
                if not ok then
                    ngx.say("failed to connect: ", err)
                    return
                end

                method_name = ngx.req.get_method()
                if (method_name == 'POST') then
                    local data = ngx.req.get_body_data()
                    ok, err = red:rpush("token", data)
                    if not ok then
                        ngx.say("POST failed to set ", err)
                        return
                    end
                    ngx.say("set result: ", ok)
                end

                local ok, err = red:set_keepalive(1000, 100)
                if not ok then
                    ngx.say("failed to set keepalive: ", err)
                    return
                end
                return
            }
        }
    location / {
        root html;
    }
}