16.7.4.2 Internal Structures

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.

The following table describes common attributes of the MySQL proxy scripting element.

AttributeDescription
connectionA structure containing the active client connections. For a list of attributes, see proxy.connection.
serversA structure containing the list of configured backend servers. For a list of attributes, see proxy.global.backends.
queriesA 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_VERSIONThe 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.

proxy.connection

The proxy.connection object is read only, and provides information about the current connection, and is split into a client and server tables. This enables you to examine information about both the incoming client connections to the proxy (client), and to the backend servers (server).

The following table describes the client and server attributes of the proxy.connection object.

AttributeDescription
client.default_dbDefault database requested by the client
client.usernameUser name used to authenticate
client.scrambled_passwordThe scrambled version of the password used to authenticate
client.dst.nameThe combined address:port of the Proxy port used by this client (should match the --proxy-address configuration parameter)
client.dst.addressThe IP address of the of the Proxy port used by this client
client.dst.portThe port number of the of the Proxy port used by this client
client.src.nameThe combined address:port of the client (originating) TCP/IP endpoint
client.src.addressThe IP address of the client (originating) TCP/IP port
client.src.portThe port of the client (originating) TCP/IP endpoint
server.scramble_bufferThe scramble buffer used to scramble the password
server.mysqld_versionThe MySQL version number of the server
server.thread_idThe ID of the thread handling the connection to the current server
server.dst.nameThe combined address:port for the backend server for the current connection (i.e. the connection to the MySQL server)
server.dst.addressThe address for the backend server
server.dst.portThe port for the backend server
server.src.nameThe combined address:port for the TCP/IP endpoint used by the Proxy to connect to the backend server
server.src.addressThe address of the endpoint for the proxy-side connection to the MySQL server
server.src.portThe port of the endpoint for the proxy-side connection to the MySQL server

proxy.global.backends

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 the following table.

AttributeDescription
dst.nameThe combined address:port of the backend server.
dst.addressThe IP address of the backend server.
dst.portThe port of the backend server.
connected_clientsThe number of clients currently connected.
stateThe status of the backend server. See Backend State/Type Constants.
typeThe 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.

proxy.queries

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, queries are passed on to the backend server verbatim. Also, if you do not populate the query queue by hand, the read_query_result() function is not triggered.

The following functions are supported for populating the proxy.queries object.

FunctionDescription
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. To have access to the result set through the read_query_result() function, 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:

The default mode is therefore quicker and useful if you only want to monitor the queries sent, and the basic statistics.

To perform any kind of manipulation on the returned data, you must set the flag to true, which will:

proxy.response

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) to return.

The following table describes the attributes of the proxy.response structure.

AttributeDescription
typeThe type of the response. The type must be either MYSQLD_PACKET_OK or MYSQLD_PACKET_ERR. If the MYSQLD_PACKET_ERR, you should set the value of the mysql.response.errmsg with a suitable error message.
errmsgA string containing the error message that will be returned to the client.
resultsetA 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 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 result set 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

proxy.response.resultset

The proxy.response.resultset structure should be populated with the rows and columns of data to return. The structure contains the information about the entire result set, with the individual elements of the data shown in the following table.

The following table describes the attributes of the proxy.response.resultset structure.

AttributeDescription
fieldsThe 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.
flagsA number of flags related to the result set. 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).
rowsThe 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_countThe number of warnings for this result set.
affected_rowsThe number of rows affected by the original statement.
insert_idThe last insert ID for an auto-incremented column in a table.
query_statusThe status of the query operation. You can use the MYSQLD_PACKET_OK or MYSQLD_PACKET_ERR constants to populate this parameter.

For an example showing how to use this structure, see proxy.response.

Proxy Return State Constants

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.

ConstantDescription
PROXY_SEND_QUERYCauses the proxy to send the current contents of the queries queue to the server.
PROXY_SEND_RESULTCauses the proxy to send a result set back to the client.
PROXY_IGNORE_RESULTCauses 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

