Events and the Event Scheduler

Events are scheduled stored programs executed by MySQL's Event Scheduler — the database equivalent of cron jobs.

Concept

Events are scheduled stored programs executed by MySQL's Event Scheduler — the database equivalent of cron jobs. The scheduler is ON by default in MySQL 8.0.

Why It Matters

Events automate maintenance inside the database: purging old logs, refreshing summary tables, rolling partitions, and closing stale sessions — without external schedulers.

Syntax

CREATE EVENT [IF NOT EXISTS] ev_name
ON SCHEDULE {AT timestamp [+ INTERVAL ...] | EVERY n unit [STARTS ts] [ENDS ts]}
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON REPLICA]
[COMMENT 'text']
DO statement_or_BEGIN_END_block;

ALTER EVENT ev_name DISABLE;
DROP EVENT IF EXISTS ev_name;
SHOW EVENTS;
SET GLOBAL event_scheduler = ON;

Example

Events run as their DEFINER in the event's database, in the server time zone.

Errors are written to the error log; wrap logic to record failures in a log table if needed.

On replicas, events are created DISABLE ON REPLICA so they do not run twice.

SHOW VARIABLES LIKE 'event_scheduler';       -- ON

-- Refresh the emulated materialized view every hour
CREATE EVENT ev_refresh_monthly_revenue
ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP
DO
    REPLACE INTO mv_monthly_revenue (month, orders, revenue, refreshed_at)
    SELECT DATE_FORMAT(order_date, '%Y-%m'), COUNT(*), SUM(total_amount), NOW()
    FROM orders WHERE status <> 'CANCELLED'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m');

-- Purge CAN frames older than 90 days every night at 02:00, in batches
DELIMITER //
CREATE EVENT ev_purge_can_frames
ON SCHEDULE EVERY 1 DAY STARTS (CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR)
COMMENT 'Keep 90 days of raw CAN frames'
DO
BEGIN
    DECLARE v_rows INT DEFAULT 1;
    WHILE v_rows > 0 DO
        DELETE FROM can_frames WHERE captured_at < NOW() - INTERVAL 90 DAY LIMIT 10000;
        SET v_rows = ROW_COUNT();
    END WHILE;
END //
DELIMITER ;

-- One-time event
CREATE EVENT ev_close_campaign
ON SCHEDULE AT '2026-12-31 23:59:59'
ON COMPLETION NOT PRESERVE
DO UPDATE campaigns SET active = 0 WHERE campaign_id = 7;

SELECT event_name, status, interval_value, interval_field, last_executed
FROM information_schema.events WHERE event_sch
…

Expected Result

+----------------------------+---------+----------------+----------------+---------------------+
| EVENT_NAME                 | STATUS  | INTERVAL_VALUE | INTERVAL_FIELD | LAST_EXECUTED       |
+----------------------------+---------+----------------+----------------+---------------------+
| ev_close_campaign          | ENABLED | NULL           | NULL           | NULL                |
| ev_purge_can_frames        | ENABLED | 1              | DAY            | NULL                |
| ev_refresh_monthly_revenue | ENABLED | 1              | HOUR           | 2026-09-29 10:00:00 |
+----------------------------+---------+----------------+----------------+---------------------+

Common Mistakes

Scheduler disabled (event_scheduler = OFF) in config.

Huge single DELETEs in events causing lock waits and replica lag.

Overlapping executions of long events (add a lock via GET_LOCK()).

Best Practices

Batch work, log outcomes, and keep events idempotent.

Use GET_LOCK('ev_name', 0) to prevent overlap.

Document schedules; keep event DDL in version control.

Interview Relevance

Low-medium: "How do you schedule jobs in MySQL?", "What is the Event Scheduler?".

Practice Questions

Create an event that deletes salary_audit rows older than 2 years every Sunday.

Create a one-time event that archives orders at the end of the year.