Tuesday, April 01, 2008

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.

1 comment:

fmpwizard said...

Hi Giuseppe,
I hope they fix this bug

Incorrect nil value on read_query_result

before the conference.

Unless it only applies to SHOW STATUS statements.

Diego Medina

Vote on Planet MySQL