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.
- 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
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.
Note: Learn more about cron jobs and how to set them up in our tutorial.
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:
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
SHOW processlistcommand output lists it as a daemon process.
To turn the Event Scheduler
ON, run the following command:
SET GLOBAL event_scheduler = ON;
ON is interchangeable with
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;
OFF is interchangeable with
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
To disable the Event Scheduler, add the following command-line option when starting the server:
Note: The Event Scheduler can be disabled only at server startup and cannot be disabled at runtime if its state is set to
OFF. The value of
event_scheduler cannot be changed at runtime if the Event Scheduler is set to
DISABLED at startup.
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
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.
Events can be scheduled for later execution or for periodical execution.
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 <em>interval</em>]
The possible choices for interval are:
The timestamp must be a
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
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.
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]
STARTS keyword specifies when the event execution starts, while the
ENDS keyword specifies when the event execution stops.
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.
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.
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 ]
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.
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;
This action permanently deletes the event from the database.
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 19th, 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
YEARare 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
- 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 TRANSACTIONstatement instead of
BEGIN WORK, since
BEGIN WORKis treated as the start of the
- Any other limitation of stored procedures applies to events as well.
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.