MySQL Events and Event Scheduler Guide

Introduction

MySQL Events are tasks that run according to a user-defined schedule. The Event Scheduler is a special thread that executes the Events at a scheduled time.

MySQL Events are helpful because they facilitate database management and periodical database operational tasks.

In this tutorial, you will learn what MySQL Events are and how to set up and use them.

Prerequisites:

• A system running MySQL on a database server
• MySQL user account with root privileges

What is MySQL Event Scheduler?

MySQL Event Scheduler is a thread that runs in the background and manages the execution of scheduled events. The Scheduler is in a sleep state unless the global variable event_scheduler is set to ON or 1.

The MySQL Event Scheduler represents MySQL’s alternative to Cron job. Some benefits of the Event Scheduler are:

• It is written directly on the MySQL Server.
• It is platform and application independent.
• It can be used whenever a regular database update or cleanup is required.
• It reduces the number of query compilations.

MySQL Event Scheduler Configuration

The MySQL Event Scheduler state can be configured to set the Scheduler on, off, or disable it.

To check the Event Scheduler state, run the following command:

SHOW processlist;

The event_scheduler system variable displayed in the result shows the state of the Event Scheduler. Here, the event_scheduler variable state is Waiting on empty queue, which means that the Scheduler is on and waiting for an event to trigger it.

The possible states are:

• ON: The Event Scheduler thread is running and executes all scheduled events. This is the Scheduler's default state. If the Scheduler is ON, the SHOW processlist command output lists it as a daemon process.

To turn the Event Scheduler ON, run the following command:

SET GLOBAL event_scheduler = ON;

The value ON is interchangeable with 1.

• OFF: The Event Scheduler thread is not running, and it does not show up in the output of SHOW processlist. If the Event Scheduler is set to OFF, the scheduled events are not executed.

To turn the Event Scheduler OFF, run the following command:

SET GLOBAL event_scheduler = OFF;

The value OFF is interchangeable with 0.

• DISABLED: This state means that the Event Scheduler is not operational. The thread does not run, and it does not show up in the output of the SHOW processlist command.

To disable the Event Scheduler, add the following command-line option when starting the server:

--event-scheduler=DISABLED

MySQL Event Scheduling

An event is a database object containing SQL statements executed at a specified time or in regular intervals. The events begin and end at a specific time and date.

Since MySQL Events execute at a time a user specifies, these events are also referred to as temporal triggers. However, they should not be mixed up with MySQL Triggers, which are database objects executed in response to specific events. Hence, it is better to use the term events when referring to scheduled tasks to avoid confusion.

The following sections discuss how to create, show, change or remove events.

Create New Events

To create a new event, use the following syntax:

CREATE EVENT [IF NOT EXIST] event_name
ON SCHEDULE schedule
DO
event_body

The IF NOT EXIST statement makes sure that the event name is unique to the database in use. Specify a unique event name in place of the event_name syntax. The schedule for executing the event is specified after the ON SCHEDULE statement. The event can be a one-time event or a recurring event.

Enter SQL statements in place of the event_body syntax after the DO keyword. The event body can contain stored procedures, single queries or compound statements. Write compound statements within a BEGIN END block.

Schedule Events

Events can be scheduled for later execution or for periodical execution.

One-Time Event

A one-time event is executed only once and then automatically deleted.

To create a one-time event, specify the timestamp after the ON SCHEDULE statement using the following syntax:

AT timestamp + [INTERVAL interval]

The possible choices for interval are:

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

The timestamp must be a DATETIME or TIMESTAMP value in the future. To specify an exact time, add an interval to the timestamp using + INTERVAL, a positive integer, and one of the interval choices. Note that this applies only when using the CURRENT_TIMESTAMP function.

For example:

Here, the event happens two days from its creation, and the task is to drop a table named test.

Events are automatically dropped after execution. If you want to save the event in the database, add the ON COMPLETION PRESERVE clause when creating the event.

Recurring Event

A recurring event happens repeatedly at a specified time. To schedule a recurring event, use the following syntax after the ON SCHEDULE statement:

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

The STARTS keyword specifies when the event execution starts, while the ENDS keyword specifies when the event execution stops.

For example:

This event causes MySQL to drop table test once each six months, starting immediately.

You can also specify an interval to start the event later. For example:

You can also specify the start time and end time for the event:

This event makes MySQL drop the table named test once every six months for five years, starting five days from now.

Show Events

The following command displays all the events stored in the database:

SHOW EVENTS FROM database_name;

Note that one-time events are automatically dropped after execution and do not show up in the output of the SHOW EVENTS command, unless you use the ON COMPLETION PRESERVE clause when creating the event.

The output lists all the events stored in the specified database.

Change Events

Use the ALTER EVENT statement to change an existing event:

ALTER EVENT event_name
[ ON SCHEDULE schedule ]
[ RENAME TO new_event_name ]
[ ON COMPLETION [ NOT ] PRESERVE ]
[ COMMENT 'comment' ]
[ ENABLED | DISABLED ]
[ DO sql_statement ]

The event_name must be an event that already exists. All the statements after ALTER EVENT are optional, depending on what you want to change. Omitting any clauses in the ALTER EVENT command means that they remain in their previous state. Any included clause means that new values you specify are applied.

For example:

In this example, we renamed the event and changed its SQL statement.

Remove Events (Drop Event)

To delete (drop) an event, use the following syntax:.

DROP EVENT [IF EXISTS] event_name;

For example:

This action permanently deletes the event from the database.

Using the IF EXISTS statement issues a warning if such an event does not exist:

MySQL Event Limitations

There are certain limitations to consider when using MySQL Events. Some of them are:

• Events cannot return a result set. The output is directed to dev/null, and the event either fails or succeeds without notifying the user.
• Event names are not case sensitive. Two events cannot have the same name with different case.
• Events cannot be scheduled beyond January 19^th, 2038 – the maximum that can be represented in the Unix epoch.
• Events cannot be created, dropped, or altered by another stored program, trigger, or event.
• Events cannot create, drop, or alter stored programs or triggers.
• The intervals MONTH, YEAR_MONTH, QUARTER, and YEAR are resolved in months. All other intervals are resolved in seconds.
• There can be two events with the same schedule, but there is no way to force an execution order.
• An event always runs with definer privileges. The thread executes the event acting as the user who created the event, with that user’s privileges. Note that removing a user does not remove the events the user created.
• Events do not change the count of a statement’s execution, which means there is no effect on the SHOW STATISTICS command.
• The maximum delay for executing an event is two seconds. However, the information_schema.events table always displays the accurate time of event execution.
• Use user-defined variables instead of references to local variables within prepared statements inside a stored routine.
• The number of recursive calls is limited to max_sp_recursion_depth. If this variable is 0, which is the default value, recursivity is disabled.
• Use the START TRANSACTION statement instead of BEGIN WORK, since BEGIN WORK is treated as the start of the BEGIN END block.
• Any other limitation of stored procedures applies to events as well.

Conclusion

Now you know how to use MySQL Events to automate database management. Feel free to experiment with different statements to see how they affect the events and combine them with other MySQL features, such as stored procedures.