MySQL Event Scheduler: How to Schedule & Execute Events Easily

MySQL Events are tasks that run on a predefined schedule. As a result, MySQL events are sometimes referred to as scheduled events. MySQL Events are also called named objects that contain at least one SQL explanation. They are saved in the database and run at regular intervals.

MySQL Event Scheduler is in charge of managing the scheduling and execution of Events. They are analogous to cronjobs in Linux or task schedulers in Windows. For example, you could schedule an event that optimizes all tables in the database to run every Sunday at 1:00 a.m. 

This blog will teach you about the MySQL event scheduler and how to use it to automate repetitive database tasks.

Prerequisites

• An Idea about RDBMS & MySQL

What is MySQL?

MySQL is a dependable Database Management System (DBMS) that employs Structured Query Language (SQL) to perform data-related operations. Its well-known version provides an open-source platform to which you can easily gain access and carry out your Data Management responsibilities.

MySQL‘s ease of use has given it an advantage over competing DBMS systems like Microsoft SQL Server and Oracle Databases. 

Furthermore, you could work seamlessly with MySQL and any programming language used in your industry.

MySQL is the go-to choice for scalable web applications because it comes standard in the LAMP Stack, which is an Open-Source suite of web applications comprised of Linux, Apache HTTP Server, MySQL, and PHP.

Key Features of MySQL

MySQL is a popular DBMS on the market due to the following features:

• Oracle maintained: It also provides premium MySQL versions that include additional services, proprietary plugins, user support, and extensions.
• Updates: MySQL is becoming more robust as a result of frequent updates, which include new features and security enhancements. The most recent version is 8.0.23, which was released on January 18, 2021.
• MVCC (Multi-Version Concurrency Control) Features: MySQL has recently begun to offer MVCC (Multi-Version Concurrency Control) features.
• A Helpful Community: A dedicated community of developers is available to assist with troubleshooting as needed.
• Easy Access: MySQL is a free and open-source relational database management system (RDBMS).

What is MySql Event Scheduler?

MySQL Event Scheduler is a background thread that manages the execution of scheduled events. Unless the global variable event_scheduler is set to ON or 1, the Scheduler is in a dormant state.

The MySQL Event Scheduler is MySQL’s replacement for the Cron job. The Event Scheduler has the following advantages:

• It is directly written on the MySQL Server.
• It is platform and application agnostic.
• It can be used whenever there is a need for a regular database update or cleanup.
• It reduces the number of queries that must be compiled.

Configuration of the MySQL Event Scheduler

The MySQL Event Scheduler state can be set to enable, disable, or turn on the Scheduler.

Run the following command to check the Event Scheduler’s status:

SHOW processlist;

The state of the MySQL Event Scheduler is shown by the event_scheduler system variable in the result.

The event_scheduler variable state, in this case, is Waiting on an empty queue, indicating that the Scheduler is active and waiting for an event to trigger it.

Creating a new MySQL Event

We can use the following syntax for creating an event:

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 not been used before. In place of event_name syntax, we need to specify a unique event name. 

Using the ON SCHEDULE we can schedule the event.
You need to enter the SQL statements in place of the event_body syntax after the DO keyword.

Schedule Events 

MySQL Events execute at a time specified by the user, they are also known as temporal triggers. They should not be confused with MySQL Triggers, which are database objects that are executed in response to specific events. 

As a result, to avoid confusion, it is preferable to refer to scheduled tasks as events.

• One Time Event
• Recurring Event 

One Time Event 

A one-time event is executed once and then deleted automatically.To create a one-time event, specify the timestamp after the ON SCHEDULE statement as follows:

AT timestamp + [INTERVAL interval]

The possible choices of intervals 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 in the future as a DATETIME or TIMESTAMP value. To specify an exact time, use + INTERVAL, a positive integer, and one of the interval options to add an interval to the timestamp. Please keep in mind that this only applies when using the CURRENT_TIMESTAMP function.

The event occurs two days after it is created, and the task is to delete a table named test.

After execution, events are automatically dropped.

Let us understand more clearly with an example.

• First, we need to create a table named “Demo” using the below command.

CREATE TABLE Demo (
id INT PRIMARY KEY AUTO_INCREMENT, 
message VARCHAR(255) NOT NULL, 
created_on DATETIME NOT NULL
);

• Then we will create an event using the below command.

CREATE EVENT IF NOT EXISTS one_time_event ON SCHEDULE AT CURRENT_TIMESTAMP DO INSERT INTO Demo (message, created_on) VALUES ('Test MySQL Event 1', NOW()); );

• Now we will check the Demo table in the data set there is one line that got inserted utilizing the event scheduler since we specify the time as the current timestamp as the scheduled time.

SELECT * FROM Demo;

• Now after using the following command to display all the events from the database, the above-created event will not show in that list as it’s a one-time event so it is expired after its execution and the event will automatically drop.

SHOW EVENTS FROM database_name;

• Now to keep the event after it is expired we can use the ON COMPLETION PRESERVE clause.

CREATE EVENT one_time_event ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ON COMPLETION PRESERVE DO INSERT INTO Demo (message, created_at) VALUES ('Test MySQL Event ,NOW());

• Now using the below command we can check the events list.

SHOW EVENTS FROM database_name;

Recurring Event

A recurring event occurs on a regular basis at a predetermined time. After the ON SCHEDULE statement, use the following syntax to schedule a recurring event:

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

The STARTS keyword specifies when event execution begins, while the ENDS keyword specifies when event execution concludes.

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.

Show Events 

All the events stored in the database can be displayed using the following command.

SHOW EVENTS FROM database_name;

You need to use the ON COMPLETION PRESERVE clause when creating the event to see the one-time events as they are automatically dropped after execution.

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.

Remove Events 

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

DROP EVENT [IF EXISTS] event_name;

This action permanently deletes the event from the database.

MySQL Event Scheduler Limitations

• Events do not have the ability to return a result set. The output is directed to dev/null, and the event succeeds or fails without informing the user.
• The names of events are not case-sensitive. Two events with the same name but different cases cannot exist.
• There can be two events with the same schedule, but no execution order can be forced.
• An event is always run with definer permissions. The thread executes the event as the user who created it, with all of that user’s privileges.
• The maximum number of recursive calls is max_sp_recursion_depth. Recursivity is disabled if this variable is set to 0, which is the default value.
• Because BEGIN WORK block is treated as the start of the BEGIN END statement, use the START TRANSACTION statement instead.
• Any limitation of stored procedures also applies to events.

Conclusion

You now understand how to automate database management with MySQL Events. Experiment with different statements to see how they affect the events, and combine them with other MySQL features like stored procedures.

You now understand how to automate database management with MySQL Events. Experiment with different statements to see how they affect the events, and combine them with other MySQL features like stored procedures. You can also read about working with the Airflow Scheduler if you are working with Apache Airflow to manage multiple workflows in your organization.

Extracting complex data from a diverse set of data sources to perform insightful analysis can be difficult, which is where Hevo comes in!

Want to take Hevo for a spin?  SIGN UP and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable Hevo pricing that will help you choose the right plan for your business needs.

FAQs