In MySQL, scheduled events are tasks that run according to a specified schedule. When we no longer require an event we can drop it so that it doesn’t take up unnecessary room in the system. We have a couple of ways of going about this.

Option 1: The DROP EVENT Statement

One way to drop an event is to use the DROP EVENT statement. This statement does exactly what its namesake implies – drops an event.

Here’s an example to demonstrate:

DROP EVENT Event1;

Output:

Query OK, 0 rows affected (0.00 sec)

That statement dropped an event called Event1.

The DROP EVENT statement accepts the IF EXISTS clause. This allows us to run the DROP EVENT statement without getting an error if the event doesn’t exist.

Example:

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.

Option 2: Let it Expire

By default, events are automatically dropped once they’ve expired (completed their schedule). Therefore, we can effectively drop an event by letting it expire. This assumes that the event has a defined completion date/time in its schedule.

Like this one:

CREATE EVENT Event1
    ON SCHEDULE
        EVERY 1 SECOND
            STARTS CURRENT_TIMESTAMP + INTERVAL 5 SECOND
            ENDS CURRENT_TIMESTAMP + INTERVAL 15 SECOND
    DO
        INSERT INTO EventTest ( EventTimestamp, InsertedBy ) 
            VALUES (NOW(), 'Event1');

That event starts 5 seconds after it’s created and ends 15 seconds after it’s created. When it ends, it will be automatically dropped. We know it will be automatically dropped because I haven’t specified ON COMPLETION PRESERVE, which would allow it to persist beyond the completion of its expiry.

Basically, whether or not an expired event is automatically dropped depends on whether it was created with ON COMPLETION PRESERVE or ON COMPLETION NOT PRESERVE. The default setting is ON COMPLETION NOT PRESERVE, which means that the event is dropped once it expires. So as long as we don’t specify ON COMPLETION PRESERVE, then the event should be dropped automatically upon expiry.