How to Create, alter, and drop events in MySQL

MySQL Events are tasks that run according to a schedule. They can be referred to as “scheduled events.” When you create an event, it is created as a named database object containing a SQL statement (or stored procedure) that executes at a certain moment in time, optionally recurring at a regular interval. Conceptually, this is similar to the idea of the Linux/UNIX crontab (also known as a “cron job”) or the Windows Task Scheduler.

Syntax of a MySQL Event:

CREATE EVENT [event_name]
ON SCHEDULE [schedule] DO [sql_statement]

Mandatory items

• event_name: Events are schema objects, just like tables, stored procedures, and triggers. The event_name must be a valid identifier, and it may be quoted and/or qualified with the schema name in the usual way. Event names must be unique within the schema.
• schedule: The schedule is a rule that specifies when MySQL executes the action that is associated with the event.
• sql_statement: You must include a valid SQL statement or stored procedure statement that is executed according to the schedule. This statement is subject to the same limitations applied to stored functions and dynamic SQL. For example, the statement cannot return a result set (such as those produced by SELECT or SHOW). Typically, you use a CALL statement to call a procedure to perform the actual action.

Event statements:

• SET GLOBAL event_scheduler = {ON | OFF}
• CREATE EVENT
• ALTER EVENT
• DROP EVENT

Event Scheduler

When you create an event, it is stored in the database so it can be executed according to a schedule. The schedules of all events are monitored by the event_scheduler thread, which starts a new thread to execute each event when the scheduled time arrives. By default, the event_scheduler thread is set to OFF. You must explicitly enable it by modifying the value of the global event_scheduler server variable, setting it to ON. You can do this either by adding the server variable to an option file (so the change takes effect on startup) or by dynamically using the SET syntax. If you start the server with event_scheduler set to DISABLED, you cannot enable it with a SET statement while MySQL is running. Rather, you must stop MySQL and restart it with the option enabled. After enabling the event_scheduler thread, you can see it in the output of SHOW PROCESSLIST (and the INFORMATION_SCHEMA equivalent, PROCESSLIST).

Altering Events

You can change EVENTs by using the ALTER EVENT syntax. You can change each element of the EVENT with this syntax. Consequently, the syntax model for ALTER EVENT is almost identical to that of the CREATE EVENT statement.

Schedule

A schedule is a rule that specifies when an action should be executed. You can specify the schedule in the SCHEDULE clause of the CREATE EVENT and ALTER EVENT statements.

Types of Schedule Actions

There are two types of schedules:

• Those that are executed once (using the AT keyword)
• Those that can be executed repeatedly (using the EVERY keyword)

For the latter, you must define the frequency of the event’s repetition. You can also define a time window that determines the period during which the event should be repeated. The Event Scheduler executes scheduled events and launches a separate thread to execute each event. You can use events for automatic, periodic execution of tasks. For example, regular CSV dumps of table data, ANALYZE TABLE, creating periodic summary tables.

The syntax for the SCHEDULE clause is as follows:

AT timestamp [+ INTERVAL interval]| EVERY interval [STARTS timestamp [+ INTERVAL interval]] [ENDS timestamp [+ INTERVAL interval]]

SCHEDULE Clause

The SCHEDULE clause can contain the following variable elements: 1. timestamp: An expression of the DATETIME or TIMESTAMP type. 2. interval: Specifies a duration. The duration is expressed by specifying a quantity as an integer followed by a keyword that defines a particular kind of duration. Valid keywords are:

• YEAR
• QUARTER
• MONTH
• DAY
• HOUR
• MINUTE
• WEEK
• SECOND
• YEAR_MONTH
• DAY_HOUR
• DAY_MINUTE
• DAY_SECOND
• HOUR_MINUTE
• HOUR_SECOND
• MINUTE_SECOND

Event Scheduler

The Event Scheduler, which is a separate thread in the mysqld process, is responsible for executing scheduled events. It checks whether it should execute an event; if it should, it creates a new connection to execute the action.

Event Scheduler Use Cases

Use events for automatic, periodic execution of (maintenance) tasks, such as updating a summary table or refreshing a table from a query (materialized view emulation), or for performing nightly jobs. Examples include processing the day’s work, loading a data warehouse, or exporting data to file.

Event Scheduler and Privileges

You must have the SUPER privilege to set the global event_scheduler variable. You must have the EVENT privilege to create, modify, or delete events. Use GRANT to assign privilege (at the schema level only):

mysql> GRANT EVENT ON myschema.* TO user1@srv1;
mysql> GRANT EVENT ON *.* TO user1@srv1;

Revoke Event Privilege

To cancel the EVENT privilege, use the REVOKE statement. Revoking the EVENT privilege from a user account does not delete or disable any events already created by that account.

REVOKE EVENT ON myschema.* FROM user1@srv1;

mysql Tables

Users’ EVENT privileges are stored in the Event_priv columns of the mysql.user and mysql.db tables. In both cases, this column holds one of the values ‘Y’ or ‘N’. ‘N’ is the default value.

The value of mysql.user.Event_priv is set to ‘Y’ for a given user only if that user has the global EVENT privilege. For a schema-level EVENT privilege, GRANT creates a row in mysql.db and sets that row’s column values as follows:

• Db: The name of the schema.
• User: The name of the user
• Event_priv: ‘Y’

You do not have to manipulate these tables directly, because the GRANT EVENT and REVOKE EVENT statements perform the required operations on them.

Event Execution Privileges

It is important to understand that an event is executed with the privileges of its definer, and that it cannot perform any actions for which its definer does not have the required privileges. For example, suppose that user1@srv1 has the EVENT privilege for myschema. Suppose also that he has the SELECT privilege for myschema, but no other privileges for this schema.

For example, user1@srv1 can create a SELECT event for myschema only as shown below:

CREATE EVENT e_store_ts
ON SCHEDULE
EVERY 10 SECOND
DO
INSERT INTO myschema.mytable VALUES
(UNIX_TIMESTAMP());

Even though it is possible for user1@srv1 to create a new event, the event itself cannot perform an INSERT operation because the definer, user1@srv1, does not have the privileges to perform the action.

Error Log

If you inspect the MySQL error log (hostname.err), you can see that the event is executing but that the action it is attempting to perform fails, as indicated by RetCode=0:

...
060209 22:39:44 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:44 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0
060209 22:39:54 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:39:54 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0
060209 22:40:04 [Note] EVEX EXECUTING event newdb.e [EXPR:10]
060209 22:40:04 [Note] EVEX EXECUTED event newdb.e [EXPR:10]. RetCode=0
...

Examining Events

SHOW CREATE EVENT event_name

This statement displays the CREATE EVENT statement needed to re-create a given event. You must provide the name of the event to view information about that event.

SHOW EVENTS

This statement is a MySQL extension. It returns characteristics of events, such as the database, name, type, creator, and creation and modification dates. This statement has the advantage of being able to display specific events based on a condition provided in a LIKE pattern or WHERE clause. If you do not provide such a condition, the SHOW EVENTS statement shows the information for all events.

INFORMATION_SCHEMA.EVENTS

EVENT_CATALOG: NULL
EVENT_SCHEMA: myschema
EVENT_NAME: e_store_ts
DEFINER: user1@srv1
EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES
(UNIX_TIMESTAMP())
...

Dropping Events

Explicitly drop an event by using this syntax:

DROP EVENT [IF EXISTS]
[schema_name.]event_name;

Use IF EXISTS to prevent the error that occurs when you attempt to delete an event that does not exist. You must have the EVENT privilege for the database that contains the event to be dropped.