In this tutorial, we will learn to modify the existing MySQL scheduled events. Along with it, we will see how to enable and disable the events, how to rename an event, and how to move the events from one database to another. Let’s begin!
Also read: Creating MySQL Scheduled Events – A Quick Reference
Introduction and Prerequisites
In this tutorial, we will directly dive into modifying the events instead of starting from scratch. We assume you possess the information about creating the events in MySQL. You can read our guide on working with MySQL scheduled events(link to the previously published article on creating MySQL events) if you want to learn more about this topic.
In MySQL, you can modify existing events using the ALTER EVENT statement. Using the ALTER EVENT statement, you can change the statements of the event body, rename the event and migrate the event from one database to another. We will see examples of each case stated above.
Syntax of MySQL ALTER EVENT
To modify existing events, we use the ALTER EVENT statement. Note that, you cannot use the IF EXISTS clause here to validate if the event already exists. The event must exist Before using the ALTER EVENT, otherwise, an error occurs.
Here is the syntax of the ALTER EVENT statement.
ALTER EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE]
[DO
event_body]Code language: SQL (Structured Query Language) (sql)If you look closely, everything remains unchanged except the ALTER EVENT statement. The whole syntax is the same as creating an event.
Using the above syntax, you can-
- Update the schedule time.
- Decide to preserve or not on completion.
- Rename the event.
- Enable or disable the event.
- Modify the event body.
Examples of MySQL ALTER EVENT
First, we will create an event and later we will modify it. We are using the following table schema.
Creating an Event
Let’s create an event that runs every 5 minutes, for an undefined time. The event will insert a new UUID every 5 minutes into the table.
CREATE EVENT rec_event
ON SCHEDULE EVERY 5 MINUTE
DO
INSERT INTO uuids(uuid,created_at)
VALUES(uuid(),now());Code language: SQL (Structured Query Language) (sql)Let’s check if the event occurs in the event list.
SHOW EVENTS FROM journaldev \G;Code language: SQL (Structured Query Language) (sql)
Let’s display the table data to check whether or not the new rows are inserted into it.
SELECT * FROM uuids;Code language: SQL (Structured Query Language) (sql)
Perfect! The new row is getting inserted every 5 minutes. Let’s modify the event now.
Modifying Schedule of Event
We will change the schedule of the event and set it to execute every 2 minutes now.
ALTER EVENT rec_event
ON SCHEDULE EVERY 2 MINUTE;Code language: SQL (Structured Query Language) (sql)Let’s see the event list to make sure if the scheduled time is changed.
SHOW EVENTS FROM journaldev \G;Code language: SQL (Structured Query Language) (sql)
The event schedule is changed. Let’s modify the event body now.
Modifying Event Body
We will modify the event body and insert a UUID by reversing it.
ALTER EVENT rec_event
DO
INSERT INTO uuids(uuid,created_at)
VALUES(REVERSE(uuid()),now());Code language: SQL (Structured Query Language) (sql)We have used the reverse() function to reverse the UUID.
Let’s display the table data to check if the new statements work fine.
SELECT * FROM uuids;Code language: SQL (Structured Query Language) (sql)
As you can see, the 9th row has reverse UUID which is the result of the modified event body.
Renaming the Event
Our event name is rec_event. We will update it to demo_event using the following statements.
ALTER EVENT rec_event
RENAME TO demo_event;Code language: SQL (Structured Query Language) (sql)Let’s check the event list if the change is reflected in the database.
SHOW EVENTS FROM journaldev \G;Code language: SQL (Structured Query Language) (sql)
As you can see in the highlighted part, the event name is changed.
Enable/Disable the Event
You can disable the event that is currently working in the background. Later, you can enable it again as well.
Below, we will disable the event first and then enable it again.
ALTER EVENT demo_event
DISABLE;Code language: SQL (Structured Query Language) (sql)The event is disabled. You can check if the event is enabled or disabled using the SHOW EVENTS statement.
SHOW EVENTS FROM journaldev \G;Code language: SQL (Structured Query Language) (sql)
As you can see, the status of the event is changed from enabled to disabled.
Let’s enable it again.
ALTER EVENT demo_event
ENABLE;Code language: SQL (Structured Query Language) (sql)The event gets enabled again.
Migrating the Event To Another Database
Using the ALTER EVENT statement, you can migrate the event from one database to another. To achieve this, we use RENAME clause. Have a look below at the syntax-
ALTER EVENT classicmodels.test_event_05
RENAME TO newdb.test_event_05Code language: SQL (Structured Query Language) (sql)By specifying the database name, you can move the event to another database. Before executing the above instructions, the newdb must be present in the system.
Conclusion
In this tutorial, we learned to modify an existing event with simple and effective examples. The scope of the ALTER EVENT is broad and you can modify more than two aspects of the event in one statement. For example, you can rename the event and modify the event body in one go. Isn’t that interesting! Stay tuned with mysqlcode.com for more such interesting tutorials/