Scheduled events, as the name suggests, are triggered at particular times. They provide a framework to perform one or more MySQL operations on a time-based schedule.
Schedule events in mysql
Schedule events in mysql 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”) .
Scheduled events, like triggers, are always associated with a particular table, and can be set to execute either once or repeatedly at predefined intervals. This can come in handy for tasks that need to take place periodically, such as log rotation, statistics generation, or counter updates. A MySQL event is a task that runs based on a predefined schedule therefore sometimes it is referred to as a scheduled event. MySQL event is also known as “temporal trigger” because it is triggered by time, not by table update like a trigger.
MySQL uses a special thread called event schedule thread to execute all scheduled events. You can see the status of event scheduler thread by executing the following command:
By default, the event scheduler thread is not enabled. To enable and start the event scheduler thread, you need to execute the following command:
SET GLOBAL event_scheduler = ON;
Now to see the status of event scheduler thread, you execute the SHOW PROCESSLIST command again.
To disable and stop the event the event scheduler thread, you execute the SET GLOBAL command with value of the event_scheduler is OFF:
SET GLOBAL event_scheduler = OFF;
A stored procedure is only executed when it is invoked directly; a trigger is executed when an event associated with a table such as insert, update, or delete event occurs, while an event can be executed at once or more regular intervals.
To define a scheduled event, MySQL offers the CREATE EVENT command. This command must be followed by the event name, the event schedule, an active/inactive flag, and the main body, which contains the SQL statements to be executed when the event fires. These components are illustrated in the previous example, which creates a scheduled event named paxarchive. The ON SCHEDULE EVERY 1 DAY clause in the event definition ensures that it is activated daily, while the STARTS clause specifies the event’s start date and time. The ENABLE keyword tells the system that this is an active event, while the DO clause contains the main body of the trigger; this can contain either a single MySQL statement or multiple MySQL statements enclosed within a BEGIN…END block.
CREATE EVENT [IF NOT EXIST] event_name ON SCHEDULE schedule DO event_body
- First, you specify the event name after the CREATE EVENT clause. The event name must be unique within a database schema.
- Second, you put a schedule after the ON SCHEDULE clause. If the event is a one-time event, you use the syntax: AT timestamp [+ INTERVAL]. If the event is a recurring event, you use the EVERY clause: EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL];
- Third, you place the MySQL statements after the DO keyword. It is important to notice that you can call a stored procedure inside the body of the event. In case you have compound MySQL statements, you can wrap them in a BEGIN … END block
To create a scheduled event, a user must have the EVENT privilege. To turn the global event scheduler on or off, a user must have the SUPER privilege.
To modify a scheduled event, use the ALTER EVENT command and provide new parameters for the event.
ALTER EVENT pax_day ON SCHEDULE EVERY 2 HOUR STARTS '2009-01-14 22:45:00' ENABLE DO BEGIN INSERT INTO paxarchive SELECT * FROM pax WHERE FlightDate <= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY); DELETE FROM pax WHERE FlightDate <= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY); END
To prevent an event from being automatically removed from the event queue once it is completed (for audit or other reasons), attach an ON COMPLETION PRESERVE clause to the CREATE EVENT command.
The CREATE EVENT command supports a DEFINER clause, which specifies the user account whose privileges should be considered when executing the event code. For the event to execute successfully, this user should have all the privileges necessary to perform the statements listed in the event body. By default, MySQL sets the DEFINER value to the user who created the trigger.
CREATE DEFINER = 'firstname.lastname@example.org' EVENT pax_day ON SCHEDULE EVERY 1 DAY STARTS '2009-01-14 22:45:00' ENABLE DO BEGIN INSERT INTO paxarchive SELECT * FROM pax WHERE FlightDate <= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY); DELETE FROM pax WHERE FlightDate <= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY); END
You can configure the event to fire only within a certain time period by specifying optional STARTS and ENDS clauses, which contain the starting and ending times for the event.
CREATE EVENT util_hour ON SCHEDULE EVERY 1 HOUR STARTS '2009-04-01 00:00:01' ENDS '2009-04-30 23:59:01' ENABLE DO BEGIN DECLARE fid INT; DECLARE fdate DATE; DECLARE str TEXT DEFAULT ''; DECLARE util FLOAT; DECLARE c CURSOR FOR SELECT s.FlightID, s.FlightDate, 1-(SUM(s.CurrSeats) / (SELECT SUM(fc.MaxSeats) FROM flightclass AS fc WHERE fc.FlightID = s.FlightID GROUP BY FlightID)) AS u FROM stats AS s GROUP BY s.FlightID, s.FlightDate HAVING u > 0.80; OPEN c; l: LOOP FETCH c INTO fid,fdate,util; SET str = CONCAT('Flight # ', fid, ' on ', fdate, ": ", ROUND(util*100), '%'); INSERT INTO log (ByUser, Note, EventTime) VALUES (CURRENT_USER(), str, NOW()); END LOOP l; CLOSE c; END