[MySQL]: Event Scheduler
The MySQL Event Scheduler manages the scheduling and execution of events: Tasks that run according to schedule. Event support was added in MySQL 5.1.6.
MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler.
MySQL Events have the following major features and properties:
- In MySQL 5.1.12 and later, an event is uniquely identified by its name and the schema to which it is assigned.
- An event performs a specific action according to a schedule. This action consists of an SQL statement, which can be a compound statement in a BEGIN … END block if desired
- An event’s timing can be either one-time or recurrent. A one-time event executes one time only. A recurrent event repeats its action at a regular interval
Event Scheduler Configuration:
Events are executed by a special event scheduler thread; when we refer to the Event Scheduler, we actually refer to this thread. When running, the event scheduler thread and its current state can be seen by users having the PROCESS privilege in the output of SHOW PROCESSLIST.
The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server. Beginning with MySQL 5.1.12, it has one of these 3 values, which affect event scheduling as described here:
- OFF: The Event Scheduler is stopped. The event scheduler thread does not run, is not shown in the output of SHOW PROCESSLIST, and no scheduled events are executed. OFF is the default value for event_scheduler.
- ON: The Event Scheduler is started; the event scheduler thread runs and executes all scheduled events. When the Event Scheduler is ON, the event scheduler thread is listed in the output of SHOW PROCESSLIST as a daemon process.
- DISABLED: This value renders the Event Scheduler nonoperational. When the Event Scheduler is DISABLED, the event scheduler thread does not run (and so does not appear in the output of SHOW PROCESSLIST). In addition, the Event Scheduler state cannot be changed at runtime.
It is possible to set the Event Scheduler to DISABLED only at server startup. If event_scheduler is ON or OFF, you cannot set it to DISABLED at runtime. Also, if the Event Scheduler is set to DISABLED at startup, you cannot change the value of event_scheduler at runtime.
mysql > SET GLOBAL event_scheduler = ON;
To disable the event scheduler, use one of the following two methods:
- As a command-line option when starting the server:
–event-scheduler=DISABLED
- In the server configuration file (my.cnf, or my.ini on Windows systems), include the line where it will be read by the server
event_scheduler=DISABLED
Note: You can issue event-manipulation statements when event_scheduler is set to DISABLED. No warnings or errors are generated in such cases (provided that the statements are themselves valid). However, scheduled events cannot execute until this variable is set to ON (or 1). Once this has been done, the event scheduler thread executes all events whose scheduling conditions are satisfied.
Beginning with MySQL 5.1.17, starting the MySQL server with the –skip-grant-tables option causes event_scheduler to be set to DISABLED, overriding any other value set either on the command line or in the my.cnf or my.ini file (Bug#26807).
MySQL 5.1.6 and later provides an EVENTS table in the INFORMATION_SCHEMA database. This table can be queried to obtain information about scheduled events which have been defined on the server.
Creating Events:
CREATE EVENT <event_name>
ON SCHEDULE [AT] <schedule> [EVERY] <interval>
DO
<event_body>;
Example:
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
The previous statement creates an event named myevent. This event executes once—one hour following its creation—by running an SQL statement that increments the value of the myschema.mytable table’s mycol column by 1.
The ON SCHEDULE clause determines when, how often, and for how long the event_body defined for the event repeats. This clause takes one of two forms:
- AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp, which must include both the date and time, or must be an expression that resolves to a datetime value.
- To repeat actions at a regular interval, use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous discussion of the AT keyword. (+ INTERVAL is not used with EVERY.) For example, EVERY 6 WEEK means “every six weeks”.
CREATE EVENT e_hourly
ON SCHEDULE
EVERY 1 HOUR
COMMENT ‘Clears out sessions table each hour.’
DO
DELETE FROM site_activity.sessions;
More complex compound statements, such as those used in stored routines, are possible in an event.
delimiter |
CREATE EVENT e
ON SCHEDULE
EVERY 5 SECOND
DO
BEGIN
DECLARE v INTEGER;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t1 VALUES (0);
UPDATE t2 SET s1 = s1 + 1;
SET v = v + 1;
END WHILE;
END |
delimiter ;
There is no way to pass parameters directly to or from events; however, it is possible to invoke a stored routine with parameters within an event:
CREATE EVENT e_call_myproc
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO CALL myproc(5, 27);
Alter Event:
Examples:
ALTER EVENT myevent
ON SCHEDULE
EVERY 12 HOUR;
ALTER TABLE myevent
AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
TRUNCATE TABLE myschema.mytable;
To disable myevent, use this ALTER EVENT statement:
ALTER EVENT myevent DISABLE;
ALTER EVENT myevent RENAME TO yourevent;
You can also move an event to a different database using ALTER EVENT … RENAME TO … and db_name.event_name notation, as shown here:
ALTER EVENT olddb.myevent RENAME TO newdb.myevent;
Drop Event:
DROP EVENT [IF EXISTS] event_name;