• Stars
    star
    703
  • Rank 63,829 (Top 2 %)
  • Language
    Lua
  • Created over 12 years ago
  • Updated 21 days ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Nonblocking Lua MySQL driver library for ngx_lua or OpenResty

Name

lua-resty-mysql - Lua MySQL client driver for ngx_lua based on the cosocket API

Table of Contents

Status

This library is considered production ready.

Description

This Lua library is a MySQL client driver for the ngx_lua nginx module:

https://github.com/openresty/lua-nginx-module

This Lua library takes advantage of ngx_lua's cosocket API, which ensures 100% nonblocking behavior.

Note that at least ngx_lua 0.9.11 or ngx_openresty 1.7.4.1 is required.

Also, the bit library is also required. If you're using LuaJIT 2 with ngx_lua, then the bit library is already available by default.

Synopsis

    # you do not need the following line if you are using
    # the ngx_openresty bundle:
    lua_package_path "/path/to/lua-resty-mysql/lib/?.lua;;";

    server {
        location /test {
            content_by_lua '
                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) -- 1 sec

                -- or connect to a unix domain socket file listened
                -- by a mysql server:
                --     local ok, err, errcode, sqlstate =
                --           db:connect{
                --              path = "/path/to/mysql.sock",
                --              database = "ngx_test",
                --              user = "ngx_test",
                --              password = "ngx_test" }

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

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

                ngx.say("connected to mysql.")

                local res, err, errcode, sqlstate =
                    db:query("drop table if exists cats")
                if not res then
                    ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".")
                    return
                end

                res, err, errcode, sqlstate =
                    db:query("create table cats "
                             .. "(id serial primary key, "
                             .. "name varchar(5))")
                if not res then
                    ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".")
                    return
                end

                ngx.say("table cats created.")

                res, err, errcode, sqlstate =
                    db:query("insert into cats (name) "
                             .. "values (\'Bob\'),(\'\'),(null)")
                if not res then
                    ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".")
                    return
                end

                ngx.say(res.affected_rows, " rows inserted into table cats ",
                        "(last insert id: ", res.insert_id, ")")

                -- run a select query, expected about 10 rows in
                -- the result set:
                res, err, errcode, sqlstate =
                    db:query("select * from cats order by id asc", 10)
                if not res then
                    ngx.say("bad result: ", err, ": ", errcode, ": ", sqlstate, ".")
                    return
                end

                local cjson = require "cjson"
                ngx.say("result: ", cjson.encode(res))

                -- put it into the connection pool of size 100,
                -- with 10 seconds max idle timeout
                local ok, err = db:set_keepalive(10000, 100)
                if not ok then
                    ngx.say("failed to set keepalive: ", err)
                    return
                end

                -- or just close the connection right away:
                -- local ok, err = db:close()
                -- if not ok then
                --     ngx.say("failed to close: ", err)
                --     return
                -- end
            ';
        }
    }

Back to TOC

Methods

Back to TOC

new

syntax: db, err = mysql:new()

Creates a MySQL connection object. In case of failures, returns nil and a string describing the error.

Back to TOC

connect

syntax: ok, err, errcode, sqlstate = db:connect(options)

Attempts to connect to the remote MySQL server.

