Working with MySQL Events

Martin Psinas
Martin Psinas
Share

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

What is the purpose of MySQL events?

MySQL events are tasks that run according to a schedule. They are similar to the concept of cron jobs in Unix. MySQL events are useful for performing database maintenance tasks automatically at regular intervals. For example, you can use an event to automatically delete or archive records that are older than a certain date, or to gather and store database statistics at a specific time each day.

How do I create a MySQL event?

To create a MySQL event, you use the CREATE EVENT statement. This statement includes the name of the event, the schedule on which it runs, and the SQL statement that is executed when the event runs. Here is an example:

CREATE EVENT my_event
ON SCHEDULE EVERY 1 HOUR
DO
DELETE FROM my_table WHERE my_date < DATE_SUB(NOW(), INTERVAL 1 DAY);

This event, named my_event, runs every hour and deletes records from my_table that are more than one day old.

How do I modify an existing MySQL event?

To modify an existing MySQL event, you use the ALTER EVENT statement. This statement allows you to change the schedule or the SQL statement of the event. For example, to change the schedule of my_event to run every 30 minutes, you would use the following statement:

ALTER EVENT my_event
ON SCHEDULE EVERY 30 MINUTE;

How do I delete a MySQL event?

To delete a MySQL event, you use the DROP EVENT statement. For example, to delete my_event, you would use the following statement:

DROP EVENT my_event;

How do I view the details of a MySQL event?

To view the details of a MySQL event, you can query the EVENTS table in the information_schema database. This table contains information such as the name of the event, the database in which it is defined, the SQL statement it executes, and its schedule. Here is an example query:

SELECT * FROM information_schema.EVENTS WHERE EVENT_NAME = 'my_event';

How do I enable or disable the MySQL event scheduler?

The MySQL event scheduler is a process that runs in the background and executes events. To enable or disable it, you use the SET GLOBAL statement. For example, to enable the event scheduler, you would use the following statement:

SET GLOBAL event_scheduler = ON;

To disable the event scheduler, you would use the following statement:

SET GLOBAL event_scheduler = OFF;

Can I create a MySQL event that runs at a specific time each day?

Yes, you can create a MySQL event that runs at a specific time each day by using the EVERY DAY keyword and the STARTS clause in the CREATE EVENT statement. Here is an example:

CREATE EVENT my_event
ON SCHEDULE EVERY DAY STARTS '2022-01-01 00:00:00'
DO
DELETE FROM my_table WHERE my_date < DATE_SUB(NOW(), INTERVAL 1 DAY);

This event runs every day at midnight, starting from January 1, 2022.

Can I create a MySQL event that runs only once?

Yes, you can create a MySQL event that runs only once by using the AT keyword in the CREATE EVENT statement. Here is an example:

CREATE EVENT my_event
ON SCHEDULE AT '2022-01-01 00:00:00'
DO
DELETE FROM my_table WHERE my_date < DATE_SUB(NOW(), INTERVAL 1 DAY);

This event runs once at midnight on January 1, 2022.

Can I create a MySQL event that runs on specific days of the week?

MySQL does not directly support scheduling events on specific days of the week. However, you can achieve this by creating an event that runs every day and then using a conditional statement in the SQL statement of the event to check the day of the week. Here is an example:

CREATE EVENT my_event
ON SCHEDULE EVERY DAY
DO
BEGIN
IF DAYOFWEEK(NOW()) IN (2, 3, 4) THEN
DELETE FROM my_table WHERE my_date < DATE_SUB(NOW(), INTERVAL 1 DAY);
END IF;
END;

This event runs every day, but the DELETE statement is executed only on Monday, Tuesday, and Wednesday (where Sunday is day 1 and Saturday is day 7).

Can I create a MySQL event that runs on specific dates of the month?

MySQL does not directly support scheduling events on specific dates of the month. However, you can achieve this by creating an event that runs every day and then using a conditional statement in the SQL statement of the event to check the date of the month. Here is an example:

CREATE EVENT my_event
ON SCHEDULE EVERY DAY
DO
BEGIN
IF DAYOFMONTH(NOW()) IN (1, 15) THEN
DELETE FROM my_table WHERE my_date < DATE_SUB(NOW(), INTERVAL 1 DAY);
END IF;
END;

This event runs every day, but the DELETE statement is executed only on the 1st and 15th of the month.