Modifying MySQL Events

 Modifying MySQL Events

Summary: in this tutorial, you will learn how to use the MySQL ALTER EVENT to modify an existing MySQL Event including modifying the schedule of an event, enabling or disabling an event, and renaming an event.

MySQL allows you to change various attributes of an existing event. To change existing events, you use the  ALTER EVENT a statement as follows:

ALTER EVENT event_name
ON SCHEDULE schedule
ON COMPLETION [NOT] PRESERVE
RENAME TO new_event_name
ENABLE | DISABLE
DO
  event_body

Notice that the ALTER EVENT the statement is only applied to an existing event. If you try to modify a nonexistent event, MySQL will issue an error message therefore, you should always use the  SHOW EVENTS statement to check the event for its existence before changing it.

SHOW EVENTS FROM classicmodels;

MySQL ALTER EVENT examples

Let’s create a sample event to demonstrate various features of the  ALTER EVENT statement.

The following statement creates an event that inserts a new row into the messages table every minute.

CREATE EVENT test_event_04
ON SCHEDULE EVERY 1 MINUTE
DO
   INSERT INTO messages(message,created_at)
   VALUES('Test ALTER EVENT statement',NOW());

Changing the schedule

To make the event run every 2 minutes, you use the following statement:

ALTER EVENT test_event_04
ON SCHEDULE EVERY 2 MINUTE;

Changing the event body

You can also change the event’s body by specifying the new logic as follows:

ALTER EVENT test_event_04
DO
   INSERT INTO messages(message,created_at)
   VALUES('Message from event',NOW());

You can wait for 2 minutes and check the messages table again:

SELECT * FROM messages;

Disable an event

To disable the event, you use the following statement:

ALTER EVENT test_event_04
DISABLE;

You can check the status of the event by using the  SHOW EVENTS a statement as follows:

SHOW EVENTS FROM classicmodels;

Enable an event

To enable a disabled event, you use the ENABLE keyword after the  ALTER EVENT a statement as follows:

ALTER EVENT test_event_04
ENABLE;

Rename an event

MySQL does not provide you with the  RENAME EVENT statement. Fortunately, you can use the  ALTER EVENT to rename an existing event as follows:

ALTER EVENT test_event_04
RENAME TO test_event_05;

Move an event to another database

You can move an event from one database to another database by using the  RENAME TO clause as follows:

ALTER EVENT classicmodels.test_event_05
RENAME TO newdb.test_event_05

It is assumed that the newdb the database is available on your MySQL database server.

In this tutorial, we have shown you how to change various attributes of a MySQL event by using the  ALTER EVENT statement.