Understanding Scheduled Events in MySQL

MySQL provides us with the ability to create scheduled events. These are tasks that run according to a specified schedule.

Often referred to simply as events, scheduled events can be created with the CREATE EVENT statement, they can be modified with the ALTER EVENT statement, and they can be removed with the DROP EVENT statement.

Create an Event

As mentioned, we can create events with the CREATE EVENT statement. Here’s a basic example:

CREATE EVENT Event1
    ON SCHEDULE
        EVERY 1 MINUTE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event1');

That creates an event called Event1 that inserts a row in a table with the current date/time every minute. This assumes that the table exists and it has columns called EventTimestamp and InsertedBy.

Once we’ve run that statement, the event runs immediately. Then it runs every minute from then on. Whether it runs immediately will depend on the schedule though. For example, it won’t run immediately if we specify that it should only run at a specific time in the future.

If I select the contents of the table immediately, we can see that a row has already been inserted:

SELECT * FROM EventTest;

Result:

+---------------------+------------+
| EventTimestamp      | InsertedBy |
+---------------------+------------+
| 2024-03-31 09:00:59 | Event1     |
+---------------------+------------+

And if we wait a few minutes more and then select the contents again we can see more rows:

SELECT * FROM EventTest;

Result:

+---------------------+------------+
| EventTimestamp      | InsertedBy |
+---------------------+------------+
| 2024-03-31 09:00:59 | Event1     |
| 2024-03-31 09:02:00 | Event1     |
| 2024-03-31 09:02:59 | Event1     |
+---------------------+------------+

We can see that the event has updated the table every minute for the last three minutes as specified.

The `IF NOT EXISTS` Clause

We can use an IF NOT EXISTS clause to create the event only if it doesn’t exist. Or put another way, it won’t try to create the event if another event already exists with the same name.

CREATE EVENT IF NOT EXISTS Event1
    ON SCHEDULE
        EVERY 1 MINUTE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event1');

We can run that code multiple times without error. If the event doesn’t exist, it will be created. If it does exist, it won’t throw an error, but a warning will be issued.

Here’s what that might look like:

Query OK, 0 rows affected, 1 warning (0.00 sec)

We can view the warning like this:

SHOW WARNINGS;

Output:

+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1537 | Event 'Event1' already exists |
+-------+------+-------------------------------+

As suspected, the event already exists.

Modify an Event

We can modify an event with the ALTER EVENT statement. This allows us to modify the event without having to drop it and recreate it.

Let’s modify our event:

ALTER EVENT Event1
    ON SCHEDULE
        EVERY 1 HOUR
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event1');

Here, I changed the schedule of the event to run every hour instead of every minute.

Let’s check the contents of the table:

SELECT * FROM EventTest;

Result:

+---------------------+------------+
| EventTimestamp      | InsertedBy |
+---------------------+------------+
| 2024-03-31 09:00:59 | Event1     |
| 2024-03-31 09:02:00 | Event1     |
| 2024-03-31 09:02:59 | Event1     |
| 2024-03-31 09:03:29 | Event1     |
+---------------------+------------+

A row was inserted when I altered the event. The next row will be inserted an hour later (unless we change anything).

Dropping an Event

By default events are automatically dropped when they expire, but we can override this by specifying ON COMPLETION PRESERVE.

As for events that haven’t expired, we can remove existing events with the DROP EVENT statement:

DROP EVENT Event1;

Output:

Query OK, 0 rows affected (0.00 sec)

We can also include an IF EXISTS clause so that no errors are returned if the event doesn’t exist:

DROP EVENT IF EXISTS Event1;

Output:

Query OK, 0 rows affected, 1 warning (0.00 sec)

Although no errors are returned when it doesn’t exist, we do get a warning. We can use SHOW WARNINGS to check the warning:

SHOW WARNINGS;

Output:

+-------+------+-----------------------------+
| Level | Code | Message                     |
+-------+------+-----------------------------+
| Note  | 1305 | Event Event1 does not exist |
+-------+------+-----------------------------+