The options argument is a Lua table holding the following keys:

  • host

    the host name for the MySQL server.

  • port

    the port that the MySQL server is listening on. Default to 3306.

  • path

    the path of the unix socket file listened by the MySQL server.

  • database

    the MySQL database name.

  • user

    MySQL account name for login.

  • password

    MySQL account password for login (in clear text).

  • charset

    the character set used on the MySQL connection, which can be different from the default charset setting. The following values are accepted: big5, dec8, cp850, hp8, koi8r, latin1, latin2, swe7, ascii, ujis, sjis, hebrew, tis620, euckr, koi8u, gb2312, greek, cp1250, gbk, latin5, armscii8, utf8, ucs2, cp866, keybcs2, macce, macroman, cp852, latin7, utf8mb4, cp1251, utf16, utf16le, cp1256, cp1257, utf32, binary, geostd8, cp932, eucjpms, gb18030.

  • max_packet_size

    the upper limit for the reply packets sent from the MySQL server (default to 1MB).

  • ssl

    If set to true, then uses SSL to connect to MySQL (default to false). If the MySQL server does not have SSL support (or just disabled), the error string "ssl disabled on server" will be returned.

  • ssl_verify

    If set to true, then verifies the validity of the server SSL certificate (default to false). Note that you need to configure the lua_ssl_trusted_certificate to specify the CA (or server) certificate used by your MySQL server. You may also need to configure lua_ssl_verify_depth accordingly.

  • pool

    the name for the MySQL connection pool. if omitted, an ambiguous pool name will be generated automatically with the string template user:database:host:port or user:database:path. (this option was first introduced in v0.08.)

  • pool_size

    Specifies the size of the connection pool. If omitted and no backlog option was provided, no pool will be created. If omitted but backlog was provided, the pool will be created with a default size equal to the value of the lua_socket_pool_size directive. The connection pool holds up to pool_size alive connections ready to be reused by subsequent calls to connect, but note that there is no upper limit to the total number of opened connections outside of the pool. If you need to restrict the total number of opened connections, specify the backlog option. When the connection pool would exceed its size limit, the least recently used (kept-alive) connection already in the pool will be closed to make room for the current connection. Note that the cosocket connection pool is per Nginx worker process rather than per Nginx server instance, so the size limit specified here also applies to every single Nginx worker process. Also note that the size of the connection pool cannot be changed once it has been created. Note that at least ngx_lua 0.10.14 is required to use this options.

  • backlog

    If specified, this module will limit the total number of opened connections for this pool. No more connections than pool_size can be opened for this pool at any time. If the connection pool is full, subsequent connect operations will be queued into a queue equal to this option's value (the "backlog" queue). If the number of queued connect operations is equal to backlog, subsequent connect operations will fail and return nil plus the error string "too many waiting connect operations". The queued connect operations will be resumed once the number of connections in the pool is less than pool_size. The queued connect operation will abort once they have been queued for more than connect_timeout, controlled by set_timeout, and will return nil plus the error string "timeout". Note that at least ngx_lua 0.10.14 is required to use this options.

  • compact_arrays

    when this option is set to true, then the query and read_result methods will return the array-of-arrays structure for the resultset, rather than the default array-of-hashes structure.

Before actually resolving the host name and connecting to the remote backend, this method will always look up the connection pool for matched idle connections created by previous calls of this method.

Back to TOC

set_timeout

syntax: db:set_timeout(time)

Sets the timeout (in ms) protection for subsequent operations, including the connect method.

Back to TOC

set_keepalive

syntax: ok, err = db:set_keepalive(max_idle_timeout, pool_size)

Puts the current MySQL connection immediately into the ngx_lua cosocket connection pool.

You can specify the max idle timeout (in ms) when the connection is in the pool and the maximal size of the pool every nginx worker process.

In case of success, returns 1. In case of errors, returns nil with a string describing the error.

Only call this method in the place you would have called the close method instead. Calling this method will immediately turn the current resty.mysql object into the closed state. Any subsequent operations other than connect() on the current objet will return the closed error.

Back to TOC

get_reused_times

syntax: times, err = db:get_reused_times()

This method returns the (successfully) reused times for the current connection. In case of error, it returns nil and a string describing the error.

If the current connection does not come from the built-in connection pool, then this method always returns 0, that is, the connection has never been reused (yet). If the connection comes from the connection pool, then the return value is always non-zero. So this method can also be used to determine if the current connection comes from the pool.

Back to TOC

close

syntax: ok, err = db:close()

Closes the current mysql connection and returns the status.

In case of success, returns 1. In case of errors, returns nil with a string describing the error.

Back to TOC

send_query

syntax: bytes, err = db:send_query(query)

Sends the query to the remote MySQL server without waiting for its replies.

Returns the bytes successfully sent out in success and otherwise returns nil and a string describing the error.

You should use the read_result method to read the MySQL replies afterwards.

Back to TOC

read_result

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

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

Reads in one result returned from the MySQL server.

It returns a Lua table (res) describing the MySQL OK packet or result set packet for the query result.

For queries corresponding to a result set, it returns an array holding all the rows. Each row holds key-value pairs for each data fields. For instance,

    {
        { name = "Bob", age = 32, phone = ngx.null },
        { name = "Marry", age = 18, phone = "10666372"}
    }

For queries that do not correspond to a result set, it returns a Lua table like this:

    {
        insert_id = 0,
        server_status = 2,
        warning_count = 1,
        affected_rows = 32,
        message = nil
    }

