Wednesday, April 09, 2008

MySQL Proxy Recipes - Sharing info among sessions

In MySQL Proxy scripts you can define variables and modify them. No surprise here. If you define a variable with script wide scope, you can use such variable and modify it between queries. For example:
local how_many_queries = 0
function read_query(packet)
how_many_queries=how_many_queries + 1
print (how_many_queries)
end
However, when you have two clients connected to the same Proxy instance, each one will have a separated set of local variables. By default, MySQL Proxy protects the variables inside a session. If two clients connect to the Proxy running the above script, there will be a distinct count for each session, not the total of queries, as intended.
To share information among sessions, you must use a dedicated table inside the Proxy, called, aptly enough, proxy.global
proxy.global.how_many_queries = proxy.global.how_many_queries or 0

function read_query(packet)
proxy.global.how_many_queries = proxy.global.how_many_queries + 1
print (proxy.global.how_many_queries)
end
Notice the idiom used to assign the initial value. If the global variable has not been assigned yet, it will be nil by checking for a true value, we make sure that we only initialize the variable if it is being accessed for the first time.

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.

No comments: