MySQL Proxy Recipes - Executing multiple queries

One of the goodies of MySQL Proxy is the execution of multiple queries in response of a single query from the client.
One example given in the manual is a normal query sent by the user, with a SHOW STATUS executed 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.

1. within read_query()
1. insert the queries in the query queue;
2. return an appropriate result (proxy.PROXY_SEND_QUERY), so the Proxy knows that it has to process the query queue
2. within read_query_result()
1. check the query ID
2. based on the query ID, return the result to the client, or discard it

For example, to create a simple loop, where each query starting with the "LOOP N" prefix will be executed N times, we can do the following:

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 )
     end
     max_loop = loop_no
     return proxy.PROXY_SEND_QUERY
 end
 return
end

function read_query_result (inj )
 -- do something with the result
 if inj.id ~= max_loop then
     return proxy.PROXY_IGNORE_RESULT
 end
end

Some interesting points:

• 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 recipe is quite complex and it exposes the developers to many pitfalls. Most of the nuances will be discussed in the incoming Users Conference tutorial.

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.