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:

SHOW PROCESSLIST;

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.

SHOW PROCESSLIST;

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 BEGINEND block.

CREATE EVENT [IF NOT EXIST]  event_name
ON SCHEDULE schedule
DO
event_body
  1. First, you specify the event name after the CREATE EVENT clause. The event name must be unique within a database schema.
  2. 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];
  3. 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 = 'name@example.com'
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
JSC0d3's Logo
About JSC0d3

JSC0d3 is an entrepreneur, online marketer, and an employee of an IT company. When not building websites, creating content, or helping customers improve their online business, spend time with their wife and two beautiful children. Although he still feels new in WordPress, he enjoys sharing what he has learned with all of you! If you want to get in touch with him, you can do this through this website.

On the same idea

Posted by | July 9, 2014

A trigger, as the name suggests, refers to one or more SQL statements that are automatically executed (“triggered”) by the database server when a...

Posted by | June 16, 2014

From the release of MySQL 516 comes the inclusion of a scheduler The scheduler, as the name suggests, schedules tasks within the database in the same...

Previous PostBackNext Post

Leave here an impression