OpenResty Plus™ lua-resty-libmariadb Library

lua-resty-libmariadb - a mysql/mariadb client driver the based on the coro-nginx-module.

Table of Contents

Status

This library is considered production ready.

Description

This Lua library is a MySQL client driver using mariadb-connector-c: https://github.com/mariadb-corporation/mariadb-connector-c

Synopsis

    lua_package_path "/path/to/lua-resty-libmariadb/lib/?.lua;;";

    server {
        location /test {
            content_by_lua_block {
                local mdb = require "resty.libmariadb"
                local ctx = mdb.mysql_init()
                local res, err = mdb.mysql_real_connect2(ctx, "127.0.0.1",
                    "user_name", nil, "db_name", 0, nil, 0)
                if err ~= nil then
                    ngx.log(ngx.ERR, "mysql_real_connect2 failed: ", err)
                    return
                end

                ok, err = mdb.mysql_query(ctx, "SELECT * FROM cars")
                if not ok then
                    ngx.log(ngx.ERR, "mysql_query error: ", err)
                    return
                end

                local result, err = mdb.mysql_store_result(ctx)
                if not result then
                    ngx.log(ngx.ERR, "mysql_store_result error: ", err)
                    return
                end

                local num_fields = mdb.mysql_num_fields(result)
                ngx.say("num fields: ", num_fields)

                while(true)
                do
                    local row = mdb.mysql_fetch_row(ctx, result)
                    if row == nil then
                        break
                    end

                    for i = 0, num_fields - 1
                    do
                        local v = mdb.mysql_get_row_val_by_idx(row, i)
                        ngx.say(v)
                    end
                end

                mdb.mysql_free_result(result)
                mdb.mysql_close(ctx)
            }
        }
    }

Back to TOC

Methods

Back to TOC

mysql_affected_rows

syntax: row_count, err = libmariadb.mysql_affected_rows(mysql)

Parameters:

  • mysql is a connection identifier, which was previously allocated by mysql_init()

Returns the number of affected rows by the last operation associated with mysql, if the operation was an “upsert” (INSERT, UPDATE, DELETE or REPLACE) statement, or -1 if the last query failed.

Back to TOC

mysql_close

syntax: libmariadb.mysql_close(mysql_close)

Parameters:

  • mysql - mysql handle, which was previously allocated by mysql_init()

Closes a previously opened connection.

Back to TOC

mysql_field_count

syntax: cnt = libmariadb.mysql_field_count(mysql)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init()

Returns the number of columns for the most recent query on the connection represented by the link parameter as an unsigned integer. This function can be useful when using the mysql_store_result() function to determine if the query should have produced a non-empty result set or not without knowing the nature of the query.

The mysql_field_count() function should be used to determine if there is a result set available.

Back to TOC

mysql_fetch_row

syntax: row, err = libmariadb.mysql_fetch_row(result)

Parameters:

  • result - a result set identifier returned by mysql_store_result() or mysql_use_result().

Fetches one row of data from the result set and returns it as an array of char pointers (MYSQL_ROW), where each column is stored in an offset starting from 0 (zero). Each subsequent call to this function will return the next row within the result set, or NULL if there are no more rows.

Back to TOC

mysql_free_result

syntax: libmariadb.mysql_free_result(result)

Parameters:

  • result - a result set identifier returned by mysql_store_result() or mysql_use_result().

Back to TOC

mysql_get_from_pool

syntax: mysql, err = libmariadb.mysql_get_from_pool(mysql, pool_name, pool_size, max_idle_time)

Parameters:

  • pool_name - the name used to distinguish it from other cache pools, usually using an IP and port pair.

In case of success, this method returns connection from the pool; otherwise, it returns nil and a string describing the error.

Back to TOC

mysql_get_reused_times

syntax: times, err = libmariadb.mysql_get_reused_times(mysql)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init()

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

mysql_get_server_info

syntax: info, err = libmariadb.mysql_get_server_info(mysql)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init()

Returns the server info string or nil on failure.

Back to TOC

mysql_get_server_version

syntax: ver, err = libmariadb.mysql_get_server_version(mysql)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init()

Returns an integer representing the version of connected server.

Back to TOC

mysql_init

syntax: handler, err = libmariadb.mysql_init()

Prepares and initializes a MYSQL instance to be used with mysql_real_connect2().

Any subsequent calls to any mysql function (except mysql_options()) will fail until mysql_real_connect2() was called.

Back to TOC

mysql_insert_id

syntax: ok, err = libmariadb.mysql_insert_id(mysql)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init()

The mysql_insert_id() function returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute or the value for the last usage of LAST_INSERT_ID(expr). If the last query wasn’t an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute and LAST_INSERT_ID was not used, this function will return zero.

When performing a multi insert statement, mysql_insert_id() will return the value of the first row.

Back to TOC

mysql_next_result

syntax: ok, err = libmariadb.mysql_next_result(mysql)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init()

Prepares next result set from a previous call to mysql_real_query() which can be retrieved by mysql_store_result() or mysql_use_result(). Returns nil if an error occurred.

Back to TOC

mysql_num_fields

syntax: ok, err = libmariadb.mysql_num_fields(res)

Parameters:

  • res - A result set identifier returned by mysql_store_result() or mysql_use_result().

Returns number of fields in a specified result set.

Back to TOC

mysql_num_rows

syntax: rows, err = libmariadb.mysql_num_rows(res)

