There are a number of internal structures within the scripting
element of MySQL Proxy. The primary structure is
proxy
and this provides an interface to the
many common structures used throughout the script, such as
connection lists and configured backend servers. Other
structures, such as the incoming packet from the client and
result sets are only available within the context of one of the
scriptable functions.
Attribute | Description |
---|---|
connection |
A structure containing the active client connections. For a list of
attributes, see
proxy.connection . |
servers |
A structure containing the list of configured backend servers. For a
list of attributes, see
proxy.global.backends . |
queries |
A structure containing the queue of queries that will be sent to the
server during a single client query. For a list of
attributes, see
proxy.queries . |
PROXY_VERSION |
The version number of MySQL Proxy, encoded in hex. You can use this to
check that the version number supports a particular
option from within the Lua script. Note that the value
is encoded as a hex value, so to check the version is at
least 0.5.1 you compare against
0x00501 . |
The proxy.connection
object is read only, and
provides information about the current connection, and is split
into a client
and server
tables. This allows you to examine information about both the
incoming client connections to the proxy
(client
), and to the backend servers
(server
).
Attribute | Description |
---|---|
client.default_db |
Default database request by the client |
client.username |
Username used to authenticate |
client.scrambled_password |
The scrambled version of the password used to authenticate |
client.dst.name |
The combined address:port of the Proxy port used by
this client (should match the
--proxy-address configuration
parameter) |
client.dst.address |
The IP address of the of the Proxy port used by this client |
client.dst.port |
The port number of the of the Proxy port used by this client |
client.src.name |
The combined address:port of the client (originating)
TCP/IP endpoint |
client.src.address |
The IP address of the client (originating) TCP/IP port |
client.src.port |
The port of the client (originating) TCP/IP endpoint |
server.scramble_buffer |
The scramble buffer used to scramble the password |
server.mysqld_version |
The MySQL version number of the server |
server.thread_id |
The ID of the thread handling the connection to the current server |
server.dst.name |
The combined address:port for the backend server for
the current connection (i.e. the connection to the MySQL
server) |
server.dst.address |
The address for the backend server |
server.dst.port |
The port for the backend server |
server.src.name |
The combined address:port for the TCP/IP endpoint
used by the Proxy to connect to the backend server |
server.src.address |
The address of the endpoint for the proxy-side connection to the MySQL server |
server.src.port |
The port of the endpoint for the proxy-side connection to the MySQL server |
The proxy.global.backends
table is partially
writable and contains an array of all the configured backend
servers and the server metadata (IP address, status, etc.). You
can determine the array index of the current connection using
proxy.connection["backend_ndx"]
which is the
index into this table of the backend server being used by the
active connection.
The attributes for each entry within the
proxy.global.backends
table are shown in this
table.
Attribute | Description |
---|---|
dst.name |
The combined address:port of the backend server |
dst.address |
The IP address of the backend server |
dst.port |
The port of the backend server |
connected_clients |
The number of clients currently connected. |
state |
The status of the backend server. See Section 14.6.4.2, “Internal Structures”. |
type |
The type of the backend server. You can use this to identify whether the
backed was configured as a standard read/write backend,
or a read-only backend. You can compare this value to
the proxy.BACKEND_TYPE_RW and
proxy.BACKEND_TYPE_RO . |
The proxy.queries
object is a queue
representing the list of queries to be sent to the server. The
queue is not populated automatically, but if you do not
explicitly populate the queue then queries are passed on to the
backend server verbatim. Also, if you do not populate the query
queue by hand, then the read_query_result()
function is not triggered.
The following methods are supported for populating the
proxy.queries
object.
Function | Description |
---|---|
append(id,packet,[options]) |
Appends a query to the end of the query queue. The id
is an integer identifier that you can use to recognize
the query results when they are returned by the server.
The packet should be a properly formatted query packet.
The optional options should be a
table containing the options specific to this packet. |
prepend(id,packet) |
Prepends a query to the query queue. The id is an
identifier that you can use to recognize the query
results when they are returned by the server. The packet
should be a properly formatted query packet. |
reset() |
Empties the query queue. |
len() |
Returns the number of query packets in the queue. |
For example, you could append a query packet to the
proxy.queries
queue by using the
append()
:
proxy.queries:append(1,packet)
The optional third argument to append()
should contain the options for the packet. If you want to have
access to the result set through the
read_query_result()
function, you must set
the resultset_is_needed
flag to true:
proxy.queries:append( 1, packet, { resultset_is_needed = true } )
If that flag is false (the default), proxy will:
Send the resultset to the client as soon as the resultset is received
Reduce memory usage (because the resultset is not stored internally for processing)
Lower latency of results back to the client
Pass on data from server to client unaltered
The default mode is therefore quicker and useful if you only want to monitor the queries sent, and the basic statistics.
If, however, you want to perform any kind of manipulation on the returned data, you must set the flag to true, which will:
Store the resultset so that it can be processed
Allow modification of the resultset before it is returned to the client
Allow you to discard the resultset in place of returning it to the client
The proxy.response
structure is used when you
want to return your own MySQL response, instead of forwarding a
packet that you have received a backend server. The structure
holds the response type information, an optional error message,
and the result set (rows/columns) that you want to return.
Attribute | Description |
---|---|
type |
The type of the response. The type must be either
MYSQLD_PACKET_OK or
MYSQLD_PACKET_ERR . If the
MYSQLD_PACKET_ERR , then you should
set the value of the
mysql.response.errmsg with a suitable
error message. |
errmsg |
A string containing the error message that will be returned to the client. |
resultset |
A structure containing the result set information (columns and rows),
identical to what would be returned when returning a
results from a SELECT
query. |
When using proxy.response
you either set
proxy.response.type
to
proxy.MYSQLD_PACKET_OK
and then build
resultset
to contain the results that you
want to return, or set proxy.response.type
to
proxy.MYSQLD_PACKET_ERR
and set the
proxy.response.errmsg
to a string with the
error message. To send the completed resultset or error message,
you should return the proxy.PROXY_SEND_RESULT
to trigger the return of the packet information.
An example of this can be seen in the
tutorial-resultset.lua
script within the
MySQL Proxy package:
if string.lower(command) == "show" and string.lower(option) == "querycounter" then --- -- proxy.PROXY_SEND_RESULT requires -- -- proxy.response.type to be either -- * proxy.MYSQLD_PACKET_OK or -- * proxy.MYSQLD_PACKET_ERR -- -- for proxy.MYSQLD_PACKET_OK you need a resultset -- * fields -- * rows -- -- for proxy.MYSQLD_PACKET_ERR -- * errmsg proxy.response.type = proxy.MYSQLD_PACKET_OK proxy.response.resultset = { fields = { { type = proxy.MYSQL_TYPE_LONG, name = "global_query_counter", }, { type = proxy.MYSQL_TYPE_LONG, name = "query_counter", }, }, rows = { { proxy.global.query_counter, query_counter } } } -- we have our result, send it back return proxy.PROXY_SEND_RESULT elseif string.lower(command) == "show" and string.lower(option) == "myerror" then proxy.response.type = proxy.MYSQLD_PACKET_ERR proxy.response.errmsg = "my first error" return proxy.PROXY_SEND_RESULT
The proxy.response.resultset
structure should
be populated with the rows and columns of data that you want to
return. The structure contains the information about the entire
result set, with the individual elements of the data shown in
the table below.
Attribute | Description |
---|---|
fields |
The definition of the columns being returned. This should be a
dictionary structure with the type
specifying the MySQL data type, and the
name specifying the column name.
Columns should be listed in the order of the column data
that will be returned. |
flags |
A number of flags related to the resultset. Valid flags include
auto_commit (whether an automatic
commit was triggered),
no_good_index_used (the query
executed without using an appropriate index), and
no_index_used (the query executed
without using any index). |
rows |
The actual row data. The information should be returned as an array of arrays. Each inner array should contain the column data, with the outer array making up the entire result set. |
warning_count |
The number of warnings for this result set. |
affected_rows |
The number of rows affected by the original statement. |
insert_id |
The last insert ID for an auto-incremented column in a table. |
query_status |
The status of the query operation. You can use the
MYSQLD_PACKET_OK or
MYSQLD_PACKET_ERR constants to
populate this parameter. |
For an example of the population of this table, see Section 14.6.4.2, “Internal Structures”.
The following constants are used internally by the proxy to
specify the response to send to the client or server. All
constants are exposed as values within the main
proxy
table.
Constant | Description |
---|---|
PROXY_SEND_QUERY |
Causes the proxy to send the current contents of the queries queue to the server. |
PROXY_SEND_RESULT |
Causes the proxy to send a result set back to the client. |
PROXY_IGNORE_RESULT |
Causes the proxy to drop the result set (nothing is returned to the client). |
As constants, these entities are available without qualification
in the Lua scripts. For example, at the end of the
read_query_result()
you might return
PROXY_IGNORE_RESULT:
return proxy.PROXY_IGNORE_RESULT
The following states describe the status of a network packet.
These items are entries within the main proxy
table.
Constant | Description |
---|---|
MYSQLD_PACKET_OK |
The packet is OK. |
MYSQLD_PACKET_ERR |
The packet contains error information. |
MYSQLD_PACKET_RAW |
The packet contains raw data. |
The following constants are used either to define the status of
the backend server (the MySQL server to which the proxy is
connected) or the type of backend server. These items are
entries within the main proxy
table.
Constant | Description |
---|---|
BACKEND_STATE_UNKNOWN |
The current status is unknown. |
BACKEND_STATE_UP |
The backend is known to be up (available). |
BACKEND_STATE_DOWN |
The backend is known to be down (unavailable). |
BACKEND_TYPE_UNKNOWN |
Backend type is unknown. |
BACKEND_TYPE_RW |
Backend is available for read/write. |
BACKEND_TYPE_RO |
Backend is available only for read-only use. |
The following values are used in the packets exchanged between
the client and server to identify the information in the rest of
the packet. These items are entries within the main
proxy
table. The packet type is defined as
the first character in the sent packet. For example, when
intercepting packets from the client to edit or monitor a query
you would check that the first byte of the packet was of type
proxy.COM_QUERY
.
Constant | Description |
---|---|
COM_SLEEP |
Sleep |
COM_QUIT |
Quit |
COM_INIT_DB |
Initialize database |
COM_QUERY |
Query |
COM_FIELD_LIST |
Field List |
COM_CREATE_DB |
Create database |
COM_DROP_DB |
Drop database |
COM_REFRESH |
Refresh |
COM_SHUTDOWN |
Shutdown |
COM_STATISTICS |
Statistics |
COM_PROCESS_INFO |
Process List |
COM_CONNECT |
Connect |
COM_PROCESS_KILL |
Kill |
COM_DEBUG |
Debug |
COM_PING |
Ping |
COM_TIME |
Time |
COM_DELAYED_INSERT |
Delayed insert |
COM_CHANGE_USER |
Change user |
COM_BINLOG_DUMP |
Binlog dump |
COM_TABLE_DUMP |
Table dump |
COM_CONNECT_OUT |
Connect out |
COM_REGISTER_SLAVE |
Register slave |
COM_STMT_PREPARE |
Prepare server-side statement |
COM_STMT_EXECUTE |
Execute server-side statement |
COM_STMT_SEND_LONG_DATA |
Long data |
COM_STMT_CLOSE |
Close server-side statement |
COM_STMT_RESET |
Reset statement |
COM_SET_OPTION |
Set option |
COM_STMT_FETCH |
Fetch statement |
COM_DAEMON |
Daemon (MySQL 5.1 only) |
COM_ERROR |
Error |
These constants are used to identify the field types in the
query result data returned to clients from the result of a
query. These items are entries within the main
proxy
table.
Constant | Field Type |
---|---|
MYSQL_TYPE_DECIMAL |
Decimal |
MYSQL_TYPE_NEWDECIMAL |
Decimal (MySQL 5.0 or later) |
MYSQL_TYPE_TINY |
Tiny |
MYSQL_TYPE_SHORT |
Short |
MYSQL_TYPE_LONG |
Long |
MYSQL_TYPE_FLOAT |
Float |
MYSQL_TYPE_DOUBLE |
Double |
MYSQL_TYPE_NULL |
Null |
MYSQL_TYPE_TIMESTAMP |
Timestamp |
MYSQL_TYPE_LONGLONG |
Long long |
MYSQL_TYPE_INT24 |
Integer |
MYSQL_TYPE_DATE |
Date |
MYSQL_TYPE_TIME |
Time |
MYSQL_TYPE_DATETIME |
Datetime |
MYSQL_TYPE_YEAR |
Year |
MYSQL_TYPE_NEWDATE |
Date (MySQL 5.0 or later) |
MYSQL_TYPE_ENUM |
Enumeration |
MYSQL_TYPE_SET |
Set |
MYSQL_TYPE_TINY_BLOB |
Tiny Blob |
MYSQL_TYPE_MEDIUM_BLOB |
Medium Blob |
MYSQL_TYPE_LONG_BLOB |
Long Blob |
MYSQL_TYPE_BLOB |
Blob |
MYSQL_TYPE_VAR_STRING |
Varstring |
MYSQL_TYPE_STRING |
String |
MYSQL_TYPE_TINY |
Tiny (compatible with MYSQL_TYPE_CHAR)
|
MYSQL_TYPE_ENUM |
Enumeration (compatible with MYSQL_TYPE_INTERVAL ) |
MYSQL_TYPE_GEOMETRY |
Geometry |
MYSQL_TYPE_BIT |
Bit |
User Comments
Add your own comment.