From the release of MySQL 5.1.6 comes the inclusion of a scheduler. The scheduler, as the name suggests, schedules tasks within the database in the same way as triggers. In fact, the scheduler is just that, a temporal trigger. Many of the tasks assigned to the scheduler have been run from cron, but not everybody has access to cron or the windows task scheduler. The MySQL scheduler is not a replacement for these tools as some tasks with PHP scripts need to employ other, non database, events.
Getting Started
To schedule a task, three basic steps are employed.
Event Name
Event Interval
SQL Statement
CREATE EVENT
my_event
ON SCHEDULE
EVERY 1 WEEK
DO
INSERT INTO my_table VALUES (0, ‘foo’, ‘bar’);
The format is self explanatory in the above SQL statement. An event with the name my_event is created that is to run every week and will execute the query in the last line. The time of starting will begin immediately.
As task scheduling is not to be found in any SQL standards, MySQL has followed Oracle and for those familiar with the Oracle Job Scheduler, the syntax will look familiar.
Turn Scheduler On
The scheduler itself is a background process. Basically this means a separate thread runs constantly behind the scenes looking for events run. For this to happen, the MySQL GLOBAL variable event_scheduler must be set to ON. This is done with the following SQL statement.
SET GLOBAL event_scheduler = 1;
To turn it off, simply set the variable to zero (0). To check the status of the scheduler, use a simple SELECT
SELECT @@event_scheduler;
Schedule An Event
As seen earlier in this tutorial, an event is created using the CREATE EVENT syntax. The event name can be a string of up to 64 characters. The event schedule has several options available to define when an event is to be executed. The schedule can be a MYSQL TIMESTAMP or an INTERVAL. Most will be familiar with the MYSQL TIMESTAMP format as 2009-03-21 12:34:00. The interval can be any of the following.
YEAR
MONTH
WEEK
DAY
HOUR
MINUTE
SECOND
CREATE EVENT `My Nice Event`
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 WEEK
DO
TRUNCATE TABLE my_table;
The above schedule will truncate the table named my_table in exactly one week from now. Lets do the same thing on a given date and time.
CREATE EVENT My Specific Event
ON SCHEDULE AT TIMESTAMP ‘2009-04-20 13:30:00’
DO
TRUNCATE TABLE my_table;
In the above SQL schedule the table named my_table is truncated at 1:30pm on April 20 2009. With this kind of control it is easy to assign tasks to very specific times and dates.
Schedule Recurring Events
Of course, scheduling an event as above is quite a nice feature, but more often than not, events will need to scheduled on a regular basis, just as previously done with cron or the windows task scheduler. The keyword in scheduling an event is EVERY. The syntax remains mostly the same but with the slight change in the schedule.
CREATE EVENT My Regular Event
ON SCHEDULE EVERY 2 WEEK
DO
TRUNCATE TABLE my_table;
Now the event named My Regular Event will be run every two weeks starting now. This is very useful, but more control can be gained by specifying the time to start the event. eg: 2 days from now.
CREATE EVENT My Regular Event
ON SCHEDULE EVERY 2 WEEK
STARTS CURRENT_TIMESTAMP + INTERVAL 2 DAY
DO
TRUNCATE TABLE my_table;
With the starting specified, an event may be run at any time in the future. Once again, this is quite useful, but what about setting an END time for the event.
CREATE EVENT My Regular Event
ON SCHEDULE EVERY 2 WEEK
STARTS CURRENT_TIMESTAMP + INTERVAL 2 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 YEAR
DO
TRUNCATE TABLE my_table;
Now the scheduled event will run every two weeks, beginning in two days, and run for the next year.
ALTER An Event
Up to this point, events have been created with various time intervals and frequencies. To edit, or ALTER, an event the ALTER EVENT statement is used as follows.
ALTER EVENT My Regular Event
ON SCHEDULE EVERY 1 MONTH
DO
DELETE FROM my_table WHERE id > 100;
The above code will change the the event named My Regular Event and run the SQL statement supplied in the DO.
DROP An Event
To remove of DROP an event is similar to all MySQL DROP statements, in that the syntax follows the same.
DROP EVENT my_event;
Of course, if the event named “my_event” does not exist, MySQL will return an error. To avoid this, just as with other DROP statements, the use of IF EXISTS is recommended.
DROP EVENT IF EXISTS my_event;
The event scheduler also provides a method of removing events when the event is created. By use of the ON COMPLETION statement. This provides the option to PRESERVE or NOT PRESERVE the scheduled event.
CREATE EVENT My Regular Event
ON SCHEDULE EVERY 2 WEEK
STARTS CURRENT_TIMESTAMP + INTERVAL 2 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 1 YEAR
ON COMPLETION PRESERVE
DO
TRUNCATE TABLE my_table;
With the PRESERVE, or NOT PRESERVE in place, the scheduled event is kept in the MySQL events table, or removed forever. The default behavior is to NOT PRESERVE.
View Events
To view events from the events table is simply a matter of SELECTing them as from any MySQL table.
SELECT * FROM mysql.event\G