ALTER EVENT

Use this MySQL statement to alter an existing scheduled MySQL event.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER EVENT
[DEFINER = {'user'@'host'|CURRENT_USER}]
event
ON SCHEDULE
AT timestamp [+ INTERVAL count interval] |
EVERY count interval
   [STARTS timestamp [+ INTERVAL count interval]]
   [ENDS timestamp [+ INTERVAL count interval]]
[ON COMPLETION [NOT] PRESERVE]
[ENABLE|DISABLE|DISABLE ON SLAVE]
[COMMENT 'comment']
DO statement

Explanation

Use this statement to alter an existing scheduled MySQL event. The statement can be used to change the time when the scheduled SQL statement will execute, or other aspects of its upcoming execution. The event parameter has to be the name of an event that was already scheduled but has not yet been completed, or was completed but preserved by the server. It isn't possible within MySQL to change the name of an event. Instead, use the DROP EVENT statement to delete an existing event and then create it again with a new name with CREATE EVENT. You can use the SHOW CREATE EVENT statement to be sure that all other parameters are the same.

To change the MySQL user and host through which MySQL executes the event, use the DEFINER clause. As of version 5.1.12 of MySQL, a user that has EVENT privilege can change an event. Unless the definer is specified with the DEFINER clause, the user that changes an event becomes the new definer.

To change the time and date that form the basis for running the event, use the ON SCHEDULE AT clause and give the new time in the timestamp format (yyyy-mm-dd hh:mm:ss). The time given can be a string, a time function, or just CURRENT_TIMESTAMP. You can also specify to a time relative to the timestamp given by adding a plus sign followed by the keyword INTERVAL, the number of intervals (e.g., 1) and then the interval increment (e.g., HOUR). For interval, use one of the allowable intervals shown in the description of the CREATE EVENT statement.

To make the event a recurring one, add the EVERY clause, using the same syntax and format. You can also give starting and ending times for a recurring event with the STARTS and ENDS clauses.

If an event is not yet completed, you can keep the server from dropping it by adding the ON COMPLETION clause with the PRESERVE keyword. If you already did this when you created the event, you can change your mind and set the server to NOT PRESERVE the event.

If you created an event that you need to temporarily disable for some reason, you can do so with this statement by using the DISABLE keyword. An event that has been disabled can be enabled with the ENABLE keyword. The DISABLE ON SLAVE keyword prevents the event from running on slave servers.

With the COMMENT clause you can add or change a comment describing the event for future reference. The DO clause can include any SQL statement to be executed. A stored procedure can be used to easily execute a set of SQL statements. Don't confuse the DO clause with the DO statement.

Examples

An example using this statement to change a periodic event follows:

ALTER EVENT students_copyON SCHEDULE EVERY 1 DAY
STARTS '2007-12-10 01:30:00'
ON COMPLETION PRESERVE;

If you look at the example for CREATE EVENT, you'll see that our only change is to move the time from 2:30 A.M. to 1:30 A.M. here. However, the starting time and date given are not only for the time we want, but since this statement was run on December 9, the date of December 10 was given. When an event's time is altered or when an event is first created, it must be for a future time. The EVERY clause is included because STARTS is part of it and not a separate clause of its own. So that the ON COMPLETION PRESERVE isn't set back to the default of ON COMPLETION NOT PRESERVE, we stipulate it again here.