MySQL Events and Event Scheduler Explained

Introduction

MySQL Events are tasks that run according to a user-defined schedule. The Event Scheduler is a built-in MySQL feature that executes these events at the scheduled time.

Database administrators use MySQL Events to automate repetitive operational tasks and manage databases more efficiently.

Learn how MySQL Events work and how to set them up through practical examples.

Prerequisites:

• A MySQL database server.
• MySQL user account with root privileges.

What Are MySQL Events?

MySQL Events are scheduled tasks or jobs that run automatically at a specified time or regular intervals. Users define the time or interval within the MySQL database server, and the Event Scheduler ensures the tasks are executed at the predefined times.

What Is MySQL Event Scheduler?

The MySQL Event Scheduler is a server-level process that runs in the background to monitor and execute scheduled events. It allows users to automate data synchronization, database maintenance, or cleanup operations directly within the MySQL server.

The Event Scheduler is in a sleep state unless the global variable event_scheduler is set to ON (or 1). To enable the Event Scheduler, log in to your MySQL server and enter the following command in the MySQL prompt:

SET GLOBAL event_scheduler = ON;

The value ON is interchangeable with 1.

What Is Event Scheduling in MySQL?

Event scheduling in MySQL refers to the process of creating and managing MySQL Events to execute SQL statements at specific times or intervals. To schedule an event in MySQL, you need to:

• Enable the MySQL Event Scheduler.
• Specify the SQL statements you want the Event Scheduler to execute and when they should run. For example, execute once at a specific time or repeatedly at a defined interval.
• Verify the event was created and is functioning as expected.

Benefits of Event Scheduling in MySQL

Scheduling events in MySQL has the following benefits:

• Integration with MySQL. Operates directly on the MySQL Server and is fully managed using SQL statements.
• Platform and application-independent. It works across various platforms without relying on external tools or operating system-specific features.
• Flexible scheduling. Supports one-time and recurring scheduling.
• Streamlined workflow. Reduces the number of query compilations and eliminates the need for external scripts or programs.
• Automation. Automates routine database tasks such as maintenance, cleanup, or data synchronization.

How to Configure MySQL Event Scheduler

The MySQL Event Scheduler state can be set to ON, OFF, or DISABLED.

State	Description	Command
ON	The Event Scheduler thread is running and executes all scheduled events.	SET GLOBAL event_scheduler = ON;   (ON is interchangeable with 1)
OFF	The Event Scheduler is inactive, and scheduled events are not executed.	SET GLOBAL event_scheduler = OFF; (OFF is interchangeable with 0)
DISABLED	The Event Scheduler is not operational. The DISABLED state can only be set at server startup and cannot be changed to ON or OFF during runtime.	--event-scheduler=DISABLED

Enter the following command to check the Event Scheduler state:

SELECT @@event_scheduler;

The value of the event_scheduler system variable shows the current state of the Event Scheduler.

If the Event Scheduler is ON, it runs as a daemon process and is visible in the output of the SHOW PROCESSLIST command:

SHOW PROCESSLIST;

In this example, the Event Scheduler thread has the status Waiting on empty queue, indicating that the Scheduler is ON and waiting for an event to trigger it.

How to Schedule and Manage MySQL Events

The following section explains how to create, view, modify, and delete MySQL Events.

Create New Events

To create a new event in MySQL, you need to define the event's name, schedule, and SQL statement to execute. Use the following syntax:

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

The command includes the following elements:

• IF NOT EXISTS. Ensures the event name is unique in the database.
• event_name. Replace the placeholder with a unique event name.
• ON SCHEDULE. Define when the event will run (one-time or recurring). Replace schedule with a valid DATETIME, TIMESTAMP, or interval value.
• event_body. Contains the SQL statements the event is going to execute. The event body can contain stored procedures, single queries, or compound statements enclosed in a BEGIN…END block.

Once the event is created, it runs according to the schedule. Events can be scheduled as one-time or recurring tasks.

Schedule One-Time Events

A one-time event executes only once and then is automatically deleted. To create a one-time event, specify the timestamp after the ON SCHEDULE clause using the following syntax:

ON SCHEDULE AT timestamp + [INTERVAL interval]

The clause includes the following components:

• timestamp. A valid DATETIME or TIMESTAMP value. Adding the CURRENT_TIMESTAMP function returns the MySQL server's current date and time.
• interval. Add an interval to the timestamp using + INTERVAL. The format must include a positive integer followed by one of the predefined interval options (e.g., 5 DAY).

Possible interval values include:

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

The following command creates an event named db_cleanup and instructs the Scheduler to drop a table named test two days from now:

