Schedule Tasks using Events on MySQL

In Unix and Linux systems we have this program called cron, which allows users to execute scripts or commands at a specified time. Such jobs could be to remove system cache, to run a script to create backup, or to connect to Internet and download mails at a pre-specified time. Such types of scheduled tasks were possible in database servers through triggers that were fired on certain events. This task scheduling with database servers was not time-based, but event-based. Now with MySQL 5.1.6 and later versions, MySQL has provided the support for scheduled tasks or jobs through the introduction of Events.

MySQL Event Scheduler
Event Scheduler in MySQL is similar to the Linux cron jobs and Windows Task Scheduler. It facilitates tasks to be executed at a predefined scheduled time. This is like telling the MySQL server to run a given command or a set of commands at specific time. These scheduled tasks called as Events can also be set to run at certain intervals of time rather than as a onetime task. For instance a task that is to be performed each morning or on a monthly-basis, an event can be created for such task and that would execute the task at the scheduled time ridding the DBA of MySQL server from repeating the same process each day.

Creating an Event is similar to creating a named database object containing SQL statements to be executed at one or more intervals, each beginning and ending at a specific date and time.
This feature of events in MySQL is mainly intended at database administrators, who can use it for scheduling jobs that would execute in the background as a one-time or a recurrent process at certain intervals.

The MySQL events are executed by a special event-scheduler thread. The event-scheduler thread and its current state can be visualized in the output of the SHOW PROCESSLIST command by the users who have their privilege set to 'super' or by the database administrators. Remember, before creating any event, the event- scheduler thread must be enabled. And for this the global variable event_scheduler can be used to enable or disable the event-scheduler thread. The thread can be enabled by the following command.

SET GLOBAL event_scheduler = ON;

When the server is running, the event-scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process. The default state of the event-scheduler thread is OFF.

To switch off the thread we can pass the value OFF for event_scheduler for the global variable.

Creating Events
Though for DBMS there isn't any set of official standard for events, the MySQL concept of creating events is based on the syntax of Sybase SQL Anywhere's CREATE EVENT syntax. MySQL uses the following syntax for creating events:

CREATE EVENT event_name
ON SCHEDULE schedule
PRESERVE >


DO sql_statement;

The 'event_name' must be a valid identifier of upto 64 characters. And since events are database objects which are stored within a database; the event names must be unique. The IF NOT EXISTS clause works same as it does in the CREATE TABLE statement, if an 'event_name' exists, no action will be taken. The ON SCHEDULE clause determines at what time and how often the 'sql _statement' defined for the event has to be executed. The 'schedule' can be a timestamp in the future, recurring interval or a combination of both timestamp and the interval. The two clauses it can accept are AT and EVERY.

AT timestamp is used for one-time events, which are executed once on a given date and time as specified.

Suppose we want to drop a table named 'test' from the database 'pcqtest' after two hours from now, we can create an event as follows:

CREATE EVENT deleteTable
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 HOUR
DO DROP TABLE pcqtest.test;

The event will be created and stored in the database 'mysql' under the table 'event'. The CURRENT_TIMESTAP will take the current time from the system and INTERVAL adds two hours of delay to that time. Similarly, a specific time can also be set for the event to be executed by the use of AT TIMESTAMP 'time' with the ON SCHEDULE clause. And as the event-scheduler thread is enabled, it will trigger up the event after two hours from the current time. The execution of the event would result in dropping of the table from the database.

On the other hand EVERY clause is used for tasks that are to be repeated at scheduled intervals. For instance if a job has to be executed after every 2 hours, the ON SCHEDULE clause for the event will be like: ON SCHEDULE EVERY 2 HOUR The interval that the AT and EVERY clause can accept is: YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. The EVERY clause also accepts two optional clauses: STARTS and ENDS. The STARTS clause defines at what date and time the repeating of the event process should begin. Similarly the ENDS clause defines at what date and time the event repetition should cease. For instance, if you want to insert current time into a table at an interval of 20 seconds for the next five hours, then you can do that using the following event.

CREATE EVENT addTimer
ON SCHEDULE EVERY 20 SECOND
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 5 HOUR
DO INSERT INTO pcqtest.timer (timer_id) VALUES (NOW());

The addTimer event, once created will start inserting current time into the timer_id column of the table at an interval of every 20 seconds and this repetition will go on until the next 5 hours, and then it will stop.

To alter a precreated event, the syntax for altering the event is as follows:

ALTER EVENT event_name
< ON SCHEDULE schedule >
< RENAME TO event_name2 >
< ON COMPLETION < NOT > PRESERVE >
< COMMENT 'comment' >
< ENABLED | DISABLED >
< DO sql_statement >

The ALTER EVENT statement clauses are same as that for the CREATE EVENT clauses. With RENAME TO clause, an existing event can be renamed. The clauses that you mention for altering will be the only ones that will be changed and the rest will remain intact as in the originally created event.

If you want to see all the events that are in the database server, you can run a select query on the event table of the mysql database of MySQL server. Moreover, the events that have occurred and are now not required can be dropped from the database by using the command mentioned below:

DROP EVENT 'event_name'

Thus, for tasks which includes removing sessions, cache etc. from the database internal tables, or generating monthly database reports during night when the data transaction stress on the database server is less; Event Scheduling is the key.