The warning simply informs us that the event didn’t exist when we tried to drop it.

Here’s what happens when we try to drop the non-existent event without using IF EXISTS:

DROP EVENT Event1;

Output:

ERROR 1539 (HY000): Unknown event 'Event1'

We get an error.

Event Scheduling

We have various options for scheduling each event. In the above example we scheduled it to run every hour, which was nice and simple, but we can also be more specific with the schedule.

The following syntax shows the options with regards to scheduling of events:

AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

Basically what this means is that we have the option of specifying a specific timestamp, a repeating interval, or a repeating interval within a given window of time.

The interval part can be any of the following:

YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND

The interval part is preceded by the quantity. For example INTERVAL 5 WEEK.

Run the Event Once in the Future

Here’s an example of creating an event that runs at a specified date/time:

CREATE EVENT Event2
    ON SCHEDULE
        AT '2024-03-31 09:07:51'
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event2');

Now let’s wait until that time and then query the table:

SELECT * FROM EventTest;

Result:

+---------------------+------------+
| EventTimestamp      | InsertedBy |
+---------------------+------------+
| 2024-03-31 09:00:59 | Event1     |
| 2024-03-31 09:02:00 | Event1     |
| 2024-03-31 09:02:59 | Event1     |
| 2024-03-31 09:03:29 | Event1     |
| 2024-03-31 09:07:51 | Event2     |
+---------------------+------------+

We can see that the event ran at the specified time and inserted a new row into the table.

Schedule an Event Relative to the Current Date/Time

We can add an interval to the current timestamp in order to make the event run at a certain interval from the current date/time.

Example:

CREATE EVENT Event3
    ON SCHEDULE
        AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event3');

Now let’s wait until that time and then query the table:

SELECT * FROM EventTest;

Result:

+---------------------+------------+
| EventTimestamp      | InsertedBy |
+---------------------+------------+
| 2024-03-31 09:00:59 | Event1     |
| 2024-03-31 09:02:00 | Event1     |
| 2024-03-31 09:02:59 | Event1     |
| 2024-03-31 09:03:29 | Event1     |
| 2024-03-31 09:07:51 | Event2     |
| 2024-03-31 09:13:45 | Event3     |
+---------------------+------------+

A new row was inserted as expected.

The `STARTS` and `ENDS` Clauses

The STARTS and ENDS keywords can be used with the EVERY clause in order to specify a time frame for which to start the event’s schedule.

Example:

CREATE EVENT Event4
    ON SCHEDULE
        EVERY 1 MINUTE
            STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
            ENDS CURRENT_TIMESTAMP + INTERVAL 3 MINUTE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event4');

Here I specified that the event should run every minute between a given window of time. The schedule starts one minute from the current time, and ends three minutes from the current time.

Now let’s wait until that time frame is finished and then query the table again:

SELECT * FROM EventTest;

Result:

+---------------------+------------+
| EventTimestamp      | InsertedBy |
+---------------------+------------+
| 2024-03-31 09:00:59 | Event1     |
| 2024-03-31 09:02:00 | Event1     |
| 2024-03-31 09:02:59 | Event1     |
| 2024-03-31 09:03:29 | Event1     |
| 2024-03-31 09:07:51 | Event2     |
| 2024-03-31 09:13:45 | Event3     |
| 2024-03-31 09:37:19 | Event4     |
| 2024-03-31 09:38:19 | Event4     |
| 2024-03-31 09:39:19 | Event4     |
+---------------------+------------+

We can see that the event has run three times as specified.

Disabling an Event

We can use the DISABLE keyword to disable an event. This allows the event to remain in the system, but prevents it from running, at least until it’s enabled.

Here’s an example of creating an event in disabled status:

CREATE EVENT Event5
    ON SCHEDULE
        EVERY 1 MINUTE
    DISABLE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event5');

