Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 using 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 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 later in this chapter.

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.

Here is an example using this statement to change a periodic event:

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

If you look at the example for CREATE EVENT later in this chapter, you’ll see that our only change is to move the time from 2:30 A.M. to 1:30 A.M. here. The starting time and date given are not only for the time we want, but since this statement is run on December 9, the date of December 10 is 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.