Merge table management using MySQL event scheduler and Stored Procedures

Normally we use cronjobs to execute scheduled tasks, but some times because of security policies we don't have access to cronjobs, this is even worse in safe environments, in those cases, it is quite possible we do not have access to the file system and the only possible access is though MySQL.

So what can we do when we need to run scheduled tasks in MySQL?.

Fortunately, there is an event scheduler in MySQL; using this feature we can execute basically any MySQL scehduled job.

In this case it is required to create merge table based on pre-define schedule that can be minutes, hours, days, months or years. Yes!, I know that now would bemuch better to use partitions but in this case this is the requirement.

To reach the gaol the following stored procedures were implemented:

1.table_management_create_new_merge: Create the merge table based on the create table statement and a template
2.table_management_drop_merge: Deletes the merge table and all Underlying MyISAM tables
3.table_management_alter_merge: Changes the table and all merge MyISAM tables Underlying
4.table_management_add_new_table_to_merge: Adds a new table to merge
5.table_management_drop_table_from_merge: Take out a MyISAM table from the merge

This is a very basic merge table management system, what is more important in this case is to add and exclude the merge tables on cheduled basis, however, I decided to implement all the necessary procedures to make easier the administration of these Merge tables.

I want o emphasize two things that made this a little more complex than initially thought:


1. MySQL does not store any part of its metadata (information_schema) the relationship between merge MyISAM tables and tables that are part of this. Stores it in a file. MRG which I have no access!


2. I was not able by a cursor or variable assign the result of "SHOW CREATE TABLE" which was the second place where it could achieve the relationship between the merge table and MyISAM tables Underlying!. If someone knows please let me know.

Ok, there was no other choice but to implement a basic data structure what I will use record the relationship between the Merge table and its Underlying MyISAM tables.

Lets go back to goal: add / exclude table of a merge table on scheduled basis.

Here is the procedure:


1. Create the data structure that stores the relationship between the merge table and the underlying mysam tables:


create database table_management;

CREATE TABLE `dbschemas` (
`schema_id` int(11) NOT NULL AUTO_INCREMENT,
`schema_name` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`schema_id`),
UNIQUE KEY `schema_name_UNIQUE` (`schema_name`)
) ENGINE=InnoDB;


