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.
Direct Hit! |
Applies To: DBAs USP: Apply events based on time to execute tasks Primary Link: dev.mysql.com/downloads/mysql/5.1.html Keywords: MySQL On DVD: PCQ_Professional\Labs |
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
ON SCHEDULE schedule
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.