Monday, November 19, 2007

Multiple scripts in MySQL Proxy

MySQL Proxy is being beefed up, to make it ready for prime time. New features are being added, and old ones are improved and tested.
Testing the Proxy is an adventure in its own right. It's a tool that was designed to get in the middle of the scene and change things. Testing its features is more challenging than testing a straightforward application, even more complex like a database server. So the test suite for MySQL Proxy is much more complex and feature rich than the corresponding suite for the database server.
While researching to create the next edition of the test suite, able to cope with more features and corresponding testing requests, I developed a Lua script that enhances the current Proxy features and allows you to load several scripts, and use them all at once.
The script (load-multi.lua) is available in the latest Subversion tree. The usage is not difficult.
From any client, you need to send a query
PLOAD script_name;
The script you indicate will then be available to all the clients.
If your script contains functions that are used at session start (connect_server, read_handshake, read_auth, read_auth_result), they will be available when the next client connects. If your script uses read_query and read_query_result, they will be available immediately.
The mechanics of how this works is simple. You load one or more script containing one or more of the above mentioned functions that hook to the Proxy, and the load-multi module will stack each function in a list. For each hook, the load-multi module loops through the loaded functions, executes each one and tests the result. If the function returns a non null value, then that value is passed back to the Proxy.
load-multi
This means that if you have two scripts that can handle a particular query, only the first one that has been loaded will get a chance to evaluate the query.
When loading modules, you must check the order in which you are loading them. If you load first a script that handles every query, such as a logging application, subsequent scripts would be just filling memory.
There are more goodies.
Your scripts can use some global functions that load-multi prepares for you. Each of these routines is in the proxy.global namespace, so they are available to all clients and their loaded scripts.
One first useful application that can be plugged to this module is a script that lists the status of loaded functions:
-- show_handlers.lua
function read_query (packet)
if packet:byte() ~= proxy.COM_QUERY then
return
end
local query = packet:sub(2)
if query:match('select pload status') then
local header = { 'module', 'handler' }
local rows = {}
for id, lmodule in pairs(proxy.global.handler_status) do
for i,h in pairs(lmodule) do
table.insert(rows, { id, h.func } )
end
end
return proxy.global.make_dataset(header,rows)
end
end
Load this script with PLOAD show_handlers, and any client will be able to get a list of modules, with the function that each one introduced.
pload show_handlers;
+-------------------------------+
| info |
+-------------------------------+
| module "show_handlers" loaded |
+-------------------------------+
1 row in set (0.01 sec)

pload auth1;
+-----------------------+
| info |
+-----------------------+
| module "auth1" loaded |
+-----------------------+
1 row in set (0.03 sec)

select pload status;
+---------------+-------------------+
| module | handler |
+---------------+-------------------+
| show_handlers | read_query |
| auth1 | read_auth |
| auth1 | disconnect_client |
| auth1 | read_handshake |
| auth1 | connect_server |
| auth1 | read_auth_result |
+---------------+-------------------+
6 rows in set (0.01 sec)

This new module has the ability of extending its own behavior. If you like playing with new features, you should try this one!

4 comments:

Sheeri K. said...

Hrm....this seems useful because I've been wanting to be able to use more than one feature, but not have one huge monolithic script.

ie, I might want to filter out a query, but also log all incoming queries, so that the filtered query gets filtered and logged. It sounds like it would be difficult to do more than one action with one query unless I actually have a monolithic script.

Or did I miss something? This definitely does help if I want to add features like crosstab and other development tricks, but less useful for administrative tricks in the aggregate.

gmax said...

If your scripts do different things, you can combine them with this utility.
In your example, if you want to filter and log, you can load first the filtering script and then the logging one.
If you load the logging one first, and that script is logging queries and results like this snippet then the following scripts don't get a chance to be executed.
The same disappointment can happen if both scripts handle the same event. For example if you have two scripts that react to the same query ("SELECT MAGIC") and perform two different actions, the first one is always executed, while the second one gathers dust.
If this is your problem, you can't solve it with this tool, and you need to use chained proxies. Will talk about this in another article.

Sheeri K. Cabral said...

Chaining proxies....brilliant!

Thanx for clarifying, now I know when to use chained proxies vs. when I can use multiple scripts.

Noodler said...

I'm a coder, but a lua newb, so before I fire up an editor for what looks like a reasonably easy task ... Is there any reason why this technique can't be used to chain modules (rather than incurring the chained proxy costs) using something like

proxy.global.handled_functions = {
rq = {}, ...

by accumulating read_query, etc. functions and then executing them in the order in which they were specified?

With careful rewriting of modules, global namespace issues could be avoided.

I have visions of runtime tricks including using PLOAD like

PLOAD module-name;
PLOAD module-name AFTER prior-module-name;
PLOAD module-name FIRST;
PUNLOAD module-name;
PUNLOAD ALL;

Vote on Planet MySQL