CREATE TABLE `dbtables` (
`table_id` int(11) NOT NULL AUTO_INCREMENT,
`schema_id` int(11) NOT NULL,
`table_name` varchar(64) DEFAULT NULL,
`parent_table_id` int(11) DEFAULT NULL,
PRIMARY KEY (`table_id`,`schema_id`),
UNIQUE KEY `schema_id_table_name_UNIQUE` (`schema_id`,`table_name`),
KEY `fk_table_schema1` (`schema_id`),
KEY `fk_table_table1` (`parent_table_id`),
CONSTRAINT `fk_table_schema1` FOREIGN KEY (`schema_id`) REFERENCES `dbschemas` (`schema_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_table_table1` FOREIGN KEY (`parent_table_id`) REFERENCES `dbtables` (`table_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB;


2. Create the required stored procedures:


##################Create new merge table and its template MyISAM TABLE#################

drop procedure table_management_create_new_merge;
delimiter //
#The parameter create_stmt is a regular table create statement, it does not include the union neither the engine.
create procedure table_management_create_new_merge (target_schema varchar(64),create_stmt varchar(10000))
begin
declare merge_table_name varchar(64);
declare column_def varchar(10000);
declare v_merge_table_id int;
declare v_schema_id int;

select trim(substring(trim(substring(create_stmt,locate('table',create_stmt)+5)),1,locate('(',trim(substring(create_stmt,locate('table',create_stmt)+5)))-1)) into @merge_table_name;
select substring(trim(substring(create_stmt,locate('table',create_stmt)+5)),locate('(',trim(substring(create_stmt,locate('table',create_stmt)+5)))) into @column_def;

set @create_myisam_template_table_stmt=concat('create table ',target_schema,'.',@merge_table_name,'_tmpt ',@column_def,' ENGINE=MyISAM');
prepare create_myisam_template_table_stmt from @create_myisam_template_table_stmt;
execute create_myisam_template_table_stmt;

set @create_merge_table_stmt=concat('create table ',target_schema,'.',@merge_table_name,' ',@column_def,' ENGINE=MRG_MyISAM UNION=(',target_schema,'.',@merge_table_name,'_tmpt)');
prepare create_merge_table_stmt from @create_merge_table_stmt;
execute create_merge_table_stmt;

start transaction;

insert ignore into table_management.dbschemas set schema_name=target_schema;
select schema_id into v_schema_id from table_management.dbschemas where schema_name=target_schema;
insert into table_management.dbtables set schema_id=v_schema_id,table_name=@merge_table_name;
select table_id into v_merge_table_id from table_management.dbtables where schema_id=v_schema_id and table_name=@merge_table_name;
insert into table_management.dbtables set schema_id=v_schema_id,table_name=concat(@merge_table_name,'_tmpt'),parent_table_id=v_merge_table_id;

commit;
deallocate prepare create_myisam_template_table_stmt;
deallocate prepare create_merge_table_stmt;
end//
delimiter ;

#################Drop a Merge table and all its underlying MyISAM tables #########################
drop procedure table_management_drop_merge;
delimiter //
create procedure table_management_drop_merge (target_schema varchar(64),target_merge_table varchar(64))
begin
declare done varchar(5) default 'START';
declare table_name varchar(64);
declare v_table_id int;
declare v_schema_id int;
declare table_list cursor for
select c.table_name
from table_management.dbschemas a join table_management.dbtables b on a.schema_id=b.schema_id join table_management.dbtables c on b.table_id=c.parent_table_id
where a.schema_name=target_schema and b.table_name=target_merge_table;
declare continue handler for not found set done = 'END';
open table_list;
while done<>'END' do
fetch table_list into table_name;
if done<>'END' then
set @drop_table_stmt=concat('drop table ',target_schema,'.',table_name);
prepare drop_table_stmt from @drop_table_stmt;
execute drop_table_stmt;
end if;
end while;
close table_list;
set @drop_merge_table_stmt=concat('drop table ',target_schema,'.',target_merge_table);
prepare drop_merge_table_stmt from @drop_merge_table_stmt;
execute drop_merge_table_stmt;
start transaction;
select c.table_id,c.schema_id
into v_table_id,v_schema_id
from table_management.dbschemas a join table_management.dbtables b on a.schema_id=b.schema_id join table_management.dbtables c on b.table_id=c.parent_table_id
where a.schema_name=target_schema and b.table_name=target_merge_table;
delete from table_management.dbtables where table_id=v_table_id and schema_id=v_schema_id;
select a.table_id,a.schema_id
into v_table_id,v_schema_id
from table_management.dbtables a join table_management.dbschemas b on a.schema_id=b.schema_id
where b.schema_name=target_schema and a.table_name=target_merge_table;
delete from table_management.dbtables where table_id=v_table_id and schema_id=v_schema_id;
commit;
deallocate prepare drop_merge_table_stmt;
deallocate prepare drop_table_stmt;
end//
delimiter ;

#################Alter a Merge table and all its underlying MyISAM tables #########################
drop procedure table_management_alter_merge;
delimiter //
create procedure table_management_alter_merge (target_schema varchar(64),alter_stmt varchar(10000))
begin

declare done varchar(5) default 'START';
declare table_name varchar(64);
declare merge_table_name varchar(64);
declare column_def varchar(10000);
declare table_list cursor for
select b.table_name
from table_management.dbschemas a join table_management.dbtables b on a.schema_id=b.schema_id join table_management.dbtables c on b.table_id=c.parent_table_id
where a.schema_name=target_schema and b.table_name=@merge_table_name
union
select c.table_name
from table_management.dbschemas a join table_management.dbtables b on a.schema_id=b.schema_id join table_management.dbtables c on b.table_id=c.parent_table_id
where a.schema_name=target_schema and b.table_name=@merge_table_name;
declare continue handler for not found set done = 'END';

select trim(substring(trim(substring(alter_stmt,locate('table',alter_stmt)+5)),1,locate(' ',trim(substring(alter_stmt,locate('table',alter_stmt)+5)))-1)) into @merge_table_name;

select substring(trim(substring(alter_stmt,locate('table',alter_stmt)+5)),locate(' ',trim(substring(alter_stmt,locate('table',alter_stmt)+5)))) into @column_def;

open table_list;
while done<>'END' do
fetch table_list into table_name;
if done<>'END' then
set @alter_table_stmt=concat('alter table ',target_schema,'.',table_name,' ',@column_def);
prepare alter_table_stmt from @alter_table_stmt;
execute alter_table_stmt;
end if;
end while;
close table_list;
deallocate prepare alter_table_stmt;
end//
delimiter ;

#################Create a new MyISAM table and add it to the a Merge table #########################

drop procedure table_management_add_new_table_to_merge;
delimiter //
create procedure table_management_add_new_table_to_merge (target_schema varchar(64),target_merge_table varchar(64),new_table varchar(64))
begin
declare i int default 0;
declare done varchar(5) default 'START';
declare table_name varchar(64);
declare table_union varchar(10000) default '';
declare table_list cursor for
select c.table_name
from table_management.dbschemas a join table_management.dbtables b on a.schema_id=b.schema_id join table_management.dbtables c on b.table_id=c.parent_table_id
where a.schema_name=target_schema and b.table_name=target_merge_table;
declare continue handler for not found set done = 'END';
open table_list;
while done<>'END' do
fetch table_list into table_name;
if done<>'END' then
if i=0 then
set table_union=concat(target_schema,'.',table_name);
else
set table_union=concat(table_union,',',target_schema,'.',table_name);
end if;
set i=i+1;
end if;
end while;
close table_list;
set @create_table_stmt=concat('create table ',target_schema,'.',new_table,' like ',target_schema,'.',target_merge_table,'_tmpt');
prepare create_table_stmt from @create_table_stmt;
execute create_table_stmt;
set @alter_table_stmt=concat('alter table ',target_schema,'.',target_merge_table,' UNION=(',table_union,',',target_schema,'.',new_table,')');
prepare alter_table_stmt from @alter_table_stmt;
execute alter_table_stmt;
set @insert_into_dbtables_stmt=concat('insert into table_management.dbtables (schema_id,table_name,parent_table_id) select a.schema_id,\'',new_table,'\',b.table_id from table_management.dbschemas a join table_management.dbtables b on a.schema_id=b.schema_id where a.schema_name=\'',target_schema,'\' and table_name=\'',target_merge_table,'\'');
prepare insert_into_dbtables_stmt from @insert_into_dbtables_stmt;
execute insert_into_dbtables_stmt;
deallocate prepare create_table_stmt;
deallocate prepare alter_table_stmt;
deallocate prepare insert_into_dbtables_stmt;
end//
delimiter ;


#################Take a MyISAM table out of ther Merge and alter the merge accordingly #########################
drop procedure table_management_drop_table_from_merge;
delimiter //
create procedure table_management_drop_table_from_merge (target_schema varchar(64),target_merge_table varchar(64),table_to_drop varchar(64))
begin
declare i int default 0;
declare v_schema_id int;
declare v_table_id int;
declare done varchar(5) default 'START';
declare table_name varchar(64);
declare table_union varchar(10000) default '';
declare table_list cursor for
select c.table_name
from table_management.dbschemas a join table_management.dbtables b on a.schema_id=b.schema_id join table_management.dbtables c on b.table_id=c.parent_table_id
where a.schema_name=target_schema and b.table_name=target_merge_table;
declare continue handler for not found set done = 'END';
open table_list;
while done<>'END' do
fetch table_list into table_name;
if done<>'END' then
if table_name<>table_to_drop then
if i=0 then
set table_union=concat(target_schema,'.',table_name);
else
set table_union=concat(table_union,',',target_schema,'.',table_name);
end if;
set i=i+1;
end if;
end if;
end while;
close table_list;
set @alter_table_stmt=concat('alter table ',target_schema,'.',target_merge_table,' UNION=(',table_union,')');
prepare alter_table_stmt from @alter_table_stmt;
execute alter_table_stmt;
start transaction;
delete table_management.dbtables
from
table_management.dbschemas join
table_management.dbtables on table_management.dbschemas.schema_id=table_management.dbtables.schema_id
where table_management.dbschemas.schema_name=target_schema and table_management.dbtables.table_name=table_to_drop;
commit;
deallocate prepare alter_table_stmt;
end//


3. Create the initial merge and template tables:

create database secure3;
table_management_create_new_merge call ('secure3', 'create table myminutetable (id int, timestamp datetime, primary key (id ))');


4. Create a test event. For this, the event scheduler must be enable. This is disabled by default.

To enable it, do:
SET GLOBAL event_scheduler = ON;
To make the change permanent, Add the following to the my.cnf mysqld section inside the
event_scheduler = ON

To check this do a "show running processlist\G" and you should see something like this:

Id: 1
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 5
State: Waiting for next activation
Info: NULL

Now you can create the test event, in this case I am creating an event that runs every minute and adds a new table to merge.

drop event myminutetable;
delimiter //

create event myminutetable
on schedule every 1 minute
do
begin
declare datetime varchar(14) default '';
declare table_name varchar(64) default '';
select date_format(now(),'%Y%m%d%k%i') into datetime;
set @table_name=concat('myminutetable_',datetime);
call table_management_add_new_table_to_merge ('secure3','myminutetable',@table_name);
end//
delimiter ;

Verify that it is creating tables every minute:

use secure3;
show tables;

Verify that table_management is being updated

use table_management;
select * from dbtables;

Future improvements:

1. Exception and error handling
2. Parameter validation