MySQL Events

Wed, 08 Jul 2020 13:07 UTC by garethbrown

The following scripts demonstrate how to set up and manage events in MySQL. This is a very useful feature which can allow a DBA to run tasks that might otherwise require a developer to create an external service to run.

Table for testing ...

-- -----------------------------------------------------
-- Table `test_event`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test_event`;

CREATE TABLE IF NOT EXISTS `test_event` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `created_date_utc` DATETIME NOT NULL,
  `expires_date_utc` DATETIME NOT NULL,
  `user_id` BIGINT(20) NOT NULL,
  `test_val` BIGINT(20) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8;

Check that event processing is enabled

SET GLOBAL event_scheduler = ON;

Create a recurring event

CREATE EVENT event_test_event_delete_expired
  ON SCHEDULE
    EVERY 30 SECOND
    STARTS (TIMESTAMP(CURRENT_DATE))
  DO
    -- Allow 30 seconds for clock skew
  
	DELETE FROM test_event WHERE expires_date_utc < UTC_TIMESTAMP - INTERVAL 30 SECOND;

Testing - insert and verify data

INSERT INTO test_event (created_date_utc, expires_date_utc, user_id, test_val) VALUES (UTC_TIMESTAMP, UTC_TIMESTAMP + INTERVAL 30 SECOND, 1, 1);

SELECT * FROM test_event; -- run later to check data has been deleted    

The following will only show events that are due to occur

SHOW PROCESSLIST;

e.g.

4	event_scheduler	localhost		Daemon	4	Waiting for next activation	

    

Alternatively, run a one time event (no recurrence)

CREATE EVENT event_test_event_delete_expired
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 30 SECOND
DO
  DELETE FROM test_event WHERE expires_date_utc < UTC_TIMESTAMP - INTERVAL 30 SECOND;

Dropping events

DROP EVENT event_test_event_delete_expired

Useful links:

https://stackoverflow.com/questions/3070277/mysql-event-scheduler-on-a-specific-time-everyday

http://www.dbrnd.com/2016/11/mysql-how-to-create-a-sql-job-or-event-scheduler-for-scheduling-the-database-task-to-execute-automatically/


The information on this site is provided “AS IS” and without warranties of any kind either
express or implied. To the fullest extent permissible pursuant to applicable laws, the author disclaims all warranties, express or implied, including, but not limited to, implied warranties of merchantability, non-infringement and suitability for a particular purpose.

UI block loader
One moment please ...