Events Scheduler

-- 1) Turn on the scheduler (off by default on many installs)
SET GLOBAL event_scheduler = ON;
-- Persistent across restarts: my.cnf -> event_scheduler = ON

-- 2) Simple recurring event — runs every night at 02:30
DELIMITER //
CREATE EVENT IF NOT EXISTS evt_purge_old_audit
ON SCHEDULE EVERY 1 DAY STARTS '2026-06-19 02:30:00'
COMMENT 'Trim orders_audit to 90 days'
DO BEGIN
  DELETE FROM orders_audit
   WHERE changed_at < NOW() - INTERVAL 90 DAY
   LIMIT 100000;       -- bounded per run; loop if needed
END//
DELIMITER ;

-- 3) One-shot event — runs once at a specific time
CREATE EVENT evt_migrate_feature_x
ON SCHEDULE AT '2026-06-20 03:00:00'
DO ALTER TABLE orders ADD COLUMN feature_x_enabled TINYINT(1) DEFAULT 0;

-- 4) Recurring event with end date (auto-cleanup of itself)
CREATE EVENT evt_temp_repair
ON SCHEDULE EVERY 1 HOUR
STARTS '2026-06-18 00:00:00'
ENDS   '2026-06-25 00:00:00'
DO UPDATE products SET needs_review = 0 WHERE last_seen_at < NOW() - INTERVAL 1 HOUR;

-- 5) Materialised-view-style summary refresh
DELIMITER //
CREATE EVENT evt_refresh_daily_sales
ON SCHEDULE EVERY 5 MINUTE
DO BEGIN
  INSERT INTO daily_sales (day, total_cents, orders_count)
  SELECT DATE(created_at), SUM(total_cents), COUNT(*)
  FROM orders
  WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
  GROUP BY DATE(created_at)
  ON DUPLICATE KEY UPDATE
    total_cents = VALUES(total_cents),
    orders_count = VALUES(orders_count);
END//
DELIMITER ;

-- 6) Inspect what is scheduled
SHOW EVENTS;
SELECT EVENT_NAME, STATUS, EVENT_DEFINITION, INTERVAL_VALUE, INTERVAL_FIELD,
       LAST_EXECUTED, NEXT_EXECUTION
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = DATABASE();

-- 7) Disable / enable / drop
ALTER EVENT evt_purge_old_audit DISABLE;
ALTER EVENT evt_purge_old_audit ENABLE;
DROP EVENT IF EXISTS evt_purge_old_audit;

-- 8) Observability — failures go to the error log
-- Add structured logging by writing into a table at the end of the event body:
DELIMITER //
CREATE EVENT evt_purge_with_log
ON SCHEDULE EVERY 1 DAY
DO BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    INSERT INTO event_log (event_name, status, ran_at, message)
    VALUES ('evt_purge_with_log', 'error', NOW(), 'SQLEXCEPTION raised');
    RESIGNAL;
  END;

  DELETE FROM orders_audit WHERE changed_at < NOW() - INTERVAL 90 DAY LIMIT 100000;

  INSERT INTO event_log (event_name, status, ran_at)
  VALUES ('evt_purge_with_log', 'ok', NOW());
END//
DELIMITER ;

-- 9) When NOT to use MySQL events
-- - Anything that calls external services (HTTP, queues) — events have no retries
-- - Logic with rich error handling, alerting, dead-letter queues
-- - Cross-database orchestration
-- For those, use a proper scheduler (Sidekiq cron, Celery beat, Temporal, k8s CronJob)
-- and let MySQL focus on data work.

SET GLOBAL event_scheduler = ON;
CREATE EVENT cleanup
ON SCHEDULE EVERY 1 DAY
DO DELETE FROM sessions WHERE expires < NOW();