MySQL & Database Design

Events & Scheduled Tasks

13 min Lesson 23 of 40

Events & Scheduled Tasks

MySQL Events are scheduled tasks that run automatically at specified times or intervals. They're perfect for database maintenance, report generation, data archiving, and other recurring operations. Think of them as cron jobs within MySQL itself.

What are MySQL Events?

Events are named database objects that execute SQL statements at scheduled times. They run independently in the background without requiring external scripts or applications.

Key Concept: Events require the Event Scheduler to be enabled. They're ideal for automated database maintenance tasks, data cleanup, and scheduled reporting.

Enabling the Event Scheduler

First, ensure the Event Scheduler is running:

-- Check if Event Scheduler is enabled
SHOW VARIABLES LIKE 'event_scheduler';

-- Enable Event Scheduler (current session)
SET GLOBAL event_scheduler = ON;

-- Disable Event Scheduler
SET GLOBAL event_scheduler = OFF;

-- Check running events
SHOW PROCESSLIST;

Tip: To enable Event Scheduler permanently, add `event_scheduler=ON` to your my.cnf or my.ini configuration file.

Creating One-Time Events

One-time events execute once at a specific datetime:

-- Simple one-time event
CREATE EVENT cleanup_temp_data
ON SCHEDULE AT '2024-12-31 23:59:59'
DO
    DELETE FROM temp_sessions WHERE created_at < DATE_SUB(NOW(), INTERVAL 24 HOUR);

-- Event that executes 1 hour from now
CREATE EVENT send_daily_report
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
BEGIN
    INSERT INTO reports (report_type, generated_at, data)
    SELECT
        'daily_sales',
        NOW(),
        JSON_OBJECT(
            'total_orders', COUNT(*),
            'total_revenue', SUM(total_amount)
        )
    FROM orders
    WHERE DATE(order_date) = CURDATE();
END;

-- Event with specific date and time
CREATE EVENT year_end_summary
ON SCHEDULE AT '2024-12-31 00:00:00'
DO
    CALL GenerateAnnualReport();

Creating Recurring Events

Recurring events execute repeatedly at specified intervals:

-- Event that runs every day
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
BEGIN
    -- Delete old logs
    DELETE FROM activity_logs
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

    -- Archive old orders
    INSERT INTO archived_orders
    SELECT * FROM orders
    WHERE status = 'completed'
    AND completed_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

    DELETE FROM orders
    WHERE order_id IN (
        SELECT order_id FROM archived_orders
    );
END;

-- Event that runs every hour
CREATE EVENT hourly_stats_update
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
    UPDATE statistics
    SET
        active_users = (SELECT COUNT(*) FROM users WHERE last_active > DATE_SUB(NOW(), INTERVAL 1 HOUR)),
        hourly_revenue = (SELECT SUM(total_amount) FROM orders WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)),
        updated_at = NOW()
    WHERE stat_type = 'hourly';

-- Event that runs every 30 minutes
CREATE EVENT update_trending_products
ON SCHEDULE EVERY 30 MINUTE
DO
    CALL CalculateTrendingProducts();

Event Time Units

-- Available time intervals
EVERY 1 SECOND
EVERY 5 MINUTE
EVERY 1 HOUR
EVERY 1 DAY
EVERY 1 WEEK
EVERY 1 MONTH
EVERY 1 QUARTER
EVERY 1 YEAR

-- Compound intervals
EVERY '1:30' HOUR_MINUTE    -- Every 1 hour 30 minutes
EVERY '1 2' DAY_HOUR         -- Every 1 day 2 hours
EVERY '3 0:0:0' DAY_SECOND   -- Every 3 days

Event Start and End Times

Control when events start and stop executing:

-- Event with start time
CREATE EVENT promotional_emails
ON SCHEDULE EVERY 1 DAY
STARTS '2024-06-01 08:00:00'
DO
    CALL SendPromotionalEmails();

-- Event with start and end times
CREATE EVENT summer_sale_reminder
ON SCHEDULE EVERY 1 DAY
STARTS '2024-06-01 09:00:00'
ENDS '2024-08-31 23:59:59'
DO
BEGIN
    UPDATE promotions
    SET emails_sent = emails_sent + 1
    WHERE promo_code = 'SUMMER2024';

    -- Send reminder logic here
END;

-- Event that runs for 30 days then stops
CREATE EVENT trial_period_check
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 30 DAY
DO
    CALL CheckTrialExpirations();

