How to Prevent a MySQL Event from Disappearing Once it’s Completed

Posted on by Ian

By default, scheduled events in MySQL are dropped from the system once they expire. That means that if the event’s schedule has completed, then the event is dropped.

But we can override this behaviour with the ON COMPLETION clause. Specifically we can specify ON COMPLETION PRESERVE to keep the event in the system after it has expired. By default, events have ON COMPLETION NOT PRESERVE added to their definition, which means they are dropped as soon as they expire. Using ON COMPLETION PRESERVE changes this so that they aren’t dropped when they expire.

Example

The following event will not be dropped when it expires – it will remain in the system:

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

This event is scheduled to run once, ten seconds after it’s created. Once run, it will remain in the system. That’s because I specified ON COMPLETION PRESERVE.

Here’s what happens when we use SHOW EVENTS before the event runs:

SHOW EVENTS LIKE 'Event1';

Result:

+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| Db           | Name   | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status  | Originator | character_set_client | collation_connection | Database Collation |
+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+
| krankykranes | Event1 | root@localhost | SYSTEM    | ONE TIME | 2024-04-12 09:09:57 | NULL           | NULL           | NULL   | NULL | ENABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+---------+------------+----------------------+----------------------+--------------------+

We can see that the event is enabled (its status is ENABLED).

And here’s what happens when we do it after the event runs:

SHOW EVENTS LIKE 'Event1';

Result:

+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db           | Name   | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |
+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| krankykranes | Event1 | root@localhost | SYSTEM    | ONE TIME | 2024-04-12 09:09:57 | NULL           | NULL           | NULL   | NULL | DISABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+

We can see that it has been disabled (its status is DISABLED).

The Default

If I hadn’t specified ON COMPLETION PRESERVE for the above event, it would have been dropped from the system once the schedule had completed (i.e. after ten seconds).

Let’s create two more events that use the default setting of ON COMPLETION NOT PRESERVE:

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

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

Here Event2 doesn’t have an ON COMPLETION clause but on Event3 has ON COMPLETION NOT PRESERVE. Either way, both events use this setting because it’s the default setting.

And let’s use SHOW EVENTS before both events run:

SHOW EVENTS LIKE 'Event%';

Result:

+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db           | Name   | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |
+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| krankykranes | Event1 | root@localhost | SYSTEM    | ONE TIME | 2024-04-12 09:09:57 | NULL           | NULL           | NULL   | NULL | DISABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| krankykranes | Event2 | root@localhost | SYSTEM    | ONE TIME | 2024-04-12 09:17:57 | NULL           | NULL           | NULL   | NULL | ENABLED  |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| krankykranes | Event3 | root@localhost | SYSTEM    | ONE TIME | 2024-04-12 09:17:57 | NULL           | NULL           | NULL   | NULL | ENABLED  |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+

We can see that both events are enabled (their status is ENABLED).

And here’s what happens when we do it after the events run:

SHOW EVENTS LIKE 'Event%';

Result:

+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| Db           | Name   | Definer        | Time zone | Type     | Execute at          | Interval value | Interval field | Starts | Ends | Status   | Originator | character_set_client | collation_connection | Database Collation |
+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+
| krankykranes | Event1 | root@localhost | SYSTEM    | ONE TIME | 2024-04-12 09:09:57 | NULL           | NULL           | NULL   | NULL | DISABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+--------------+--------+----------------+-----------+----------+---------------------+----------------+----------------+--------+------+----------+------------+----------------------+----------------------+--------------------+

Both events have been dropped. The only event that remains is Event1, which is the one we specified ON COMPLETION PRESERVE for.