Monday, March 17, 2008

Using the event scheduler with OS commands

One of the major additions to MySQL 5.1 is the the event scheduler. It is an internal scheduler, which does not need any help from the operating system. As such, it works independently in every platform.
One drawback of this feature, though, is that it can't communicate with the operating system. i.e. the event scheduler can't read system files, can't send e-mail messages, store data into log files. It can only work within the database server. This is convenient for security, but it is quite limiting. Time for hacking!

In getting started with MySQL Proxy I showed an example of how to run shell commands from any MySQL client. Unfortunately, this method can't be used with the events, because events can't send queries to the outside. Or can they? Let's be creative, and combine Federated tables and MySQL Proxy:
drop table if exists t1, t1f;
create server fed
foreign data wrapper mysql
options (
host '127.0.0.1',
port 4040
database 'test',
user 'msandbox',
password 'msandbox'
);
create table t1 (
id int nt null, cmd varchar(250), primary key (id), key (cmd)
);
create table t1f (
id int not null, cmd varchar(250), primary key (id), key (cmd)
) engine=federated connection = 'fed/t1';
Table t1f is Federated, and it is accessed through the Proxy port (4040). Thus, every query directed to this table will be intercepted by MySQL Proxy, and then we can do what we want.
The Lua script associated with this Federated table is the following, which intercepts UPDATE statements directed to table t1, and sends the contents of the cmd column to an echo statement, using a OS call. (It's just a proof of concept)
function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then return end
local query = packet:sub(2)
if query:match('UPDATE') then
local tokens = proxy.tokenize(query)
if tokens[10]['token_name'] == 'TK_INTEGER'
and tokens[2]['text'] == 't1'
and tokens[10]['text'] == '1' then
print( " --- " .. query )
os.execute('echo "executing ### ' .. tokens[6]['text'] .. ' ###"')
end
end
end
In short, this script checks if we are updating table t1 (not t1f! The Federated engine accesses the base table) with id = 1, and if yes, uses the content of cmd as argument for an OS command.
Now, let's create and event and see what happens:
set global event_scheduler=1;
create event e1
on schedule every 10 second
do
update t1f set cmd=concat('do this ', now())
where id =1;
The result, as observed by the Proxy, is as follows:
--- UPDATE `t1` SET `cmd` = 'do this 2008-03-17 02:27:47' WHERE `id` = 1 LIMIT 1
executing ### do this 2008-03-17 02:27:47 ###
--- UPDATE `t1` SET `cmd` = 'do this 2008-03-17 02:27:57' WHERE `id` = 1 LIMIT 1
executing ### do this 2008-03-17 02:27:57 ###
QED!

4 comments:

Unknown said...

Hi!

I just use UDF's :)

-Brian

rpbouman said...

@Brian sure you can do anything you like with UDFs. But in many cases, OS-level things are just too dangerous as MySQL does not provide proper authorization. I wish we'd fix that it would make UDFs so much more useful...

Unknown said...

Most awesome thank you.

Anonymous said...

Though using the famd, and fam php api, I thought my method is more eaiser, but since a lot have commented on UDFs, I will be trying the UDF method on a dedicated server soon.
http://www.php-trivandrum.org/code-snippets/invoke-shell-from-mysql-trigger.html