Event Preservation and Completion

-- Drop event after completion (default for one-time events)
CREATE EVENT temp_cleanup
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
ON COMPLETION NOT PRESERVE
DO
    DELETE FROM temp_data WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY);

-- Keep event after completion
CREATE EVENT important_task
ON SCHEDULE AT '2024-12-31 23:59:59'
ON COMPLETION PRESERVE
DO
    CALL CriticalYearEndTask();

-- Preserve recurring event even after ENDS time
CREATE EVENT seasonal_task
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 00:00:00'
ENDS '2024-12-31 23:59:59'
ON COMPLETION PRESERVE
DO
    CALL SeasonalMaintenance();

Event Status Management

-- Create disabled event (won't run until enabled)
CREATE EVENT backup_database
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 03:00:00'
DISABLE
DO
    CALL BackupAllTables();

-- Enable/disable events
ALTER EVENT backup_database ENABLE;
ALTER EVENT backup_database DISABLE;

-- Temporarily disable an event
ALTER EVENT daily_cleanup DISABLE ON SLAVE;

Modifying Events

-- Change event schedule
ALTER EVENT daily_cleanup
ON SCHEDULE EVERY 2 DAY;

-- Change event action
ALTER EVENT daily_cleanup
DO
BEGIN
    DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 180 DAY);
    OPTIMIZE TABLE logs;
END;

-- Rename event
ALTER EVENT old_event_name
RENAME TO new_event_name;

-- Change multiple properties
ALTER EVENT update_statistics
ON SCHEDULE EVERY 15 MINUTE
STARTS CURRENT_TIMESTAMP
ENABLE
COMMENT 'Updated to run more frequently';

Real-World Event Examples

-- Automated database maintenance
CREATE EVENT weekly_maintenance
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-07 03:00:00'  -- Sunday at 3 AM
DO
BEGIN
    -- Optimize tables
    OPTIMIZE TABLE orders, order_items, customers, products;

    -- Analyze tables for query optimization
    ANALYZE TABLE orders, customers;

    -- Clean up old temp files
    DELETE FROM temp_uploads WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

    -- Log maintenance
    INSERT INTO maintenance_log (task, completed_at)
    VALUES ('weekly_maintenance', NOW());
END;

-- Session cleanup
CREATE EVENT cleanup_expired_sessions
ON SCHEDULE EVERY 30 MINUTE
DO
BEGIN
    DELETE FROM sessions
    WHERE last_activity < DATE_SUB(NOW(), INTERVAL 2 HOUR);

    DELETE FROM password_resets
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR);
END;

-- Customer engagement tracking
CREATE EVENT calculate_customer_metrics
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 01:00:00'
DO
BEGIN
    -- Update customer lifetime value
    UPDATE customers c
    SET
        lifetime_value = (
            SELECT COALESCE(SUM(total_amount), 0)
            FROM orders
            WHERE customer_id = c.customer_id
        ),
        last_purchase_date = (
            SELECT MAX(order_date)
            FROM orders
            WHERE customer_id = c.customer_id
        );

    -- Calculate churn risk
    UPDATE customers
    SET churn_risk = CASE
        WHEN DATEDIFF(NOW(), last_purchase_date) > 180 THEN 'high'
        WHEN DATEDIFF(NOW(), last_purchase_date) > 90 THEN 'medium'
        ELSE 'low'
    END
    WHERE last_purchase_date IS NOT NULL;
END;

-- Data archiving
CREATE EVENT archive_old_data
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-01-01 00:00:00'
DO
BEGIN
    DECLARE archived_count INT DEFAULT 0;

    -- Archive completed orders older than 2 years
    INSERT INTO archived_orders
    SELECT * FROM orders
    WHERE status = 'completed'
    AND completed_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

    SET archived_count = ROW_COUNT();

    -- Delete archived orders from main table
    DELETE FROM orders
    WHERE status = 'completed'
    AND completed_at < DATE_SUB(NOW(), INTERVAL 2 YEAR);

    -- Log archiving
    INSERT INTO archive_log (table_name, records_archived, archived_at)
    VALUES ('orders', archived_count, NOW());
END;

