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.