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)
}
}
}
Methods
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.
mysql_close
syntax: libmariadb.mysql_close(mysql_close)
Parameters:
- mysql - mysql handle, which was previously allocated by mysql_init()
Closes a previously opened connection.
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.
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.
mysql_free_result
syntax: libmariadb.mysql_free_result(result)
Parameters:
- result - a result set identifier returned by mysql_store_result() or mysql_use_result().
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
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.
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.
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().
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.
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.
TODO
Copyright and License
Copyright (C) 2021 by OpenResty Inc. All rights reserved.
License: Proprietary.