If more results are following the current result, a second err return value will be given the string again. One should always check this (second) return value and if it is again, then she should call this method again to retrieve more results. This usually happens when the original query contains multiple statements (separated by semicolon in the same query string) or calling a MySQL procedure. See also Multi-Resultset Support.

In case of errors, this method returns at most 4 values: nil, err, errcode, and sqlstate. The err return value contains a string describing the error, the errcode return value holds the MySQL error code (a numerical value), and finally, the sqlstate return value contains the standard SQL error code that consists of 5 characters. Note that, the errcode and sqlstate might be nil if MySQL does not return them.

The optional argument nrows can be used to specify an approximate number of rows for the result set. This value can be used to pre-allocate space in the resulting Lua table for the result set. By default, it takes the value 4.

Back to TOC

query

syntax: res, err, errcode, sqlstate = db:query(query)

syntax: res, err, errcode, sqlstate = db:query(query, nrows)

This is a shortcut for combining the send_query call and the first read_result call.

You should always check if the err return value is again in case of success because this method will only call read_result only once for you. See also Multi-Resultset Support.

Back to TOC

server_ver

syntax: str = db:server_ver()

Returns the MySQL server version string, like "5.1.64".

You should only call this method after successfully connecting to a MySQL server, otherwise nil will be returned.

Back to TOC

set_compact_arrays

syntax: db:set_compact_arrays(boolean)

Sets whether to use the "compact-arrays" structure for the resultsets returned by subsequent queries. See the compact_arrays option for the connect method for more details.

This method was first introduced in the v0.09 release.

Back to TOC

SQL Literal Quoting

It is always important to quote SQL literals properly to prevent SQL injection attacks. You can use the ngx.quote_sql_str function provided by ngx_lua to quote values. Here is an example:

    local name = ngx.unescape_uri(ngx.var.arg_name)
    local quoted_name = ngx.quote_sql_str(name)
    local sql = "select * from users where name = " .. quoted_name

Back to TOC

Multi-Resultset Support

For a SQL query that produces multiple result-sets, it is always your duty to check the "again" error message returned by the query or read_result method calls, and keep pulling more result sets by calling the read_result method until no "again" error message returned (or some other errors happen).

Below is a trivial example for this:

    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 = "world",
        user = "monty",
        password = "pass"})

    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("select 1; select 2; select 3;")
    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

This code snippet will produce the following response body data:

result #1: [{"1":"1"}]
result #2: [{"2":"2"}]
result #3: [{"3":"3"}]

Back to TOC

Debugging

It is usually convenient to use the lua-cjson library to encode the return values of the MySQL query methods to JSON. For example,

    local cjson = require "cjson"
    ...
    local res, err, errcode, sqlstate = db:query("select * from cats")
    if res then
        print("res: ", cjson.encode(res))
    end

Back to TOC

Automatic Error Logging

By default the underlying ngx_lua module does error logging when socket errors happen. If you are already doing proper error handling in your own Lua code, then you are recommended to disable this automatic error logging by turning off ngx_lua's lua_socket_log_errors directive, that is,

    lua_socket_log_errors off;

Back to TOC

