Modifying MySQL Scheduled Events – A Complete Guide

Modifying Mysql Events

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]

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.

Uuids Table Description
Uuids Table Description

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());

Let’s check if the event occurs in the event list.

SHOW EVENTS FROM journaldev \G;
Rec Event Information
rec_event Information

Let’s display the table data to check whether or not the new rows are inserted into it.

SELECT * FROM uuids;
Uuids Table Data
Uuids Table Data

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;

Let’s see the event list to make sure if the scheduled time is changed.

SHOW EVENTS FROM journaldev \G;
Modify Event Schedule Time
Modify Event Schedule Time

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());

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;
Uuids Table Data After Modifying Event Body
Uuids Table Data After Modifying Event Body

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;

Let’s check the event list if the change is reflected in the database.

SHOW EVENTS FROM journaldev \G;
Rename The Event
Rename The Event

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;

The event is disabled. You can check if the event is enabled or disabled using the SHOW EVENTS statement.

SHOW EVENTS FROM journaldev \G;
Disabling The Event
Disabling The Event

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;

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_05

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/