Definition:

The MySQL Event Scheduler is a process which runs in the background and constantly looks for events to execute.

DB Outline & Context of the example:

  • Suppose, we are monitoring traffic intensity in our living city Dhaka, Bangladesh. Android, IoS applications might help people in this case. Using these applications, people can see the traffic intensity of the current time. So, that people may take decisions accordingly. These applications will be using a database table where data are inserted from
  • These applications will be using a database table where data are inserted from the application site. When a user wants to see the traffic intensity, based on the data stored in the table a decision will be given or we may show the user exactly the data submitted by various users. So,  prepare a table named tbl_traffic_info  with following the block of codes in Mysql:
CREATE TABLE `tbl_traffic_info` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `place_from` VARCHAR(150) NOT NULL,
  `place_towards` VARCHAR(150) NOT NULL,
  `intensity` ENUM('low','medium','high') NOT NULL,
  `report_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `posted_by` VARCHAR(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
  • So, what if our application is very popular and people use it frequently to post the updates of the roads. There will be tons of data inserted. In this situation, more than 1-hour old data might be of no use as we are focusing only on the data those are not more than 1 hour old in the table.
  • So we can delete the data which are more than 1 hour old. But how? We can do it manually, but for that, we need someone who regularly deletes data after every 1 hour. What if our office is closed? What if that guy is on vacation?
  • Our table might look like following initially :

Solution:

  • In this case, we can use MySQL Event Scheduler which will do this for us. We just need to configure it once and the rest will be taken by our database.
  • Scheduler is by default not activated in Mysql. You have to activate it first with the following command:
SET GLOBAL event_scheduler = ON;
  • you can see the process lists with the command
     SHOW PROCESSLIST 
  • Let’s create an event scheduler erase_history like following :

DELIMITER |

CREATE EVENT `erase_history`
 ON SCHEDULE EVERY 1 HOUR
 DO
 BEGIN
 DELETE FROM `tbl_traffic_info` WHERE report_time < ( NOW() - INTERVAL 1 HOUR );
 END |

DELIMITER ;

  • erase_history will run in every 1 Hour and will delete the data that are more than 1 hour. After the scheduler runs, old data will be deleted automatically like the following:
  • In this way, we can focus only on the data that are between 1 hour.
  • You can drop the scheduler event with the following command:
     DROP EVENT `erase_history`