Event Scheduler Basics

Learning Focus

Use this lesson to understand the MySQL Event Scheduler — a built-in job scheduler that lets you run SQL statements automatically on a schedule, without external tools like cron.

Concept Overview

What Is the Event Scheduler?

The MySQL Event Scheduler is a built-in scheduler that runs SQL statements at specified times or intervals. Think of it as cron inside your database — you define events with SQL, and MySQL executes them according to the schedule.

-- This event runs every day at midnight
CREATE EVENT cleanup_sessions
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
    DELETE FROM sessions WHERE expires_at < NOW();

Why Use the Event Scheduler?

Benefit	Explanation
No external dependencies	No need for cron, systemd timers, or task schedulers
SQL-native	Write events in SQL; they have full access to your database
Transactional	Events participate in MySQL's transaction system
Self-contained	Backup the database and you backup the scheduled jobs too
Portable	Events move with your database to another server

Event Scheduler vs. Cron

Feature	Event Scheduler	Cron / Systemd Timers
Language	SQL	Shell commands
Setup	SQL DDL	OS configuration
Database access	Direct	Requires mysql client connection
Backed up with DB	✅	❌ (separate backup needed)
OS-level tasks	❌	✅ (file operations, network calls)
Monitoring	SHOW EVENTS	systemctl status, logs

Use the Event Scheduler for: database-internal tasks (cleanup, aggregation, archival) Use cron for: tasks that involve the OS, network, or external services

---

Basic Syntax & Rules

Enabling the Event Scheduler

The Event Scheduler is disabled by default in many MySQL installations:

-- Check current status
SHOW VARIABLES LIKE 'event_scheduler';

-- Enable for current session
SET GLOBAL event_scheduler = ON;

For permanent activation, add to my.cnf:

[mysqld]
event_scheduler = ON

Important: After a MySQL restart, the scheduler reverts to OFF unless configured in my.cnf.

One-Time Events

CREATE EVENT one_time_cleanup
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
    DELETE FROM temp_data WHERE created_at < NOW() - INTERVAL 24 HOUR;

This event runs once (1 hour from now) and then auto-drops itself.

Recurring Events

CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY  -- starts tomorrow midnight
DO
    DELETE FROM sessions WHERE expires_at < NOW();

Event with Multi-Statement Body

Use BEGIN ... END for multiple statements:

DELIMITER //

CREATE EVENT daily_maintenance
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
BEGIN
    -- Step 1: Clean expired sessions
    DELETE FROM sessions WHERE expires_at < NOW();

    -- Step 2: Log the cleanup
    INSERT INTO maintenance_log (task, executed_at)
    VALUES ('session_cleanup', NOW());
END //

DELIMITER ;

Key Schedule Options

Option	Example	Meaning
EVERY 1 HOUR	Hourly	Repeat every N units
EVERY 5 MINUTE	Every 5 minutes	Short intervals
EVERY 1 DAY	Daily	Common for maintenance
EVERY 1 WEEK	Weekly	Weekly reports
STARTS datetime	STARTS '2026-02-11 00:00:00'	When to start the schedule
ENDS datetime	ENDS '2026-12-31 23:59:59'	When to stop scheduling
ON COMPLETION PRESERVE	Keep after expiry	Event stays visible after ENDS
ON COMPLETION NOT PRESERVE	Auto-delete	Default — event disappears

---

Step-by-Step Examples

Example 1: Enable the Scheduler and Create a Cleanup Event

Step 1: Enable the event scheduler

SET GLOBAL event_scheduler = ON;
SHOW VARIABLES LIKE 'event_scheduler';

Variable_name	Value
event_scheduler	ON

Step 2: Create the event

DELIMITER //

CREATE EVENT evt_cleanup_expired_sessions
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
COMMENT 'Remove expired sessions every hour'
DO
BEGIN
    DELETE FROM sessions
    WHERE expires_at < NOW()
    LIMIT 10000;
END //

DELIMITER ;

Step 3: Verify the event was created

SHOW EVENTS FROM app_db\G

Expected output:

                  Db: app_db
                Name: evt_cleanup_expired_sessions
             Definer: root@localhost
           Time zone: SYSTEM
                Type: RECURRING
          Execute at: NULL
      Interval value: 1
      Interval field: HOUR
              Status: ENABLED

---

Example 2: One-Time Scheduled Task

Scenario: You need to archive data at a specific time (e.g., after business hours tonight).

CREATE EVENT evt_archive_old_orders
ON SCHEDULE AT '2026-02-10 23:00:00'
ON COMPLETION PRESERVE
COMMENT 'Archive orders older than 1 year'
DO
    INSERT INTO orders_archive
    SELECT * FROM orders
    WHERE order_date < NOW() - INTERVAL 1 YEAR;

ON COMPLETION PRESERVE keeps the event visible after execution so you can verify it ran.

