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:
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.
drop table if exists t1, t1f;
create server fed
foreign data wrapper mysql
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';
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)
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.
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['token_name'] == 'TK_INTEGER'
and tokens['text'] == 't1'
and tokens['text'] == '1' then
print( " --- " .. query )
os.execute('echo "executing ### ' .. tokens['text'] .. ' ###"')
Now, let's create and event and see what happens:
The result, as observed by the Proxy, is as follows:
set global event_scheduler=1;
create event e1
on schedule every 10 second
update t1f set cmd=concat('do this ', now())
where id =1;
--- 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 ###