One example given in the manual is a normal query sent by the user, with a
SHOW STATUSexecuted before and after the query to evaluate the efficiency of the query itself.
Another common possibility is to create a loop, i.e. the client sends a query with appropriate commands for the Proxy, and the query is executed N times.
Both cases are accomplished using the query queue, a data structure that the Proxy uses to process user defined queries.
The tricky part in multiple query execution is that the client has only sent one query, and thus it expects only one result. If we execute multiple queries, we will get multiple results from the server. Thus, we need to handle the results that we want to process within the Proxy and prevent them from reaching the client, while making sure that the client receives one and one only result.
- within read_query()
- insert the queries in the query queue;
- return an appropriate result (proxy.PROXY_SEND_QUERY), so the Proxy knows that it has to process the query queue
- within read_query_result()
- check the query ID
- based on the query ID, return the result to the client, or discard it
Some interesting points:
local max_loop = 0
function read_query (packet )
if packet:byte() ~= proxy.COM_QUERY then return end
local query = packet:sub(2)
local loop_no, repeat_query = query:match('^LOOP%s+(%d+)%s+(.+)')
if loop_no then
loop_no = loop_no + 0
if loop_no < 2 then return end
for x = 1, loop_no do
proxy.queries:append(x, string.char(proxy.COM_QUERY) .. repeat_query )
max_loop = loop_no
function read_query_result (inj )
-- do something with the result
if inj.id ~= max_loop then
- loop_no must be transformed to a number before using it;
- The query to be appended must be prefixed by proxy.COM_QUERY;
- if no explicit return is given on read_query(), the default behavior is to send the query from the client to the server without changes;
- if no explicit return is given on read_query_result(), the default behavior is to send the result from the server to the client without changes.
This post is part of a set of recipes that will eventually become a long article.
Want to hear more? Attend MySQL Proxy - The complete tutorial at the MySQL Users Conference 2008.