-- Report generation
CREATE EVENT generate_monthly_reports
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-01-01 06:00:00'
DO
BEGIN
    -- Sales report
    INSERT INTO monthly_reports (report_type, period, data, generated_at)
    SELECT
        'sales',
        DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m'),
        JSON_OBJECT(
            'total_orders', COUNT(*),
            'total_revenue', SUM(total_amount),
            'avg_order_value', AVG(total_amount),
            'top_products', (
                SELECT JSON_ARRAYAGG(
                    JSON_OBJECT('product_id', product_id, 'quantity', SUM(quantity))
                )
                FROM order_items
                WHERE order_id IN (
                    SELECT order_id FROM orders
                    WHERE MONTH(order_date) = MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH))
                )
                GROUP BY product_id
                ORDER BY SUM(quantity) DESC
                LIMIT 10
            )
        ),
        NOW()
    FROM orders
    WHERE MONTH(order_date) = MONTH(DATE_SUB(NOW(), INTERVAL 1 MONTH))
    AND YEAR(order_date) = YEAR(DATE_SUB(NOW(), INTERVAL 1 MONTH));
END;

Managing Events

-- List all events in current database
SHOW EVENTS;

-- Show events with filters
SHOW EVENTS WHERE Name LIKE '%cleanup%';

-- View event definition
SHOW CREATE EVENT daily_cleanup;

-- Drop an event
DROP EVENT IF EXISTS daily_cleanup;

-- Query event information from information_schema
SELECT
    EVENT_NAME,
    EVENT_DEFINITION,
    INTERVAL_VALUE,
    INTERVAL_FIELD,
    STATUS,
    STARTS,
    ENDS,
    LAST_EXECUTED
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'your_database'
ORDER BY EVENT_NAME;

Event Error Handling

DELIMITER //

CREATE EVENT safe_cleanup
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        -- Log error
        INSERT INTO event_errors (event_name, error_message, occurred_at)
        VALUES ('safe_cleanup', 'An error occurred during cleanup', NOW());
    END;

    -- Cleanup operations
    DELETE FROM temp_data WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
    DELETE FROM old_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

    -- Log success
    INSERT INTO event_log (event_name, status, executed_at)
    VALUES ('safe_cleanup', 'success', NOW());
END //

DELIMITER ;

Monitoring Events

-- Create event execution log
CREATE TABLE event_execution_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(64),
    execution_time TIMESTAMP,
    duration_seconds INT,
    rows_affected INT,
    status VARCHAR(20),
    error_message TEXT
);

-- Event with logging
CREATE EVENT monitored_cleanup
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    DECLARE start_time TIMESTAMP DEFAULT NOW();
    DECLARE affected_rows INT DEFAULT 0;

    -- Perform cleanup
    DELETE FROM temp_sessions WHERE created_at < DATE_SUB(NOW(), INTERVAL 2 HOUR);
    SET affected_rows = ROW_COUNT();

    -- Log execution
    INSERT INTO event_execution_log (
        event_name,
        execution_time,
        duration_seconds,
        rows_affected,
        status
    )
    VALUES (
        'monitored_cleanup',
        start_time,
        TIMESTAMPDIFF(SECOND, start_time, NOW()),
        affected_rows,
        'completed'
    );
END;

Practice Exercise:

Create a Complete Scheduled Maintenance System:

Create an event 'daily_analytics' that runs every day at 2 AM to:
- Calculate daily sales totals
- Update product popularity rankings
- Identify inactive customers (no orders in 60 days)
- Generate a summary report in a reports table
Create an event 'hourly_cache_refresh' that runs every hour to update cached statistics
Create a one-time event that runs at month-end to archive last month's data
Add proper error handling and logging to all events

Best Practices

✓ Always enable Event Scheduler in production
✓ Schedule maintenance during low-traffic periods
✓ Use transactions for data modifications
✓ Add error handling to events
✓ Log event executions for monitoring
✓ Test events thoroughly before deployment
✓ Use PRESERVE for important recurring events
✓ Document event purposes and schedules
✓ Monitor event execution times
✗ Don't run heavy operations during peak hours
✗ Avoid events that lock tables for long periods
✗ Don't create too many simultaneous events

Summary

In this lesson, you learned:

Events are scheduled tasks that run automatically in MySQL
One-time events execute once at a specific datetime
Recurring events execute repeatedly at specified intervals
Event Scheduler must be enabled for events to run
Events can be enabled, disabled, modified, or dropped
STARTS and ENDS control event execution windows
Events are perfect for maintenance, cleanup, and reporting
Always add error handling and logging to events

Next Up: In the next lesson, we'll explore Cursors and Advanced Procedure Patterns for complex data processing!