CREATE EVENT db_cleanup
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 DAY
DO
DROP TABLE test;

One-time events are automatically dropped after execution. If you want to preserve the event in the database, add the ON COMPLETION PRESERVE clause when creating the event.

Schedule Recurring Events

A recurring event occurs repeatedly at a specified time. To schedule a recurring event, use the following syntax:

ON SCHEDULE EVERY interval
STARTS timestamp [+ INTERVAL interval]
ENDS timestamp [+ INTERVAL interval];

• EVERY. Defines the repetition interval (e.g., 6 MONTH).
• STARTS. Specifies when the event execution starts.
• ENDS. Specifies when the event execution stops.

The following command schedules MySQL to drop the table named test every six months, starting immediately:

CREATE EVENT db_cleanup
ON SCHEDULE EVERY 6 MONTH
STARTS CURRENT_TIMESTAMP
DO
DROP TABLE test;

You can delay the start of the event by specifying an interval. This example schedules the first execution two days from now:

CREATE EVENT db_cleanup
ON SCHEDULE EVERY 6 MONTH
STARTS CURRENT_TIMESTAMP + INTERVAL 2 DAY
DO
DROP TABLE test;

The following command schedules MySQL to drop the test table every six months, starting two days from now and ending five years from now:

CREATE EVENT db_cleanup
ON SCHEDULE EVERY 6 MONTH
STARTS CURRENT_TIMESTAMP + INTERVAL 2 DAY
ENDS CURRENT_TIMESTAMP + INTERVAL 5 YEAR
DO
DROP TABLE test;

After five years, the event will be automatically dropped after execution. To retain the event in the database for future reference or reuse, add the ON COMPLETION PRESERVE clause when creating it.

Preserving Events

By default, the system deletes events from the database after execution, and they no longer appear in the list of events. This includes recurring events if the ENDS clause was specified.

If you want to retain the event in the database, include the ON COMPLETION PRESERVE clause when creating it:

CREATE EVENT db_cleanup
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 2 DAY
ON COMPLETION PRESERVE
DO
DROP TABLE test;

A preserved event remains in the database even after execution, allowing you to review, modify, or reuse it later.

Show Events

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

SHOW EVENTS FROM database_name;

Events dropped after execution and no longer appear in the SHOW EVENTS command output unless you use the ON COMPLETION PRESERVE clause when creating the event.

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

Modify Events

Use the ALTER EVENT statement to modify 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 they remain in their previous state while new values you specify are applied.

For example, to rename the db_cleanup event and change its SQL statement, enter:

ALTER EVENT db_cleanup
RENAME TO delete_records
DO
TRUNCATE TABLE payments;

The command renames the db_cleanup event to delete_records and updates the event body to truncate the payments table instead of deleting the test table.

Remove Events (Drop Event)

To permanently delete (drop) an event from the database, use the following syntax:

DROP EVENT [IF EXISTS] event_name;

The IF EXISTS statement prevents an error and issues a warning if the event does not exist. In this example, the db_cleanup event does not exist:

DROP EVENT IF EXISTS db_cleanup;

As a result, MySQL returns the Event db_cleanup does not exist message.

MySQL Event Limitations

MySQL Events are useful for scheduling tasks, but they also have several limitations:

• No result set. The output is directed to dev/null, and the event fails or succeeds without notifying the user.
• Case insensitivity. Two events cannot have the same name with different cases.
• Limited date range. Events cannot be scheduled beyond January 19^th, 2038 – the maximum represented in the Unix epoch.
• Program interactions. Events cannot be created, dropped, or altered by another stored program, trigger, or event. Similarly, events cannot create, drop, or alter stored programs or triggers.
• Interval resolution. MONTH, YEAR_MONTH, QUARTER, and YEAR are resolved in months. All other intervals are resolved in seconds.
• Unspecified execution order. There can be two events with the same schedule, but there is no way to force an execution order.
• Definer-based privileges. 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.
• No impact on statistics. Events do not change the count of a statement's execution, which means there is no effect on the SHOW STATISTICS command.
• Execution timing delay. The maximum delay for executing an event is two seconds. The information_schema.events table always displays the accurate time of event execution.
• A limited number of recursive calls. 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.
• Transaction statement restrictions. BEGIN WORK is treated as the start of the BEGIN…END block in MySQL. To initiate a transaction, use the START TRANSACTION statement instead.
• Stored procedure limitations. All limitations that apply to stored procedures also apply to events.

Conclusion

The guide explained how to use MySQL Events to automate database management tasks.

Experiment with different SQL statements to see how they affect events and combine them with other features, such as MySQL Triggers.