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!