MySQL Event Scheduler

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

Introduction

MySQL is an open-source relational database management system (RDBMS). A relational database organizes data into one or more data tables in which data types may be related to each other; these relations help structure the data. SQL is a language programmers use to create, modify and extract data from the relational database, as well as control user access to the database.

Minimum Software Requirements

• MySQL
• MySQL Workbench (Optional)

Goals

• Enable event scheduler
• List existing events from a particular schema
• Create table for example
• Congigure Event Scheduler
  • One-time event
  • One-time event with delayed start
  • Recurring-event with end condition
• Drop event scheduler

Enable event scheduler

SET GLOBAL event_scheduler = ON;

List existing events from a particular schema.

SHOW EVENTS 
FROM SCHEMA_NAME;

events are automatically dropped when it expires

Create table for example

create a sample table

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

• One-time event

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());

ON COMPLETION PRESERVE, keeps the event even after it has expired

• One-time event with delayed start

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

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());

CREATE EVENT event_scheduler_test_event_03 :event name

ON SCHEDULE EVERY 1 MINUTE :frequency

STARTS CURRENT_TIMESTAMP :event start condition

ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR :event expiration period

• Drop event scheduler

DROP EVENT event_scheduler_test_event_03;

Notes

SHOW PROCESSLIST;

Tags

• MySQL