How to Create Scheduled Events in MySQL

Craig Buckler
Craig Buckler
Share

This is the third and last article in a series about database automation with triggers and events. If you’ve not done so already, please read How to Create Triggers in MySQL which introduces many of the concepts discussed here. An event is similar to a trigger. However, rather than running in response to a data change, events can be scheduled to run any number of times during a specific period. In effect, it’s a database-only cron job. Events have been supported in MySQL since version 5.1. They are ideal for maintenance tasks such as data archiving or report generation which can be scheduled during off-peak times.

Our Database Plan

Our blog database has a problem. Old posts are marked as deleted rather than being removed from the `blog` table. Our table will grow indefinitely and become slower over time. We could purge the old posts but that would remove them forever. Therefore, we’ll move posts and their associated audit records to archive tables. The archive tables can grow without affecting the speed of the main web application and we can undelete old posts if necessary. Two archive tables are required:
  • `blog_archive`: identical to the `blog` table except it does not require a deleted flag or an auto-incrementing ID.
  • `audit_archive`: identical to the `audit` table except the timestamp is not automatically generated and it does not require an auto-incrementing ID.
The following SQL creates both tables:

CREATE TABLE `blog_archive` (
	`id` mediumint(8) unsigned NOT NULL,
	`title` text,
	`content` text,
	PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Blog posts archive';
-- 
CREATE TABLE `audit_archive` (
	`id` mediumint(8) unsigned NOT NULL,
	`blog_id` mediumint(8) unsigned NOT NULL,
	`changetype` enum('NEW','EDIT','DELETE') NOT NULL,
	`changetime` timestamp NOT NULL,
	PRIMARY KEY (`id`),
	KEY `ix_blog_id` (`blog_id`),
	KEY `ix_changetype` (`changetype`),
	KEY `ix_changetime` (`changetime`),
	CONSTRAINT `FK_audit_blog_archive_id` FOREIGN KEY (`blog_id`) REFERENCES `blog_archive` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Starting MySQL’s Event Scheduler

MySQL events are executed by a special event scheduler thread. It’s disabled by default so use the following MySQL command can determine whether it’s running:

SHOW PROCESSLIST;
If the scheduler is running, at least two rows will be shown and one will have its user field set to “event_scheduler”. If only one row is returned, the scheduler is disabled and events will not run. You can ensure the scheduler starts when MySQL is launched with the command-line option --event-scheduler=ON or setting event_scheduler=ON in your MySQL configuration file (my.cnf or my.ini on Windows). Alternatively, you can start the scheduler from the MySQL command line:

SET GLOBAL event_scheduler = ON;

Creating an Event

We require a scheduled event which:
  • Copies posts from `blog` to `blog_archive` when the deleted flag is set to 1.
  • Copies the associated audit entries for those posts from `audit` to `audit_archive`.
  • Physically deletes archived posts from the `blog` table. Referential integrity has been defined with a foreign key so all associated audit entries for those posts will also be removed.
Assuming you have MySQL rights to create events, the basic syntax is:

CREATE EVENT `event_name` 
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE] 
[ENABLE | DISABLE | DISABLE ON SLAVE]
DO BEGIN
	-- event body
END;
The schedule can be assigned various settings, e.g.
  • Run once on a specific date/time:
    AT ‘YYYY-MM-DD HH:MM.SS’
    e.g. AT ‘2011-06-01 02:00.00’
  • Run once after a specific period has elapsed:
    AT CURRENT_TIMESTAMP + INTERVAL n [HOUR|MONTH|WEEK|DAY|MINUTE]
    e.g. AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
  • Run at specific intervals forever:
    EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE]
    e.g. EVERY 1 DAY
  • Run at specific intervals during a specific period:
    EVERY n [HOUR|MONTH|WEEK|DAY|MINUTE] STARTS date ENDS date
    e.g. EVERY 1 DAY STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK ENDS ‘2012-01-01 00:00.00’
An event is normally dropped once its schedule has expired (ON COMPLETION NOT PRESERVE). Set ON COMPLETION PRESERVE to prevent that behavior. The MySQL CREATE EVENT Syntax documentation provides further details. We can now define our event (remembering to set the DELIMITER first). We’ll set it to run every week starting on a Sunday morning:

DELIMITER $$

CREATE 
	EVENT `archive_blogs` 
	ON SCHEDULE EVERY 1 WEEK STARTS '2011-07-24 03:00:00' 
	DO BEGIN
	
		-- copy deleted posts
		INSERT INTO blog_archive (id, title, content) 
		SELECT id, title, content
		FROM blog
		WHERE deleted = 1;
	    
		-- copy associated audit records
		INSERT INTO audit_archive (id, blog_id, changetype, changetime) 
		SELECT audit.id, audit.blog_id, audit.changetype, audit.changetime 
		FROM audit
		JOIN blog ON audit.blog_id = blog.id
		WHERE blog.deleted = 1;
		
		-- remove deleted blogs and audit entries
		DELETE FROM blog WHERE deleted = 1;
	    
	END */$$

DELIMITER ;
This is a simple example but you could add more functionality, e.g. only move posts which were deleted at least 1 month ago and purge all archieved posts over 1 year old. I hope you’ve enjoyed this series and are considering database triggers and events in your next project.

Frequently Asked Questions (FAQs) about Creating MySQL Events

What is the Syntax for Creating a MySQL Event?

The syntax for creating a MySQL event is as follows:

CREATE EVENT event_name
ON SCHEDULE schedule
DO
event_body;

In this syntax, event_name is the name of the event you want to create. schedule is the time when the event will be executed. It can be set to execute at a specific time or at regular intervals. event_body is the SQL statement to be executed when the event is triggered.

How Can I Enable the Event Scheduler in MySQL?

The event scheduler in MySQL is disabled by default. To enable it, you can use the following command:

SET GLOBAL event_scheduler = ON;

This command will enable the event scheduler globally. To check if the event scheduler is enabled, you can use the SHOW VARIABLES command.

How Can I Schedule a MySQL Event to Run at a Specific Time?

To schedule a MySQL event to run at a specific time, you can use the AT keyword in the ON SCHEDULE clause. Here is an example:

CREATE EVENT my_event
ON SCHEDULE AT '2022-12-31 23:59:59'
DO
UPDATE my_table SET my_column = 'New Year';

This event will update my_column in my_table to ‘New Year’ at the end of 2022.

How Can I Schedule a MySQL Event to Run at Regular Intervals?

To schedule a MySQL event to run at regular intervals, you can use the EVERY keyword in the ON SCHEDULE clause. Here is an example:

CREATE EVENT my_event
ON SCHEDULE EVERY 1 HOUR
DO
UPDATE my_table SET my_column = NOW();

This event will update my_column in my_table to the current time every hour.

How Can I View the Details of a MySQL Event?

To view the details of a MySQL event, you can use the SHOW CREATE EVENT command. Here is an example:

SHOW CREATE EVENT my_event;

This command will display the SQL statement that was used to create my_event.

How Can I Modify a MySQL Event?

To modify a MySQL event, you can use the ALTER EVENT command. Here is an example:

ALTER EVENT my_event
ON SCHEDULE AT '2023-01-01 00:00:00'
DO
UPDATE my_table SET my_column = 'Happy New Year';

This command will modify my_event to update my_column in my_table to ‘Happy New Year’ at the start of 2023.

How Can I Delete a MySQL Event?

To delete a MySQL event, you can use the DROP EVENT command. Here is an example:

DROP EVENT my_event;

This command will delete my_event.

How Can I Disable a MySQL Event Without Deleting It?

To disable a MySQL event without deleting it, you can use the ALTER EVENT command with the DISABLE keyword. Here is an example:

ALTER EVENT my_event
DISABLE;

This command will disable my_event.

How Can I Enable a Disabled MySQL Event?

To enable a disabled MySQL event, you can use the ALTER EVENT command with the ENABLE keyword. Here is an example:

ALTER EVENT my_event
ENABLE;

This command will enable my_event.

How Can I Check if a MySQL Event Has Been Executed?

To check if a MySQL event has been executed, you can query the information_schema.events table. Here is an example:

SELECT * FROM information_schema.events
WHERE event_name = 'my_event';

This command will display the details of my_event, including the time it was last executed.