Parameters:

  • MYSQL_RES - a result set identifier returned by mysql_store_result() or mysql_use_result().

Returns number of rows in a result set.

The behaviour of mysql_num_rows() depends on whether buffered or unbuffered result sets are being used. For unbuffered result sets, mysql_num_rows() will not return the correct number of rows until all the rows in the result have been retrieved.

Back to TOC

mysql_options

syntax: ok, err = libmariadb.mysql_options(mysql, mysql_option, arg)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init() or mysql_real_connect().
  • mysql_option - the option you want to set. See description below.
  • arg - the value for the option.

Used to set extra connect options and affect behavior for a connection. This function may be called multiple times to set several options. mysql_options() should be called after mysql_init() and before mysql_real_connect().

Returns nil if an error occurred (invalid option or value).

Back to TOC

mysql_put_to_pool

syntax: ok, err = libmariadb.mysql_put_to_pool(mysql, pool_name, pool_size, max_idle_time)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init()
  • pool_name - the name used to distinguish it from other cache pools, usually using an IP and port pair.
  • pool_size - the max free mysql handles which will be cached by the pool
  • max_idle_time - the maximal idle time(ms) of the connections in the connection pool. When this timeout reaches, idle connections will be closed and removed from the pool.

Puts the current connection immediately into the connection pool and keep it alive until get_from_pool method calls request it or the associated maximal idle timeout is expired.

In case of success, this method returns 1; otherwise, it returns nil and a string describing the error.

When the system receive buffer for the current connection has unread data, then this method will return the “connection in dubious state” error message (as the second return value) because the previous session has unread data left behind for the next session and the connection is not safe to be reused.

Back to TOC

mysql_real_connect2

syntax: ok, err = libmariadb.mysql_real_connect2(mysql, host, user, passwd, db, port, unix_socket, flags)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init().
  • host - can be either a host name or an IP address. Passing the NULL value or the string “localhost” to this parameter, the local host is assumed. When possible, pipes will be used instead of the TCP/ - IP protocol.
  • user - the user name.
  • passwd - If provided or NULL, the server will attempt to authenticate the user against those user records which have no password only. This allows one username to be used with different permissions(depending on if a password as provided or not).
  • db - if provided will specify the default database to be used when performing queries.
  • port - specifies the port number to attempt to connect to the server.
  • unix_socket - specifies the socket or named pipe that should be used.
  • flags - the flags allows various connection options to be set:
    • CLIENT_FOUND_ROWS: Return the number of matched rows instead of number of changed rows.
    • CLIENT_NO_SCHEMA: Forbids the use of database.tablename.column syntax and forces the SQL parser to generate an error.
    • CLIENT_COMPRESS: Use compression protocol
    • CLIENT_IGNORE_SPACE: Allows spaces after function names. All function names will become reserved words.
    • CLIENT_LOCAL_FILES: Allows LOAD DATA LOCAL statements
    • CLIENT_MULTI_STATEMENTS: Allows the client to send multiple statements in one command. Statements will be divided by a semicolon.
    • CLIENT_MULTI_RESULTS: Indicates that the client is able to handle multiple result sets from stored procedures or multi statements. This option will be automatically set if CLIENT_MULTI_STATEMENTS is set.

Establishes a connection to a database server. Returns nil if an error occurred.

Back to TOC

mysql_real_query

syntax: ok, err = libmariadb.mysql_real_query(mysql, query, length)

Parameters:

  • mysql - a mysql handle, which was previously allocated by* mysql_init() or mysql_real_connect().
  • query - a string containing the statement to be performed.
  • length - length of the string.

mysql_real_query() is the binary safe function for performing a statement on the database server. Returns nil when failed.

Back to TOC

mysql_select_db

syntax: ok, err = libmariadb.mysql_select_db(mysql, db)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init().
  • db - the default database name

Selects a database as default. Returns nil on failure.

Back to TOC

mysql_server_end

syntax: ok, err = libmariadb.mysql_server_end()

Call when finished using the library, such as after disconnecting from the server or openresty is exiting.

Back to TOC

mysql_store_result

syntax: ok, err = libmariadb.mysql_store_result(mysql)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init()

Returns a buffered result set from the last executed query.

mysql_store_result() returns NULL in case an error occured or if the query didn’t return data (e.g. when executing an INSERT or UPDATE query.)

mysql_field_count() indicates if there will be a result set available.

The memory allocated by mysql_store_result() needs to be released by calling the function mysql_free_result().

Back to TOC

mysql_use_result

syntax: ok, err = libmariadb.mysql_use_result(mysql)

Parameters:

  • mysql - a mysql handle, which was previously allocated by mysql_init()

Used to initiate the retrieval of a result set from the last query executed using the mysql_real_query() function on the database connection. Either this or the mysql_store_result() function must be called before the results of a query can be retrieved, and one or the other must be called to prevent the next query on that database connection from failing.

Returns an unbuffered result set or NULL if an error occurred.

Back to TOC

Installation

You need to configure the lua_package_path directive to add the path of your lua-resty-libmariadb source tree to ngx_lua’s LUA_PATH search path, as in

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

Ensure that the system account running your Nginx ‘‘worker’’ processes have enough permission to read the .lua file.

Back to TOC

TODO

Back to TOC

Copyright (C) 2021 by OpenResty Inc. All rights reserved.

License: Proprietary.

Back to TOC

See Also

Back to TOC