Limitations

  • This library cannot be used in code contexts like init_by_lua*, set_by_lua*, log_by_lua*, and header_filter_by_lua* where the ngx_lua cosocket API is not available.
  • The resty.mysql object instance cannot be stored in a Lua variable at the Lua module level, because it will then be shared by all the concurrent requests handled by the same nginx worker process (see https://github.com/openresty/lua-nginx-module#data-sharing-within-an-nginx-worker ) and result in bad race conditions when concurrent requests are trying to use the same resty.mysql instance. You should always initiate resty.mysql objects in function local variables or in the ngx.ctx table. These places all have their own data copies for each request.

Back to TOC

More Authentication Method Support

By default, Of all authentication method, only Old Password Authentication(mysql_old_password) and Secure Password Authentication(mysql_native_password) are suppored. If the server requires sha256_password or cache_sha2_password, an error like auth plugin caching_sha2_password or sha256_password are not supported because resty.rsa is not installed may be returned.

Need lua-resty-rsa when using the sha256_password and cache_sha2_password.

Back to TOC

Installation

If you are using the ngx_openresty bundle (http://openresty.org ), then you do not need to do anything because it already includes and enables lua-resty-mysql by default. And you can just use it in your Lua code, as in

    local mysql = require "resty.mysql"
    ...

If you are using your own nginx + ngx_lua build, then you need to configure the lua_package_path directive to add the path of your lua-resty-mysql source tree to ngx_lua's LUA_PATH search path, as in

    # nginx.conf
    http {
        lua_package_path "/path/to/lua-resty-mysql/lib/?.lua;;";
        ...
    }

Ensure that the system account running your Nginx ''worker'' proceses have enough permission to read the .lua file.

Back to TOC

Community

Back to TOC

English Mailing List

The openresty-en mailing list is for English speakers.

Back to TOC

Chinese Mailing List

The openresty mailing list is for Chinese speakers.

Back to TOC

Bugs and Patches

Please submit bug reports, wishlists, or patches by

  1. creating a ticket on the GitHub Issue Tracker,
  2. or posting to the OpenResty community.

Back to TOC

TODO

  • improve the MySQL connection pool support.
  • implement the MySQL binary row data packets.
  • implement MySQL server prepare and execute packets.
  • implement the data compression support in the protocol.

Back to TOC

Author

Yichun "agentzh" Zhang (η« δΊ¦ζ˜₯) [email protected], OpenResty Inc.

Back to TOC

Copyright and License

This module is licensed under the BSD license.

Copyright (C) 2012-2018, by Yichun "agentzh" Zhang (η« δΊ¦ζ˜₯) [email protected], OpenResty Inc.

All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

  • Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

  • Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Back to TOC

See Also

Back to TOC

More Repositories

1

openresty

High Performance Web Platform Based on Nginx and LuaJIT
C
12,431
star
2

lua-nginx-module

Embed the Power of Lua into NGINX HTTP servers
C
11,224
star
3

nginx-tutorials

Nginx Tutorials
Perl
2,864
star
4

lua-resty-redis

Lua redis client driver for the ngx_lua based on the cosocket API
Lua
1,893
star
5

openresty-systemtap-toolkit

Real-time analysis and diagnostics tools for OpenResty (including NGINX, LuaJIT, ngx_lua, and more) based on SystemTap
Perl
1,653
star
6

headers-more-nginx-module

Set, add, and clear arbitrary output headers in NGINX http servers
C
1,636
star
7

openresty.org

Code and data for the openresty.org site
HTML
1,262
star
8

luajit2

OpenResty's Branch of LuaJIT 2
C
1,223
star
9

echo-nginx-module

An Nginx module for bringing the power of "echo", "sleep", "time" and more to Nginx's config file
C
1,162
star
10

docker-openresty

Docker tooling for OpenResty
Dockerfile
934
star
11

redis2-nginx-module

Nginx upstream module for the Redis 2.0 protocol
C
902
star
12

lua-resty-limit-traffic

Lua library for limiting and controlling traffic in OpenResty/ngx_lua
Lua
815
star
13

lua-resty-core

New FFI-based API for lua-nginx-module
Lua
797
star
14

stream-lua-nginx-module

Embed the power of Lua into NGINX TCP/UDP servers
C
723
star
15

stapxx

Simple macro language extentions to systemtap
Perl
692
star
16

sregex

A non-backtracking NFA/DFA-based Perl-compatible regex engine matching on large data streams
C
616
star
17

lua-resty-upstream-healthcheck

Health Checker for Nginx Upstream Servers in Pure Lua
Lua
513
star
18

lua-resty-websocket

WebSocket support for the ngx_lua module (and OpenResty)
Lua
503
star
19

lua-upstream-nginx-module

Nginx C module to expose Lua API to ngx_lua for Nginx upstreams
C
499
star
20

srcache-nginx-module

Transparent subrequest-based caching layout for arbitrary nginx locations.
C
474
star
21

opm

OpenResty Package Manager
Lua
458
star
22

test-nginx

Data-driven test scaffold for Nginx C module and OpenResty Lua library development
Perl
438
star
23

lua-resty-lrucache

Lua-land LRU Cache based on LuaJIT FFI
Lua
437
star
24

lua-resty-string

String utilities and common hash functions for ngx_lua and LuaJIT
Lua
426
star
25

lua-resty-upload

Streaming reader and parser for http file uploading based on ngx_lua cosocket
Lua
401
star
26

set-misc-nginx-module

Various set_xxx directives added to nginx's rewrite module (md5/sha1, sql/json quoting, and many more)
C
388
star
27

drizzle-nginx-module

an nginx upstream module that talks to mysql and drizzle by libdrizzle
C
336
star
28

openresty-gdb-utils

GDB Utilities for OpenResty (including Nginx, ngx_lua, LuaJIT, and more)
Python
335
star
29

lua-resty-balancer

A generic consistent hash implementation for OpenResty/Lua
Lua
322
star
30

lua-resty-dns

DNS resolver for the nginx lua module
Lua
321
star
31

programming-openresty

Programming OpenResty Book
Perl
317
star
32

lua-resty-lock

Simple nonblocking lock API for ngx_lua based on shared memory dictionaries
Lua
304
star
33

openresty-devel-utils

Utilities for nginx module development
Perl
266
star
34

resty-cli

Fancy command-line utilities for OpenResty
Perl
263
star
35

replace-filter-nginx-module

Streaming regular expression replacement in response bodies
C
258
star
36

memc-nginx-module

An extended version of the standard memcached module that supports set, add, delete, and many more memcached commands.
C
213
star
37

lua-resty-memcached

Lua memcached client driver for the ngx_lua based on the cosocket API
Lua
210
star
38

encrypted-session-nginx-module

encrypt and decrypt nginx variable values
C
198
star
39

openresty-packaging

Official OpenResty packaging source and scripts for various Linux distributions and other systems
Makefile
176
star
40

rds-json-nginx-module

An nginx output filter that formats Resty DBD Streams generated by ngx_drizzle and others to JSON
C
153
star
41

xss-nginx-module

Native support for cross-site scripting (XSS) in an nginx
C
148
star
42

mockeagain

Mocking ideally slow network that only allows reading and/or writing one byte at a time
C
129
star
43

lua-resty-shell

Lua module for nonblocking system shell command executions
Perl
124
star
44

lua-tablepool

Lua table recycling pools for LuaJIT
Perl
112
star
45

lua-redis-parser

Lua module for parsing raw redis responses
C
93
star
46

openresty-survey

OpenResty Web App for OpenResty User Survey
HTML
90
star
47

lua-ssl-nginx-module

NGINX C module that extends ngx_http_lua_module for enhanced SSL/TLS capabilities
Lua
86
star
48

opsboy

A rule-based sysadmin tool that helps setting up complex environment for blank machines
Perl
85
star
49

no-pool-nginx

replace nginx's pool mechanism with plain malloc & free to help tools like valgrind
Shell
77
star
50

stream-echo-nginx-module

TCP/stream echo module for NGINX (a port of ngx_http_echo_module)
C
69
star
51

meta-lua-nginx-module

Meta Lua Nginx Module supporting both Http Lua Module and Stream Lua Module
C
66
star
52

array-var-nginx-module

Add support for array-typed variables to nginx config files
C
66
star
53

lemplate

OpenResty/Lua template framework implementing Perl's TT2 templating language
Perl
54
star
54

openresty-con

JavaScript
48
star
55

nginx-dtrace

An nginx fork that adds dtrace USDT probes
C
44
star
56

orbpf-ko

The orbpf (eBPF+) Linux kernel module from OpenResty Inc.
C
37
star
57

lua-resty-memcached-shdict

Powerful memcached client with a shdict caching layer and many other features
Lua
33
star
58

lua-resty-signal

Lua library for killing or sending signals to UNIX processes
Perl
32
star
59

lua-resty-shdict-simple

Simple applicaton-oriented interface to the OpenResty shared dictionary API
Perl
32
star
60

luajit2-test-suite

OpenResty's LuaJIT test suite based on Mike Pall's LuaJIT tests
Lua
29
star
61

ngx_postgres

OpenResty's fork of FRiCKLE/ngx_postgres
C
26
star
62

rds-csv-nginx-module

Nginx output filter module to convert Resty-DBD-Streams (RDS) to Comma-Separated Values (CSV)
C
22
star
63

showman-samples

Sample screenplay files for generating our public video tutorials using OpenResty Showman
20
star
64

lua-rds-parser

Resty DBD Stream (RDS) parser for Lua written in C
C
19
star
65

redis-nginx-module

8
star
66

AB-test-http

test http requests between two systems.
Perl
5
star
67

transparency

2
star