MySQL Scheduling

Managing Events

Switch on the event scheduler in the database

SET GLOBAL event_scheduler=on;

To set it on startup, it is necessary to update "/etc/mysql/my.cnf" to add the following:

[mysqld]
event_scheduler = on

Create event to periodically update the database

USE temperatures;
CREATE DEFINER = 'root'@'localhost' event
  IF NOT EXISTS snapshot
ON SCHEDULE EVERY 15 minute STARTS '2018-11-27 00:00:00'
DO
  INSERT INTO temperatures.minmax
    SELECT curdate(), round(min(outside_temp), 1) as min, round(max(outside_temp), 1) AS max
      FROM temperatures.readings WHERE datetime > curdate()
    ON DUPLICATE KEY UPDATE
      min = min, max = max;
Show and delete events

SHOW EVENTS;
SHOW CREATE EVENT snapshot;
DROP EVENT snapshot;
SHOW PROCESSLIST;

Note that code blocks may need the delimiter to change to allow the client to accept nested statements:

DELIMITER //

BEGIN
  SELECT * FROM mytable;
END //

To show all events:

SHOW EVENTS;

To show existing event scripts for a given event

SHOW CREATE EVENT snapshot;

And to alter event scripts

ALTER EVENT snapshot
DO
[new code];