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.

When the event_scheduler is enabled, the PROCESSLIST shows the event scheduler thread running as Daemon process waiting for the activation of the event

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.