---

Example 3: Managing Events

-- List all events
SHOW EVENTS;

-- View event definition
SHOW CREATE EVENT evt_cleanup_expired_sessions\G

-- Temporarily disable an event
ALTER EVENT evt_cleanup_expired_sessions DISABLE;

-- Re-enable it
ALTER EVENT evt_cleanup_expired_sessions ENABLE;

-- Change the schedule
ALTER EVENT evt_cleanup_expired_sessions
ON SCHEDULE EVERY 30 MINUTE;

-- Rename an event
ALTER EVENT old_name RENAME TO new_name;

-- Drop an event
DROP EVENT IF EXISTS evt_cleanup_expired_sessions;

---

Practical Use Cases

1. Session Cleanup

Delete expired sessions hourly to keep the sessions table small and fast.

2. Temporary Data Purging

Remove old records from staging, import, or temporary tables on a schedule.

3. Audit Log Retention

Purge audit records older than the retention period (e.g., 90 days) every night.

4. Report Pre-Generation

Aggregate daily/weekly metrics into summary tables during off-peak hours.

5. Health Monitoring

Insert a heartbeat row every minute; external monitoring checks for freshness.

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Event scheduler is OFF	Events exist but never run	SET GLOBAL event_scheduler = ON and add to my.cnf
Scheduler resets after restart	Events stop until manually re-enabled	Add event_scheduler = ON to my.cnf
Event runs as wrong user	Missing privileges for the operations	Create events as a user with sufficient privileges
No LIMIT on DELETE/UPDATE	Event locks the table for long periods	Always use LIMIT in maintenance events
No logging or monitoring	Can't tell if events are running	Insert into a log table inside the event body
Event and cron doing the same thing	Double-execution, wasted resources	Choose one mechanism per task

---

Best Practices

• Always add to my.cnf — event_scheduler = ON must survive restarts
• Use LIMIT — batch operations in events to avoid long locks
• Log execution — insert into a log table so you can verify events ran
• Name with evt_ prefix — distinguish events from procedures and triggers
• Add COMMENT — describe what the event does for future reference
• Use ON COMPLETION PRESERVE for one-time events — so you can verify they executed
• Monitor the scheduler — SHOW PROCESSLIST shows the event scheduler thread

---

Hands-On Practice

Exercise 1: Create a One-Time Event (Easy)

Create an event that runs 5 minutes from now and inserts a row into a test_log table with the current timestamp. Verify it ran.

Exercise 2: Hourly Cleanup (Medium)

Create an event evt_purge_temp_files that:

1. Runs every hour
2. Deletes rows from temp_uploads where created_at < NOW() - INTERVAL 24 HOUR
3. Limits to 5000 rows per run
4. Logs the deletion count into maintenance_log

Exercise 3: Verify Event Execution (Advanced)

Create a monitoring query that shows:

1. All events and their last execution time
2. Which events are enabled vs disabled
3. Any events that haven't run in the expected timeframe

---

Connection to Other Concepts

Related Concept	How It Connects
Stored Procedures	Events can CALL procedures for complex logic
Triggers	Triggers are event-driven (data changes); events are time-driven
Transactions	Event bodies can use transactions
Audit Logging	Events can maintain retention on audit tables
Date Arithmetic	NOW() - INTERVAL N DAY is the core of retention queries

What to Learn Next

• Recurring Maintenance Events — advanced patterns for production maintenance

---

Visual Learning Diagram

flowchart TD
    A["MySQL Server starts"] --> B{"event_scheduler = ON?"}
    B -->|No| C["❌ Events exist but never fire"]
    B -->|Yes| D["Event Scheduler thread running"]
    D --> E["Checks event schedules continuously"]
    E --> F{"Event due?"}
    F -->|Yes| G["Execute event body"]
    G --> H["Log completion"]
    H --> E
    F -->|No| E

    classDef warning fill:#ffc107,stroke:#333,color:#333
    classDef success fill:#28a745,stroke:#fff,color:#fff
    class C warning
    class G,H success

Quick Reference

-- Enable scheduler
SET GLOBAL event_scheduler = ON;

-- Create recurring event
CREATE EVENT evt_name
ON SCHEDULE EVERY 1 HOUR
DO DELETE FROM temp WHERE created_at < NOW() - INTERVAL 1 DAY LIMIT 5000;

-- Create one-time event
CREATE EVENT evt_once
ON SCHEDULE AT NOW() + INTERVAL 1 HOUR
ON COMPLETION PRESERVE
DO INSERT INTO log (msg) VALUES ('Ran once');

-- Manage events
SHOW EVENTS;
ALTER EVENT evt_name DISABLE;
ALTER EVENT evt_name ENABLE;
DROP EVENT IF EXISTS evt_name;

What's Next

• Recurring Maintenance Events