Manoj Swami

Posted on Jun 7

MySQL Events: Automate Database Tasks with Scheduled Jobs

#mysql

MySQL Events are like scheduled tasks that run automatically at specific times or intervals. Think of them as MySQL's built-in cron job system - they can perform database operations without any external trigger.

What Are MySQL Events?

Events are stored programs that execute at scheduled times. They're perfect for:

Regular data cleanup
Generating reports
Updating statistics
Archiving old data
Sending automated notifications

Prerequisites

Before working with events, ensure the Event Scheduler is enabled:

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

-- Enable Event Scheduler if it's OFF
SET GLOBAL event_scheduler = ON;

How to Access/View Events

1. View All Events

SHOW EVENTS;

2. View Events from Specific Database

SHOW EVENTS FROM your_database_name;

3. View Detailed Event Information

SELECT * FROM information_schema.EVENTS 
WHERE EVENT_SCHEMA = 'your_database_name';

4. View Specific Event Details

SHOW CREATE EVENT event_name;

Examples:

-- Example 1: See all events in current database
SHOW EVENTS;

-- Example 2: Check events in 'ecommerce' database
SHOW EVENTS FROM ecommerce;

-- Example 3: Get detailed info about all events
SELECT EVENT_NAME, EVENT_TYPE, EXECUTE_AT, INTERVAL_VALUE, 
       INTERVAL_FIELD, STATUS 
FROM information_schema.EVENTS;

-- Example 4: View how a specific event was created
SHOW CREATE EVENT daily_cleanup;

How to Create Events

Basic Syntax:

CREATE EVENT event_name
ON SCHEDULE schedule_type
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE]
DO
  sql_statement;

Schedule Types:

AT: Run once at specific time
EVERY: Run repeatedly at intervals

Examples:

Example 1: One-time Event

-- Delete old logs after 24 hours
CREATE EVENT delete_old_logs
ON SCHEDULE AT NOW() + INTERVAL 1 DAY
DO
  DELETE FROM activity_logs 
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

Example 2: Recurring Daily Event

-- Clean up expired sessions every day at 2 AM
CREATE EVENT daily_session_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
  DELETE FROM user_sessions 
  WHERE expires_at < NOW();

Example 3: Weekly Report Generation

-- Generate weekly sales report every Monday at 9 AM
CREATE EVENT weekly_sales_report
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-08 09:00:00'  -- Next Monday
DO
BEGIN
  INSERT INTO sales_reports (report_date, total_sales, order_count)
  SELECT 
    CURDATE() - INTERVAL 7 DAY,
    SUM(total_amount),
    COUNT(*)
  FROM orders 
  WHERE DATE(created_at) BETWEEN 
    CURDATE() - INTERVAL 14 DAY AND 
    CURDATE() - INTERVAL 7 DAY;
END;

Example 4: Archive Old Data Monthly

-- Archive orders older than 1 year, runs first day of each month
CREATE EVENT monthly_archive
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-02-01 01:00:00'
DO
BEGIN
  -- Move old orders to archive table
  INSERT INTO orders_archive 
  SELECT * FROM orders 
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

  -- Delete archived orders from main table
  DELETE FROM orders 
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
END;

How to Update Events

Use ALTER EVENT to modify existing events:

Examples:

Example 1: Change Schedule

-- Change daily cleanup to run every 2 days
ALTER EVENT daily_session_cleanup
ON SCHEDULE EVERY 2 DAY;

Example 2: Modify Event Action

-- Update the deletion criteria
ALTER EVENT delete_old_logs
DO
  DELETE FROM activity_logs 
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 60 DAY);  -- Changed from 30 to 60 days

Example 3: Enable/Disable Event

-- Temporarily disable an event
ALTER EVENT weekly_sales_report DISABLE;

-- Re-enable the event
ALTER EVENT weekly_sales_report ENABLE;

Example 4: Change Event Name and Schedule

-- Rename and reschedule event
ALTER EVENT monthly_archive
RENAME TO quarterly_archive
ON SCHEDULE EVERY 3 MONTH;

How to Delete Events

Use DROP EVENT to remove events permanently:

Examples:

Example 1: Delete Specific Event

DROP EVENT delete_old_logs;

Example 2: Delete Event if it Exists

DROP EVENT IF EXISTS daily_session_cleanup;

Example 3: Delete Multiple Events (one by one)

DROP EVENT weekly_sales_report;
DROP EVENT monthly_archive;
DROP EVENT daily_session_cleanup;

Example 4: Delete Event from Specific Database

-- First switch to the database
USE ecommerce_db;
DROP EVENT product_price_update;

Event Status and Completion Options

ON COMPLETION Options:

PRESERVE: Keep event definition after execution (for one-time events)
NOT PRESERVE: Remove event definition after execution (default)

Status Options:

ENABLE: Event is active and will execute
DISABLE: Event exists but won't execute
DISABLE ON SLAVE: For replication setups

Example with All Options:

CREATE EVENT comprehensive_cleanup
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-15 03:00:00'
ENDS '2024-12-31 23:59:59'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Weekly cleanup of temporary data'
DO
BEGIN
  DELETE FROM temp_uploads WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
  DELETE FROM temp_cache WHERE expires_at < NOW();
  OPTIMIZE TABLE temp_uploads;
END;

Common Use Cases and Best Practices

1. Data Cleanup Events

-- Remove expired password reset tokens
CREATE EVENT cleanup_password_tokens
ON SCHEDULE EVERY 1 HOUR
DO
  DELETE FROM password_resets 
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR);

2. Statistics Updates

-- Update user activity statistics daily
CREATE EVENT update_user_stats
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 01:30:00'
DO
BEGIN
  UPDATE user_profiles up
  SET last_activity_days = DATEDIFF(NOW(), 
    (SELECT MAX(login_time) FROM user_sessions 
     WHERE user_id = up.id)
  );
END;

3. Backup Triggers

-- Create daily backup indicator
CREATE EVENT daily_backup_flag
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 23:45:00'
DO
  INSERT INTO backup_schedule (backup_date, status) 
  VALUES (CURDATE(), 'pending');

4. Data Validation

-- Check for data inconsistencies weekly
CREATE EVENT data_validation_check
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-07 05:00:00'
DO
BEGIN
  INSERT INTO validation_log (check_date, issue_type, count)
  SELECT NOW(), 'orphaned_orders', COUNT(*)
  FROM orders o
  LEFT JOIN customers c ON o.customer_id = c.id
  WHERE c.id IS NULL;
END;

Important Notes for Beginners

Time Zones

Events use the server's time zone. Be careful when scheduling:

-- Specify exact timestamp to avoid confusion
STARTS '2024-06-15 14:30:00'

Error Handling

Events run silently. Check the error log if they don't work:

-- Enable general log to see event execution
SET GLOBAL general_log = 'ON';

Testing Events

Test your event logic first with regular queries:

-- Test the DELETE statement before creating the event
SELECT COUNT(*) FROM activity_logs 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

Performance Considerations

Avoid heavy operations during peak hours
Use LIMIT for large data operations
Consider breaking big tasks into smaller chunks

Monitoring Events

-- Check when events last ran
SELECT EVENT_NAME, LAST_EXECUTED, STATUS 
FROM information_schema.EVENTS 
WHERE EVENT_SCHEMA = DATABASE();

Happy Coding!