2 Ways to Return Disabled Events in MySQL

There are several ways to list out all scheduled events in MySQL. But sometimes we might want to limit the results to just those that are set to a given status. Sometimes we might only want to see a list of disabled events.

Below are two options we can use to return all disabled events.

The `SHOW EVENTS` Statement

The SHOW EVENTS statement allows us to get a list of events that match a certain criteria. We can use it to return just those events that are in disabled status:

SHOW EVENTS WHERE status = 'DISABLED';

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 | Event1 | root@localhost | SYSTEM    | RECURRING | NULL                | 1              | MINUTE         | 2024-04-10 09:05:31 | NULL | DISABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| 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 |
+------+--------+----------------+-----------+-----------+---------------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+

By default SHOW EVENTS returns just those events that are in the current schema. We can use the FROM clause (or its synonym IN) to specify another schema:

SHOW EVENTS FROM krankykranes
WHERE status = 'DISABLED';

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 | check_products | root@localhost | SYSTEM    | RECURRING | NULL       | 1              | SECOND         | 2024-04-10 13:27:14 | 2024-04-10 13:27:24 | DISABLED |          1 | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+--------------+----------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+----------+------------+----------------------+----------------------+--------------------+

The SHOW EVENTS statement shows us a lot more than just the event name and its status. It shows us a whole lot of information that could be useful.

The `information_schema.events` View

We can use the information_schema.events view to do a similar thing to the SHOW EVENTS statement. We can also use it to return just the columns we’re interested in:

SELECT 
    event_schema,
    event_name,
    event_type,
    status
FROM information_schema.events
WHERE status = 'DISABLED'
ORDER BY event_schema, event_name;

Result:

+--------------+----------------+------------+----------+
| EVENT_SCHEMA | EVENT_NAME     | EVENT_TYPE | STATUS   |
+--------------+----------------+------------+----------+
| krankykranes | check_products | RECURRING  | DISABLED |
| test         | Event1         | RECURRING  | DISABLED |
| test         | Event5         | RECURRING  | DISABLED |
| test         | Event6         | RECURRING  | DISABLED |
| test         | Event7         | ONE TIME   | DISABLED |
+--------------+----------------+------------+----------+

That query returned all disabled events from all schemas. We can narrow the query down to just those schemas we’re interested in:

SELECT 
    event_schema,
    event_name,
    event_type,
    status
FROM information_schema.events
WHERE status = 'DISABLED'
AND event_schema = 'krankykranes'
ORDER BY event_schema, event_name;

Result:

+--------------+----------------+------------+----------+
| EVENT_SCHEMA | EVENT_NAME     | EVENT_TYPE | STATUS   |
+--------------+----------------+------------+----------+
| krankykranes | check_products | RECURRING  | DISABLED |
+--------------+----------------+------------+----------+

The SHOW EVENTS Statement

The information_schema.events View

The `SHOW EVENTS` Statement

The `information_schema.events` View