How to Use Scheduled Events in MySQL

Introduction

In this tip, I will discuss how to create, alter, enable, disable and remove MySQL event in order to automate database manipulation tasks. I will create a sample schema with one table to show you how to automate a database manipulation task.

Background

MySQL Events or sometimes referred as Schedules Events are tasks that run according to a schedule. They are similar to the idea of the Unix crontab or Windows Task Schedule. MySQL Events are a named database object containing one or more SQL statements to be executed at one or more regular intervals, starts and ends at a specific date and time.

The Sample Schema and Table

Let’s have a sample schema called purchase which will contain only one table called purchase_status. This table will hold data about the purchase requests made to the purchasing (procurement) team of the Company. The following table gives a brief description about each column of our table.

In order to create, alter or drop MySQL Events, you need to have Event privilege for the schema in which the event is going to be created. I am connected as a root user and here is the SQL script to create the schema and the table.

CREATE SCHEMA `pr` ;

CREATE TABLE `pr`. `pr_status` (
  `pr_number` VARCHAR(10),
  `description` VARCHAR(45) ,
  `date_pr_submitted` DATE NOT NULL,
 `status` VARCHAR(45) DEFAULT "PR SUBMITTED",
  `delivery_date` DATE NULL,
  `days_taken` INT NULL,
  PRIMARY KEY (`pr_number`));

Configure the Event Scheduler

In order to execute scheduled events in MySQL, we have to check the value of the global system variable event_sheduler. This enables us to determine the status of the special event scheduler thread which is responsible to execute Events. The following command will show you whether the scheduler is running or not.

SHOW PROCESSLIST;

If the event_sheduler is not running, it will not be displayed in the process list.

Use the following command to enable the event_schedule:

SET GLOBAL event_scheduler = ON;

And check it again by running the show process list command again and you will see the event scheduler running and waiting for events.

Okay, now as you can see on the third row, the event scheduler is running and ready to run scheduled tasks. The next step is to create our event.

Create the Scheduled Event

Our scheduled event will run every day at 00:20:00 o’clock and will update the days taken (total lead time) column by calculating the date difference based on the following two situations.

• If the delivery date is not blank (0000-00-00), it will be the difference between the purchase request submission and delivery date.
• Otherwise, it will be the difference between the purchase request submission and the current date.

Here is the script to create the event:

CREATE EVENT update_days_taken
ON SCHEDULE EVERY 1 DAY STARTS '2015-06-21 00:20:00'
DO 
UPDATE `purchase`.`purchase_status` SET `days_taken`= 
CASE  
WHEN ` delivery_date ` ='0000-00-00' THEN datediff(CURDATE(),`date_pr_submitted `) 
ELSE datediff(`delivery_date`,` date_pr_submitted `)
END;

Let's see the purpose of each line one by one:

• The first line creates the event named update_days_taken, which uniquely identifies the event within the database schema.
• On the second line, the ON SCHEDULE clause determines when and how often the event executes.
• After the DO clause on the third line, the actual SQL statement to be executed by the event follows.
• This script calculates the total days taken by using case statement as per the description given above.
• The END statement marks the end of the actual script statement.

Alter Scheduled Events

In order to change any of the characteristics of an existing event, you simply use the ALTER EVENT statement. For example, you can alter the update_days_taken event to run every week instead of every day as follows:

ALTER EVENT update_days_taken
ON SCHEDULE EVERY 12 HOUR STARTS '2015-06-21 00:20:00'
DO 
UPDATE `purchase`.`purchase_status` SET `days_taken`= 
CASE  
WHEN ` date_pr_submitted ` ='0000-00-00' THEN datediff(CURDATE(),`date_pr_submitted `) 
ELSE datediff(`delivery_date`,` date_pr_submitted `)
END;

Disable or Enable Scheduled Events

Since ENABLE is the default for create and alter event statement, you don't need to specify explicitly. Here is the script to disable MySQL schedule events.

ALTER EVENT myevent
    DISABLE;

Drop Scheduled Events

You can stop the event from being active anymore and delete completely from the server using the DROP EVENT statement. Here is the statement to drop our sample scheduled event.

DROP EVENT IF EXISTS update_days_taken;

Conclusion

This is a simple way to create a scheduled event in MySQL server. There are so many alternative and advanced ways to create events. For example, you can call stored procedures instead of writing the SQL statement directly or you can have multiple SQL statements. There are a variety of alternatives for setting the schedule and interval of the event. For more details on the syntax, visit the official MySQL documentation website, where you can get rich information.

Reference

You can get details on MySQL Events on MySQL Developer Zone.