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 PROCESSLISTG
...
     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:

DELIMITER |

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      BEGIN
        UPDATE mytable SET mycol = mycol + 1;
      END |

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 EVENTSG
********************** 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:

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    ON COMPLETION PRESERVE
    DO
      BEGIN
        UPDATE mytable SET mycol = mycol + 1;
      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:

DROP EVENT myevent;

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

CREATE EVENT myevent
    ON SCHEDULE EVERY 1 HOUR
    DO
      BEGIN
        UPDATE mytable SET mycol = mycol + 1;
      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:

CREATE EVENT myevent
    ON SCHEDULE EVERY 1 HOUR
    STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
    ENDS CURRENT_TIMESTAMP + INTERVAL 1 YEAR
    DO
      BEGIN
        UPDATE mytable SET mycol = mycol + 1;
      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, or SECOND. 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:

ALTER EVENT myevent
    ON SCHEDULE EVERY 1 MONTH
    STARTS '2011-12-01 01:00:00' |

To update the event with a different set of queries, you would use:

ALTER EVENT myevent
    DO
      BEGIN
        INSERT INTO mystats (total)
          SELECT COUNT(*) FROM sessions;
        TRUNCATE sessions;
      END |

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

ALTER EVENT myevent
    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:

<?php
// establish database connection and filter incoming data
// ...

// insert blog post with pending status, get id assigned to post
$query = "INSERT INTO blog_posts (id, title, post_text, status) 
    VALUES (NULL, :title, :postText, 'pending')";
$stm = $db->prepare($query);
$stm->execute(array(":title" => $title, ":postText" => $text));
$id = $db->lastInsertId();

// is this a future post?
if (isset($_POST["schedule"], $_POST["time"])) {
    $scheduleDate = strtotime($_POST["time"]);

    $query = "CREATE EVENT publish_:id
    ON SCHEDULE AT FROM_UNIXTIME(:scheduleDate)
    DO
      BEGIN
        UPDATE blog_posts SET status = 'published' WHERE id = :id;
      END";
    $stm = $db->prepare($query);
    $stm->execute(array(":id" => $id, ":scheduleDate" => $scheduleDate));
}
// this is not a future post, publish now
else {
    $query = "UPDATE blog_posts SET status = 'published' WHERE id = :id";
    $stm = $db->prepare($query);
    $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 EXISTS and re-add it with the new scheduled time.

Summary

You should now have a solid understanding of what MySQL events are, as well as how to create and manage events of your own. While events are not a replacement for cron jobs or scheduled tasks, as events cannot execute external code such as PHP scripts, they are a useful alternative for time-dependent tasks specific to the MySQL database. As always, if you’re interested in learning more, be sure to read the official documentation. Image via Garsya / Shutterstock

Frequently Asked Questions (FAQs) about Working with MySQL Events