So, although this event is set up to run every minute, it’s currently disabled and won’t run at all.

We can enable it with the ALTER EVENT statement:

ALTER EVENT Event5
    ENABLE;

In this case, enabling my event runs it immediately, and then it runs every minute thereafter. But this will depend on the event’s schedule.

We can use the ALTER EVENT statement to disable the event again:

ALTER EVENT Event5
    DISABLE;

Comments in Events

We can use the COMMENT clause to provide comments in the event:

ALTER EVENT Event6
    ON SCHEDULE
        EVERY 1 HOUR
    DISABLE
    COMMENT 'Insert timestamp into table'
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event6');

The comment is a string literal and must be surrounded in quotes. It can be up to 64 characters long.

Persisting Expired Events

As mentioned, by default events are automatically dropped once they’ve expired, but we can override this by specifying ON COMPLETION PRESERVE.

Example:

CREATE EVENT Event7
    ON SCHEDULE
        AT CURRENT_TIMESTAMP + INTERVAL 10 SECOND
    ON COMPLETION PRESERVE
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event7');

Now when this event has run, it won’t be dropped. It will remain in the system in disabled status.

Get a List of Events

We can use the SHOW EVENTS statement to return a list of events:

SHOW EVENTS;

Result:

+------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
| Db   | Name   | Definer        | Time zone | Type      | Execute at          | Interval value | Interval field | Starts              | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |
+------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
| test | Event5 | root@localhost | SYSTEM    | RECURRING | NULL                | 1              | MINUTE         | 2024-03-31 11:30:27 | NULL | DISABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| test | Event6 | root@localhost | SYSTEM    | RECURRING | NULL                | 1              | HOUR           | 2024-03-31 11:37:11 | NULL | DISABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| test | Event7 | root@localhost | SYSTEM    | ONE TIME  | 2024-03-31 11:45:36 | NULL           | NULL           | NULL                | NULL | ENABLED  |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+

I ran that SHOW EVENTS statement before Event7 ran, and so we can see that its status is ENABLED.

Let’s run it again, after Event7 has finished running:

SHOW EVENTS;

Result:

+------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
| Db   | Name   | Definer        | Time zone | Type      | Execute at          | Interval value | Interval field | Starts              | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |
+------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
| test | Event5 | root@localhost | SYSTEM    | RECURRING | NULL                | 1              | MINUTE         | 2024-03-31 11:30:27 | NULL | DISABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| test | Event6 | root@localhost | SYSTEM    | RECURRING | NULL                | 1              | HOUR           | 2024-03-31 11:37:11 | NULL | DISABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| test | Event7 | root@localhost | SYSTEM    | ONE TIME  | 2024-03-31 11:45:36 | NULL           | NULL           | NULL                | NULL | DISABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+

This time Event7 is in DISABLED status. This is the event that I applied ON COMPLETION PRESERVE to, so that it wouldn’t be dropped after it ran.

Actually, all events are currently disabled, due to the fact that I explicitly disabled the other two events.

Events and the `sql_mode` System Variable

MySQL stores the sql_mode system variable setting in effect when an event is created or altered, and always executes the event with this setting in effect, regardless of the current server SQL mode when the event begins executing.

So with this in mind, it should be safe to change the system’s sql_mode without it inadvertently affecting any existing events.

More Information

See the MySQL documentation for the CREATE EVENT statement for more information about creating events in MySQL. Also take a look at Event Scheduler Overview in the MySQL documentation.

Create an Event

The IF NOT EXISTS Clause

Modify an Event

Dropping an Event

Event Scheduling

Run the Event Once in the Future

Schedule an Event Relative to the Current Date/Time

The STARTS and ENDS Clauses

Disabling an Event

Comments in Events

Persisting Expired Events

Get a List of Events

Events and the sql_mode System Variable

More Information

The `IF NOT EXISTS` Clause

The `STARTS` and `ENDS` Clauses

Events and the `sql_mode` System Variable