MySQL Event Scheduler

This post briefly documents the process of using the MySQL Event Scheduler, which manages the scheduling and execution of events (tasks) that run according to a specified schedule.

Introduction

MySQL is an open-source relational database management system (RDBMS) that organizes data into one or more tables, where data types may be related to each other. These relationships help structure the data. SQL (Structured Query Language) is used by programmers to create, modify, and extract data from the relational database and control user access to the database.

Minimum Software Requirements

• MySQL
• MySQL Workbench (Optional)

Goals

1. Enable event scheduler
2. List existing events from a particular schema
3. Create table for example
4. Configure Event Scheduler
   • Create a one-time event
   • Create a one-time event with delayed start
   • Create a recurring-event with an end condition
5. Drop event scheduler

Enabling the Event Scheduler

To enable the event scheduler, use the following command:

SET GLOBAL event_scheduler = ON;

Listing Existing Events from a Particular Schema

To list existing events from a specific schema, use the following command:

SHOW EVENTS 
FROM SCHEMA_NAME;

Note: Events are automatically dropped when they expire

Create a Table for Example

First, create a sample table to use for the events:

CREATE TABLE event_scheduler_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    my_data VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL
);

Configuring the Event Scheduler

• One-time event

To create a one-time event that executes immediately, use the following command:

CREATE EVENT IF NOT EXISTS event_scheduler_test_event_01
ON SCHEDULE AT CURRENT_TIMESTAMP
ON COMPLETION PRESERVE -- keeps the event even after it has expired
DO
  INSERT INTO my_schema.event_scheduler_test(my_data,created_at)
  VALUES('My Event Scheduler Test Data 1',NOW());

Note: ON COMPLETION PRESERVE ensures the event is kept even after it expires.

• One-Time Event with Delayed Start

To create a one-time event that starts after a delay, use the following command:

CREATE EVENT event_scheduler_test_event_02
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
  INSERT INTO my_schema.event_scheduler_test(my_data,created_at)
  VALUES('My Event Scheduler Test Data 2',NOW());

• Recurring Event with End Condition

To create a recurring event that runs at regular intervals and stops after a specified duration, use the following command:

CREATE EVENT event_scheduler_test_event_03	
ON SCHEDULE EVERY 1 MINUTE					
STARTS CURRENT_TIMESTAMP					
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR	
DO
   INSERT INTO mis_dashboard.event_scheduler_test(my_data,created_at)
   VALUES('My Event Scheduler Test Data 3',NOW());

Explanation:

CREATE EVENT event_scheduler_test_event_03 :event name

ON SCHEDULE EVERY 1 MINUTE :The frequency at which the event runs.

STARTS CURRENT_TIMESTAMP :event start condition

ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR :event expiration period

• Dropping an Event Scheduler

To drop an event scheduler, use the following command:

DROP EVENT event_scheduler_test_event_03;

Additional Notes

To view the current processes, you can use the following command:

SHOW PROCESSLIST;

This command lists the active threads that the MySQL server is running, which can help you monitor and troubleshoot running events.

By following these steps, you can effectively manage and schedule tasks using the MySQL Event Scheduler, ensuring timely and automated execution of database tasks.

Conclusion

In conclusion, the MySQL Event Scheduler is a powerful tool for automating routine database tasks. By enabling and configuring events, you can schedule tasks such as data cleanup, reporting, or notifications without manual intervention. Understanding how to create one-time and recurring events with various configurations allows for flexible and efficient management of database operations. Remember to monitor active processes using SHOW PROCESSLIST; to ensure smooth execution of scheduled tasks and troubleshoot any issues that may arise. Harnessing the capabilities of the MySQL Event Scheduler can significantly enhance the automation and efficiency of your database management tasks.

Tags

• MySQL