Packet State Constants

The following states describe the status of a network packet. These items are entries within the main proxy table.

ConstantDescription
MYSQLD_PACKET_OKThe packet is OK
MYSQLD_PACKET_ERRThe packet contains error information
MYSQLD_PACKET_RAWThe packet contains raw data

Backend State/Type Constants

The following constants are used either to define the status or type of the backend MySQL server to which the proxy is connected. These items are entries within the main proxy table.

ConstantDescription
BACKEND_STATE_UNKNOWNThe current status is unknown
BACKEND_STATE_UPThe backend is known to be up (available)
BACKEND_STATE_DOWNThe backend is known to be down (unavailable)
BACKEND_TYPE_UNKNOWNBackend type is unknown
BACKEND_TYPE_RWBackend is available for read/write
BACKEND_TYPE_ROBackend is available only for read-only use

Server Command Constants

The values described in the table below 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.

ConstantDescription
COM_SLEEPSleep
COM_QUITQuit
COM_INIT_DBInitialize database
COM_QUERYQuery
COM_FIELD_LISTField List
COM_CREATE_DBCreate database
COM_DROP_DBDrop database
COM_REFRESHRefresh
COM_SHUTDOWNShutdown
COM_STATISTICSStatistics
COM_PROCESS_INFOProcess List
COM_CONNECTConnect
COM_PROCESS_KILLKill
COM_DEBUGDebug
COM_PINGPing
COM_TIMETime
COM_DELAYED_INSERTDelayed insert
COM_CHANGE_USERChange user
COM_BINLOG_DUMPBinlog dump
COM_TABLE_DUMPTable dump
COM_CONNECT_OUTConnect out
COM_REGISTER_SLAVERegister slave
COM_STMT_PREPAREPrepare server-side statement
COM_STMT_EXECUTEExecute server-side statement
COM_STMT_SEND_LONG_DATALong data
COM_STMT_CLOSEClose server-side statement
COM_STMT_RESETReset statement
COM_SET_OPTIONSet option
COM_STMT_FETCHFetch statement
COM_DAEMONDaemon (MySQL 5.1 only)
COM_ERRORError

MySQL Type Constants

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.

ConstantField Type
MYSQL_TYPE_DECIMALDecimal
MYSQL_TYPE_NEWDECIMALDecimal (MySQL 5.0 or later)
MYSQL_TYPE_TINYTiny
MYSQL_TYPE_SHORTShort
MYSQL_TYPE_LONGLong
MYSQL_TYPE_FLOATFloat
MYSQL_TYPE_DOUBLEDouble
MYSQL_TYPE_NULLNull
MYSQL_TYPE_TIMESTAMPTimestamp
MYSQL_TYPE_LONGLONGLong long
MYSQL_TYPE_INT24Integer
MYSQL_TYPE_DATEDate
MYSQL_TYPE_TIMETime
MYSQL_TYPE_DATETIMEDatetime
MYSQL_TYPE_YEARYear
MYSQL_TYPE_NEWDATEDate (MySQL 5.0 or later)
MYSQL_TYPE_ENUMEnumeration
MYSQL_TYPE_SETSet
MYSQL_TYPE_TINY_BLOBTiny Blob
MYSQL_TYPE_MEDIUM_BLOBMedium Blob
MYSQL_TYPE_LONG_BLOBLong Blob
MYSQL_TYPE_BLOBBlob
MYSQL_TYPE_VAR_STRINGVarstring
MYSQL_TYPE_STRINGString
MYSQL_TYPE_TINYTiny (compatible with MYSQL_TYPE_CHAR)
MYSQL_TYPE_ENUMEnumeration (compatible with MYSQL_TYPE_INTERVAL)
MYSQL_TYPE_GEOMETRYGeometry
MYSQL_TYPE_BITBit