When we create a scheduled event in MySQL it’s enabled by default. However, we do have the option of creating it in disabled status. We can also go back later and change an event’s status from enabled to disabled.
Given this fact, we may sometimes find ourselves wondering whether an event is currently enabled or disabled.
Below are three ways to go about checking an event for its enabled/disabled status.
Check the Information Schema
One option is to check the information_schema.events view:
SELECT
status
FROM information_schema.events
WHERE event_schema = 'krankykranes'
AND event_name = 'check_products';Result:
+----------+
| STATUS |
+----------+
| DISABLED |
+----------+
The SHOW EVENTS Statement
Another way to do it is with the SHOW EVENTS statement:
SHOW EVENTS LIKE 'check_products';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 |
+--------------+----------------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+---------------------+----------+------------+----------------------+----------------------+--------------------+
We get a lot more information than just the event’s status, but we can still see its status under the Status column.
By default SHOW EVENTS returns only events in the current schema. We can use FROM or IN to specify another schema to return events from that schema:
SHOW EVENTS
FROM test
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 |
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
| 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 |
+------+--------+----------------+-----------+-----------+------------+----------------+----------------+---------------------+------+----------+------------+----------------------+----------------------+--------------------+
This event is also disabled.
The SHOW CREATE EVENT Statement
Another statement that will show us an event’s status is the SHOW CREATE EVENT statement:
SHOW CREATE EVENT Event1;Result:
+--------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Event | sql_mode | time_zone | Create Event | character_set_client | collation_connection | Database Collation |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Event1 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | SYSTEM | CREATE DEFINER=`root`@`localhost` EVENT `Event1` ON SCHEDULE EVERY 1 MINUTE STARTS '2024-04-10 09:05:31' ON COMPLETION NOT PRESERVE DISABLE DO INSERT INTO EventTest ( EventTimestamp, InsertedBy )
VALUES (NOW(), 'Event1') | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
This option is probably the inferior option if all you want to do is get the event’s status. But if you’re interested in other aspects of the event, then it could provide you with all info you need with one statement. We can see the DISABLE keyword in amongst the event’s definition, which means that the event will be created in disabled status if we were to use that code to create the event again. And of course, this also means that the event is currently disabled.