13.1.2 ALTER EVENT Statement
ALTER [DEFINER = user] EVENT event_name [ON SCHEDULE schedule] [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_event_name] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'string'] [DO event_body]
ALTER EVENT statement changes one or more of the characteristics of an existing event without the need to drop and recreate it. The syntax for each of the
DO clauses is exactly the same as when used with
CREATE EVENT. (See Section 13.1.12, “CREATE EVENT Statement”.)
Any user can alter an event defined on a database for which that user has the
EVENT privilege. When a user executes a successful
ALTER EVENT statement, that user becomes the definer for the affected event.
ALTER EVENT works only with an existing event:
mysql> ALTER EVENT no_such_event > ON SCHEDULE > EVERY '2:3' DAY_HOUR; ERROR 1517 (HY000): Unknown event 'no_such_event'
In each of the following examples, assume that the event named
myevent is defined as shown here:
CREATE EVENT myevent ON SCHEDULE EVERY 6 HOUR COMMENT 'A sample comment.' DO UPDATE myschema.mytable SET mycol = mycol + 1;
The following statement changes the schedule for
myevent from once every six hours starting immediately to once every twelve hours, starting four hours from the time the statement is run:
ALTER EVENT myevent ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;
It is possible to change multiple characteristics of an event in a single statement. This example changes the SQL statement executed by
myevent to one that deletes all records from
mytable; it also changes the schedule for the event such that it executes once, one day after this
ALTER EVENT statement is run.
ALTER EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO TRUNCATE TABLE myschema.mytable;
Specify the options in an
ALTER EVENT statement only for those characteristics that you want to change; omitted options keep their existing values. This includes any default values for
CREATE EVENT such as
myevent, use this
ALTER EVENT statement:
ALTER EVENT myevent DISABLE;
ON SCHEDULE clause may use expressions involving built-in MySQL functions and user variables to obtain any of the
interval values which it contains. You cannot use stored routines or user-defined functions in such expressions, and you cannot use any table references; however, you can use
SELECT FROM DUAL. This is true for both
ALTER EVENT and
CREATE EVENT statements. References to stored routines, user-defined functions, and tables in such cases are specifically not permitted, and fail with an error (see Bug #22830).
ALTER EVENT statement that contains another
ALTER EVENT statement in its
DO clause appears to succeed, when the server attempts to execute the resulting scheduled event, the execution fails with an error.
To rename an event, use the
ALTER EVENT statement's
RENAME TO clause. This statement renames the event
ALTER EVENT myevent RENAME TO yourevent;
You can also move an event to a different database using
ALTER EVENT ... RENAME TO ... and
notation, as shown here:
ALTER EVENT olddb.myevent RENAME TO newdb.myevent;
To execute the previous statement, the user executing it must have the
EVENT privilege on both the
There is no
RENAME EVENT statement.
DISABLE ON SLAVE is used on a replication slave instead of
DISABLE to indicate an event that was created on the master and replicated to the slave, but that is not executed on the slave. Normally,
DISABLE ON SLAVE is set automatically as required; however, there are some circumstances under which you may want or need to change it manually. See Section 184.108.40.206, “Replication of Invoked Features”, for more information.