Wednesday, April 02, 2008

MySQL Proxy Recipes - Conditional query execution

MySQL Proxy allows you to execute multiple queries, by inserting them into the query queue.
What happens if one of the queries in the pipeline generates an error?
If you don't handle this occurrence, the Proxy will continue sending to the server all the queries in the pipeline, regardless of the result. It's easy to understand that, if the second query depends on the execution of the first one, checking the result of each query is essential.
We know already how to return an error. We only need to apply that knowledge in the right place.
The read_query_result() from the previous recipe (executing multiple queries) should be modified as follows:
function read_query_result (inj )
local res = assert(inj.resultset)
if res.query_status and (res.query_status < 0 ) then
proxy.queries:reset()
return error_result('ERROR IN LOOP ' .. tostring(inj.id),
9999, 'XLOOP')
end
-- do something with the result
if inj.id ~= max_loop then
return proxy.PROXY_IGNORE_RESULT
end
end
The error status is inside the resultset, which is contained in the injection packet received by the function. If that status is negative, some error occurred.

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:

Kerala Traveller said...

I happend to drop in because I was interested in getting the total no of queries, total query time, and time distribution in percentage with respect to any web page instance, and along with all these, the details of where bottlenecks could happen. All of these along with the advantage of being able to use on shared hosting.. the result was a php class, http://www.php-trivandrum.org/open-php-myprofiler