Repository files navigation

drizzle-nginx-module - Upstream module for talking to MySQL and Drizzle directly

This module is not distributed with the Nginx source. Seethe installation instructions.

• Status
• Version
• Synopsis
• Description
  • Keepalive connection pool
  • Last Insert ID
• Directives
  • drizzle_server
  • drizzle_keepalive
  • drizzle_query
  • drizzle_pass
  • drizzle_connect_timeout
  • drizzle_send_query_timeout
  • drizzle_recv_cols_timeout
  • drizzle_recv_rows_timeout
  • drizzle_buffer_size
  • drizzle_module_header
  • drizzle_status
• Variables
  • $drizzle_thread_id
• Output Format
  • RDS Header Part
  • RDS Body Part
    • Columns
    • Rows
      • Row Flag
      • Fields Data
  • RDS buffer Limitations
• Status Code
• Caveats
• Trouble Shooting
• Known Issues
• Installation
• Compatibility
• Community
  • English Mailing List
  • Chinese Mailing List
• Report Bugs
• Source Repository
• Test Suite
• TODO
• Changes
• Authors
• Copyright & License
• See Also

This module is already production ready.

This document describes ngx_drizzlev0.1.11 released on 19 April 2018.

http{     ...upstream cluster{         # simple round-robin         drizzle_server127.0.0.1:3306 dbname=test              password=some_passuser=montyprotocol=mysql;         drizzle_server127.0.0.1:1234 dbname=test2              password=passuser=bobprotocol=drizzle;}upstream backend{         drizzle_server127.0.0.1:3306 dbname=test              password=some_passuser=montyprotocol=mysql;}server{location /mysql{set$my_sql'select * from cats';             drizzle_query$my_sql;             drizzle_pass backend;             drizzle_connect_timeout500ms; # default60s             drizzle_send_query_timeout2s;    # default60s             drizzle_recv_cols_timeout1s;    # default60s             drizzle_recv_rows_timeout1s;    # default60s}         ...         # for connection pool monitoringlocation /mysql-pool-status{allow127.0.0.1;deny all;             drizzle_status;}}}

Back to TOC

This is an nginx upstream module integratinglibdrizzle into Nginx in a non-blocking and streamming way.

Essentially it provides a very efficient and flexible way for nginx internals to access MySQL, Drizzle, as well as other RDBMS's that support the Drizzle or MySQL wired protocol. Also it can serve as a direct REST interface to those RDBMS backends.

This module does not generate human-readable outputs, rather, in a binary format called Resty-DBD-Stream (RDS) designed by ourselves. You usually need other components, likerds-json-nginx-module,rds-csv-nginx-module, orlua-rds-parser, to work with this module. SeeOutput Format for details.

Back to TOC

This module also provides a builtin per-worker connection pool mechanism for MySQL or Drizzle TCP connections.

Here's a sample configuration:

upstream backend{     drizzle_server127.0.0.1:3306 dbname=test          password=some_passuser=montyprotocol=mysql;     drizzle_keepalive max=100 mode=single overflow=reject;}

For now, the connection pool uses a simple LIFO algorithm to assign idle connections in the pool. That is, most recently (successfully) used connections will be reused first the next time. And new idle connections will always replace the oldest idle connections in the pool even if the pool is already full.

See thedrizzle_keepalive directive for more details.

Back to TOC

If you want to get LAST_INSERT_ID, then ngx_drizzle already returns that automatically for you when you're doing a SQL insert query. Consider the following samplenginx.conf snippet:

location /test{     echo_location /mysql"drop table if exists foo";     echo;     echo_location /mysql"create table foo (id serial not null, primary key (id), val real);";     echo;     echo_location /mysql"insert into foo (val) values (3.1415926);";     echo;     echo_location /mysql"select * from foo;";     echo;}location /mysql{     drizzle_pass backend;     drizzle_module_header off;     drizzle_query$query_string;     rds_json on;}

Then requestGET /test gives the following outputs:

{"errcode":0}{"errcode":0}{"errcode":0,"insert_id":1,"affected_rows":1}[{"id":1,"val":3.1415926}]

You can see theinsert_id field (as well as theaffected_rows field in the 3rd JSON response.

Back to TOC

Back to TOC

syntax:drizzle_server <host> user=<user> password=<pass> dbname=<database>

syntax:drizzle_server <host>:<port> user=<user> password=<pass> dbname=<database> protocol=<protocol> charset=<charset>

default:no

context:upstream

Directive assigns the name and the parameters of server. For the name it is possible to use a domain name, an address, with an optional port (default: 3306). If domain name resolves to several addresses, then all are used.

The following options are supported:

user=<user>MySQL/Drizzle user name<user> for login.

password=<pass>Specify mysql password<pass>for login. If you have special characters like# or spaces in your password text, then you'll have to quote the whole key-value pair with either single-quotes or double-quotes, as in

 drizzle_server127.0.0.1:3306user=monty"password=a b#1"         dbname=testprotocol=mysql;

dbname=<database>Specify default MySQL database<database> for the connection. Note that MySQL does allow referencing tables belonging to different databases by qualifying table names with database names in SQL queries.

protocol=<protocol>Specify which wire protocol to use,drizzle ormysql. Default todrizzle.

charset=<charset>Explicitly specify the character set for the MySQL connections. Setting this option to a non-empty value will make this module send out aset names '<charset>' query right after the mysql connection is established.If the default character encoding of the MySQL connection is already what you want, you needn't set this option because it has extra runtime cost.Here is a small example:

 drizzle_server foo.bar.com:3306user=monty password=some_pass                                 dbname=testprotocol=mysqlcharset=utf8;

Please note that for the mysql server, "utf-8" is not a valid encoding name whileutf8 is.

Back to TOC

syntax:drizzle_keepalive max=<size> mode=<mode>

default:drizzle_keepalive max=0 mode=single

context:upstream

Configures the keep-alive connection pool for MySQL/Drizzle connections.

The following options are supported:

max=<num>Specify the capacity of the connection pool for the current upstream block. The valuemust be non-zero. If set to0, it effectively disables the connection pool. This option is default to0.

mode=<mode>This supports two values,single andmulti. Thesingle mode means the pool does not distinguish various drizzle servers in the current upstream block whilemulti means the pool will merely reuse connections which have identical server host names and ports. Note that even undermulti, differences betweendbname oruser parameters will be silently ignored. Default tosingle.

overflow=<action>This option specifies what to do when the connection pool is already full while new database connection is required. Eitherreject orignore can be specified. In case ofreject, it will reject the current request, and returns the503 Service Unavailable error page. Forignore, this module will go on creating a new database connection.

Back to TOC

syntax:drizzle_query <sql>

default:no

context:http, server, location, location if

Specify the SQL queries sent to the Drizzle/MySQL backend.

Nginx variable interpolation is supported, but you must be careful with SQL injection attacks. You can use theset_quote_sql_str directive, for example, to quote values for SQL interpolation:

location /cat{     set_unescape_uri$name$arg_name;     set_quote_sql_str$quoted_name$name;     drizzle_query"select * from cats where name = $quoted_name";     drizzle_pass my_backend;}

Back to TOC

syntax:drizzle_pass <remote>

default:no

context:location, location if

phase:content

This directive specifies the Drizzle or MySQL upstream name to be queried in the current location. The<remote> argument can be any upstream name defined with thedrizzle_server directive.

Nginx variables can also be interpolated into the<remote> argument, so as to do dynamic backend routing, for example:

upstream moon{ drizzle_server ...;}server{location /cat{set$backend'moon';         drizzle_query ...;         drizzle_pass$backend;}}

Back to TOC

syntax:drizzle_connect_time <time>

default:drizzle_connect_time 60s

context:http, server, location, location if

Specify the (total) timeout for connecting to a remote Drizzle or MySQL server.

The<time> argument can be an integer, with an optional time unit, likes (second),ms (millisecond),m (minute). The default time unit iss, i.e., "second". The default setting is60s.

Back to TOC

syntax:drizzle_send_query_timeout <time>

default:drizzle_send_query_timeout 60s

context:http, server, location, location if

Specify the (total) timeout for sending a SQL query to a remote Drizzle or MySQL server.

The<time> argument can be an integer, with an optional time unit, likes (second),ms (millisecond),m (minute). The default time unit iss, ie, "second". The default setting is60s.

Back to TOC

syntax:drizzle_recv_cols_timeout <time>

default:drizzle_recv_cols_timeout 60s

context:http, server, location, location if

Specify the (total) timeout for receiving the columns metadata of the result-set to a remote Drizzle or MySQL server.

The<time> argument can be an integer, with an optional time unit, likes (second),ms (millisecond),m (minute). The default time unit iss, ie, "second". The default setting is60s.

Back to TOC

syntax:drizzle_recv_rows_timeout <time>

default:drizzle_recv_rows_timeout 60s

context:http, server, location, location if

Specify the (total) timeout for receiving the rows data of the result-set (if any) to a remote Drizzle or MySQL server.

The<time> argument can be an integer, with an optional time unit, likes (second),ms (millisecond),m (minute). The default time unit iss, ie, "second". The default setting is60s.

Back to TOC

syntax:drizzle_buffer_size <size>

default:drizzle_buffer_size 4k/8k

context:http, server, location, location if

Specify the buffer size for drizzle outputs. Default to the page size (4k/8k). The larger the buffer, the less streammy the outputing process will be.

Back to TOC

syntax:drizzle_module_header on|off

default:drizzle_module_header on

context:http, server, location, location if

Controls whether to output the drizzle header in the response. Default on.

The drizzle module header looks like this:

X-Resty-DBD-Module: ngx_drizzle 0.1.0

Back to TOC

syntax:drizzle_status

default:no

context:location, location if

phase:content

When specified, the current Nginx location will output a status report for all the drizzle upstream servers in the virtual server of the current Nginx worker process.

The output looks like this:

worker process: 15231upstream backend  active connections: 0  connection pool capacity: 10  overflow: reject  cached connection queue: 0  free'd connection queue: 10  cached connection successfully used count:  free'd connection successfully used count: 3 0 0 0 0 0 0 0 0 0  servers: 1  peers: 1upstream backend2  active connections: 0  connection pool capacity: 0  servers: 1  peers: 1

Note that, this isnot the global statistics if you do have multiple Nginx worker processes configured in yournginx.conf.

Back to TOC

This module creates the following Nginx variables:

Back to TOC

This variable will be assigned a textual number of the underlying MySQL or Drizzle query thread ID when the current SQL query times out. This thread ID can be further used in a SQL kill command to cancel the timed-out query.

Here's an example:

 drizzle_connect_timeout1s; drizzle_send_query_timeout2s; drizzle_recv_cols_timeout1s; drizzle_recv_rows_timeout1s;location /query{     drizzle_query'select sleep(10)';     drizzle_pass my_backend;     rds_json on;     more_set_headers -s504'X-Mysql-Tid: $drizzle_thread_id';}location /kill{     drizzle_query"kill query $arg_tid";     drizzle_pass my_backend;     rds_json on;}location /main{     content_by_lua '         local res = ngx.location.capture("/query")if res.status~= ngx.HTTP_OK then             local tid = res.header["X-Mysql-Tid"]if tid and tid~="" then                 ngx.location.capture("/kill",{ args ={tid = tid}})             endreturn ngx.HTTP_INTERNAL_SERVER_ERROR;         end         ngx.print(res.body)     '}

where we make use ofheaders-more-nginx-module,lua-nginx-module, andrds-json-nginx-module too. When the SQL query timed out, we'll explicitly cancel it immediately. One pitfall here is that you have to add these modules in this order while building Nginx:

• lua-nginx-module
• headers-more-nginx-module
• rds-json-nginx-module

Such that, their output filters will work in thereversed order, i.e., first convert RDS to JSON, and then add ourX-Mysql-Tid custom header, and finally capture the whole (subrequest) response with the Lua module. You're recommended to use theOpenResty bundle though, it ensures the module building order automatically for you.

Back to TOC

This module generates binary query results in a format that is shared among the various Nginx database driver modules likengx_postgres. This data format is namedResty DBD Stream (RDS).

If you're a web app developer, you may be more interested in

• usingrds-json-nginx-module to obtain JSON output,
• usingrds-csv-nginx-module to obain Comma-Separated-Value (CSV) output,
• or usinglua-rds-parser to parse the RDS data into Lua data structures.

For the HTTP response header part, the200 OK status code should always be returned. TheContent-Type headermust be set toapplication/x-resty-dbd-stream. And the driver generating this response also sets aX-Resty-DBD header. For instance, this module adds the following output header:

X-Resty-DBD-Module: drizzle 0.1.0

where0.1.0 is this module's own version number. ThisX-Resty-DBD-Module header is optional though.

Below is the HTTP response body format (version 0.0.3):

Back to TOC

The RDS Header Part consists of the following fields:

uint8_tendian type (1 means big-endian and little endian otherwise)

uint32_tformat version (v1.2.3 is represented as 1002003 in decimal)

uint8_tresult type (0 means normal SQL result type, fixed for now)

uint16_tstandard error code

uint16_tdriver-specific error code

uint16_tdriver-specific error string length

**u_char ***driver-specific error string data

uint64_tdatabase rows affected

uint64_tinsert id (if none, 0)

uint16_tcolumn count

Back to TOC

When thecolumn count field in theRDS Header Part is zero, then the whole RDS Body Part is omitted.

The RDS Body Part consists of two sections,Columns andRows.

The columns part consists of zero or more column data. The number of columns is determined bycolumn count field inRDS Header Part.

Each column consists of the following fields

uint16_tnon-zero value for standard column type code and for the column list terminator and zero otherwise.

uint16_tdriver-specific column type code

uint16_tcolumn name length

**u_char ***column name data

The rows part consists of zero or more row data, terminated by a 8-bit zero.

Each row data consists of aRow Flag and an optionalFields Data part.

uint8_tvalid row (1 means valid, and 0 means the row list terminator)

The Fields Data consists zero or more fields of data. The field count is predetermined by thecolumn number</code) specified inRDS Header Part.

     mysql --protocol=tcp -u user --password=password -h foo.bar.com dbname

Note that the `--protocol=tcp` option is required here, or your MySQL client may use Unix Domain Socket to connect to your MySQL server.

     tar xzvf drizzle7-2011.07.21.tar.gz     cd drizzle7-2011.07.21/     ./configure --without-server     make libdrizzle-1.0     make install-libdrizzle-1.0

Ensure that you have the `python` command point to a `python2` interpreter. It's known that on recent : Arch Linux distribution, `python` is linked to `python3` by default, and while running `make libdrizzle-1.0` will yield the error

     File"config/pandora-plugin", line 185         print"Dependency loop detected with %s" % plugin['name']                                                  ^     SyntaxError: invalid syntax     make:*** [.plugin.scan] Error 1

You can fix this by pointing `python` to `python2`.

     wget'http://nginx.org/download/nginx-1.13.6.tar.gz'     tar -xzvf nginx-1.13.6.tar.gzcd nginx-1.13.6/# if you have installed libdrizzle to the prefix /opt/drizzle, then# specify the following environments:# export LIBDRIZZLE_INC=/opt/drizzle/include/libdrizzle-1.0# export LIBDRIZZLE_LIB=/opt/drizzle/lib# Here we assume you would install you nginx under /opt/nginx/.     ./configure --prefix=/opt/nginx \                 --add-module=/path/to/drizzle-nginx-module     make -j2     make install

 $ PATH=/path/to/your/nginx-with-echo-module:$PATH prove -r t