Working with MySQL Events

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: NULL

Working 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:

01	DELIMITER |

02

03	CREATE EVENT myevent

04	ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

05

DO

06

BEGIN

07	UPDATE mytable SET mycol = mycol + 1;

08

END |

09

10	DELIMITER ;

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_ci

After an event has expired it will be automatically deleted unless you explicitly stated otherwise with an ON COMPLETION clause, for example:

1	CREATE EVENT myevent

2	ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR

3	ON COMPLETION PRESERVE

4

DO

5

BEGIN

6	UPDATE mytable SET mycol = mycol + 1;

7

END |

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:

1	DROP EVENT myevent;

To specify a recurring event, you would use the EVERY clause:

1	CREATE EVENT myevent

2	ON SCHEDULE EVERY 1 HOUR

3

DO

4

BEGIN

5	UPDATE mytable SET mycol = mycol + 1;

6

END |

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:

1	CREATE EVENT myevent

2	ON SCHEDULE EVERY 1 HOUR

3	STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY

4	ENDS CURRENT_TIMESTAMP + INTERVAL 1 YEAR

5

DO

6

BEGIN

7	UPDATE mytable SET mycol = mycol + 1;

8

END |

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:

1	ALTER EVENT myevent

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:

1	ALTER EVENT myevent

2

DO

3

BEGIN

4	INSERT INTO mystats (total)

5	SELECT COUNT(*) FROM sessions;

6	TRUNCATE sessions;

7

END |

To rename an event, you would specify a RENAME clause:

1	ALTER EVENT myevent

2	RENAME TO yourevent;

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:

01

<?php

02	// establish database connection and filter incoming data

03

// ...

04

05	// insert blog post with pending status, get id assigned to post

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();

11

12	// is this a future post?

13	if (isset($_POST["schedule"], $_POST["time"])) {

14	$scheduleDate = strtotime($_POST["time"]);

15

16	$query = "CREATE EVENT publish_:id

17	ON SCHEDULE AT FROM_UNIXTIME(:scheduleDate)

18

DO

19

BEGIN

20	UPDATE blog_posts SET status = 'published' WHERE id = :id;

21

END";

22	$stm = $db->prepare($query);

23	$stm->execute(array(":id" => $id, ":scheduleDate" =>$scheduleDate));

24

}

25	// this is not a future post, publish now

26

else {

27	$query = "UPDATE blog_posts SET status = 'published' WHERE id = :id";

28	$stm = $db->prepare($query);

29	$stm->execute(array(":id" => $id));

30

}

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.