MySQL events were added in MySQL 5.1.6 and offer an alternative to scheduled tasks and cron jobs. Events can be used to create backups, delete stale records, aggregate data for reports, and so on. Unlike standard triggers which execute given a certain condition, an event is an object that is triggered by the passage of time and is sometimes referred to as a temporal trigger. You can schedule events to run either once or at a recurring interval when you know your server traffic will be low.
In this article I’ll explain what you need to know to get started using events: starting the event scheduler, adding events to run once or multiple times, viewing existing events, and altering events. I’ll also share with how you might use MySQL events using scheduled blog posts as a practical example.
Starting the Event Scheduler
The MySQL event scheduler is a process that runs in the background and constantly looks for events to execute. Before you can create or schedule an event, you need to first turn on the scheduler, which is done by issuing the following command:
mysql> SET GLOBAL event_scheduler = ON;
Likewise, to turn all events off you would use:
mysql> SET GLOBAL event_scheduler = OFF;
Once the event scheduler is started, you can view its status in MySQL’s process list.
mysql> SHOW PROCESSLIST\G
...
Id: 79
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 12
State: Waiting on empty queue
Info: NULLWorking with Events
It’s important to note that when an event is created it can only perform actions for which the MySQL user that created the event has privileges to perform. Some additional restrictions include:
- Event names are restricted to a length of 64 characters.
- As of MySQL 5.1.8, event names are not case-sensitive; each event name should be unique regardless of case.
- Events cannot be created, altered, or dropped by another event.
You cannot reference a stored function or user-defined function when setting the event schedule.
Creating Events
The following example creates an event:
04 | ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR |
07 | UPDATE mytable SET mycol = mycol + 1; |
This event will run once, one hour from the time it was created. The BEGIN and END statements surround one or multiple queries which will be executed at the specified time. Because the semicolon is needed to terminate the UPDATE statement, you’ll need to switch delimiters before you issue the CREATE EVENT statement and then switch back afterwards if you’re working through a client.
You can view a list of all existing events with SHOW EVENTS.
mysql> SHOW EVENTS\G
********************** 1. row **********************
Db: mysql
Name: myevent
Definer: dbuser@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute At: 2011-10-26 20:24:19
Interval Value: NULL
Interval Field: NULL
Starts: NULL
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ciAfter an event has expired it will be automatically deleted unless you explicitly stated otherwise with an ON COMPLETION clause, for example:
2 | ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR |
6 | UPDATE mytable SET mycol = mycol + 1; |
In this example, even though the event has expired it will still be retained in the database which will allow you to alter and run it again later, or perhaps you’d just like to keep it for reference.
To permanently delete an event yourself, you can use DROP EVENT:
To specify a recurring event, you would use the EVERY clause:
2 | ON SCHEDULE EVERY 1 HOUR |
5 | UPDATE mytable SET mycol = mycol + 1; |
And rather than having an event that just runs once or forever, you can also schedule a reoccurring event that is valid only within a specific time period, using START and END clauses:
2 | ON SCHEDULE EVERY 1 HOUR |
3 | STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY |
4 | ENDS CURRENT_TIMESTAMP + INTERVAL 1 YEAR |
7 | UPDATE mytable SET mycol = mycol + 1; |
In this example, the reoccurring event would start tomorrow and continue to run every hour for a full year.
With regard to timing, the interval specified can be YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, orSECOND. Keep in mind that keywords are given as singular forms; writing something like INTERVAL 5 MINUTE may seem awkward to you, but it is perfectly correct to MySQL.
Updating Events
If you want to change an existing event’s behavior rather than deleting it and recreating it, you can use ALTER EVENT. For example, to change the schedule of the previous event to run every month, starting at some date in the future at 1 o’clock in the morning, you would use the following:
2 | ON SCHEDULE EVERY 1 MONTH |
3 | STARTS '2011-12-01 01:00:00' | |
To update the event with a different set of queries, you would use:
4 | INSERT INTO mystats (total) |
5 | SELECT COUNT(*) FROM sessions; |
To rename an event, you would specify a RENAME clause:
Blog Post Scheduling
So that I can show you a practical example, let’s say you
have a blog and you want the option to schedule posts to be published at some time in the future. One way to achieve this is to add a timestamp and published flag to the database records. A cron script would execute once every minute to check the timestamps and flip the flag for any posts that should be published. But this doesn’t seem very efficient. Another way to achieve this is by using MySQL events that will fire when you want publish the post.
Your blog entry form might have a checkbox that, when checked, indicates this is a scheduled post. Additionally, the form would have input fields for you to enter the date and time of when the post should be published. The receiving script would be responsible for adding the blog entry to the database and managing the events to schedule it if it’s not an immediate post. The relevant code looks like the following:
06 | $query = "INSERT INTO blog_posts (id, title, post_text, status) |
07 | VALUES (NULL, :title, :postText, 'pending')"; |
08 | $stm = $db->prepare($query); |
09 | $stm->execute(array(":title" => $title, ":postText" => $text)); |
10 | $id = $db->lastInsertId(); |
13 | if (isset($_POST["schedule"], $_POST["time"])) { |
14 | $scheduleDate = strtotime($_POST["time"]); |
16 | $query = "CREATE EVENT publish_:id |
17 | ON SCHEDULE AT FROM_UNIXTIME(:scheduleDate) |
20 | UPDATE blog_posts SET status = 'published' WHERE id = :id; |
22 | $stm = $db->prepare($query); |
23 | $stm->execute(array(":id" => $id, ":scheduleDate" =>$scheduleDate)); |
27 | $query = "UPDATE blog_posts SET status = 'published' WHERE id = :id"; |
28 | $stm = $db->prepare($query); |
29 | $stm->execute(array(":id" => $id)); |
When the post is stored in the database it is saved with a pending status. This gives you the chance to schedule the event if it’s a scheduled post, otherwise the status can be immediately updated to published.
If you were to edit the post at a later time, you can delete the event with DROP EVENT IF EXISTSand re-add it with the new scheduled time.
