MySQL Event Scheduler: Panduan Lengkap untuk Scheduled Tasks dan Automation

Event Scheduler adalah fitur built-in MySQL untuk menjalankan scheduled tasks secara otomatis. Ini adalah alternatif untuk cron jobs yang lebih terintegrasi dengan database operations. Setelah mengimplementasikan ratusan events untuk maintenance dan reporting, saya akan berbagi best practices.

Enable Event Scheduler

1. Enable Globally

-- Check status
SHOW VARIABLES LIKE 'event_scheduler';
-- Enable
SET GLOBAL event_scheduler = ON;
-- Permanent (my.cnf)
[mysqld]
event_scheduler = ON

2. Verify Scheduler

-- Check if running
SHOW PROCESSLIST;
-- Look for: User=event_scheduler, Command=Daemon
-- Check events
SHOW EVENTS;

Basic Event Syntax

1. One-Time Event

-- Event yang jalan sekali
CREATE EVENT delete_old_logs
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
  DELETE FROM system_logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

2. Recurring Event

-- Event yang berulang setiap hari
CREATE EVENT daily_report_generation
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
DO
  INSERT INTO reports_log (report_date, status)
  VALUES (CURDATE(), 'generated');

3. Event dengan Specific Time

-- Event jalan jam 2 pagi setiap hari
CREATE EVENT nightly_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 2 HOUR
DO
  CALL cleanup_old_data();

Event Management

1. Create Event dengan Options

CREATE EVENT IF NOT EXISTS archive_old_orders
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-01-01 00:00:00'
ENDS '2025-12-31 23:59:59'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Weekly archive of completed orders older than 90 days'
DO
BEGIN
  -- Archive orders
  INSERT INTO orders_archive 
  SELECT * FROM orders 
  WHERE status = 'completed' 
    AND created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
-- Delete dari main table
DELETE FROM orders 
WHERE status = 'completed' 
AND created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
END;

2. Alter Event

-- Change schedule
ALTER EVENT archive_old_orders
ON SCHEDULE EVERY 1 DAY;
-- Disable event
ALTER EVENT archive_old_orders DISABLE;
-- Enable event
ALTER EVENT archive_old_orders ENABLE;
-- Rename event
ALTER EVENT archive_old_orders RENAME TO daily_archive_old_orders;

3. Drop Event

-- Drop single event
DROP EVENT IF EXISTS archive_old_orders;
-- Drop multiple
DROP EVENT IF EXISTS event1, event2, event3;

Common Use Cases

1. Data Cleanup

CREATE EVENT cleanup_expired_sessions
ON SCHEDULE EVERY 1 HOUR
DO
  DELETE FROM user_sessions 
  WHERE last_activity < DATE_SUB(NOW(), INTERVAL 24 HOUR);

2. Maintenance Tasks

CREATE EVENT optimize_tables
ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
  -- Optimize fragmented tables
  OPTIMIZE TABLE large_table1;
  OPTIMIZE TABLE large_table2;
-- Analyze tables untuk statistics
ANALYZE TABLE large_table1;
ANALYZE TABLE large_table2;
-- Log maintenance
INSERT INTO maintenance_log (task, completed_at)
VALUES ('weekly_optimize', NOW());
END;

3. Data Aggregation

CREATE EVENT daily_sales_summary
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 1 HOUR
DO
BEGIN
  -- Clear previous day's summary
  DELETE FROM daily_summary WHERE summary_date = CURDATE() - INTERVAL 1 DAY;
-- Insert new summary
INSERT INTO daily_summary (summary_date, total_sales, order_count, avg_order_value)
SELECT 
DATE(created_at) as summary_date,
SUM(total_amount) as total_sales,
COUNT(*) as order_count,
AVG(total_amount) as avg_order_value
FROM orders
WHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAY
AND status = 'completed'
GROUP BY DATE(created_at);
END;

4. Notification/Alert Generation

CREATE EVENT check_low_inventory
ON SCHEDULE EVERY 4 HOUR
DO
BEGIN
  INSERT INTO inventory_alerts (product_id, product_name, current_stock, alert_date)
  SELECT 
    id,
    name,
    stock_quantity,
    NOW()
  FROM products
  WHERE stock_quantity <= reorder_level
    AND id NOT IN (
      SELECT product_id 
      FROM inventory_alerts 
      WHERE alert_date > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    );
END;

5. User Activity Reporting

CREATE EVENT generate_user_activity_report
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 06:00:00'
DO
BEGIN
  DECLARE yesterday DATE;
  SET yesterday = CURDATE() - INTERVAL 1 DAY;
-- Daily active users
INSERT INTO user_activity_reports (report_date, metric_name, metric_value)
SELECT 
yesterday,
'daily_active_users',
COUNT(DISTINCT user_id)
FROM user_activities
WHERE DATE(activity_time) = yesterday;
-- New registrations
INSERT INTO user_activity_reports (report_date, metric_name, metric_value)
SELECT 
yesterday,
'new_registrations',
COUNT(*)
FROM users
WHERE DATE(created_at) = yesterday;
END;

Advanced Event Features

1. Conditional Logic dalam Events

CREATE EVENT conditional_cleanup
ON SCHEDULE EVERY 1 DAY
DO
BEGIN
  DECLARE v_row_count INT;
-- Check jika ada data yang perlu dihapus
SELECT COUNT(*) INTO v_row_count
FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
IF v_row_count > 1000 THEN
-- Delete dalam batch untuk avoid long transaction
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
LIMIT 1000;
-- Log action
INSERT INTO event_log (event_name, action, row_count)
VALUES ('conditional_cleanup', 'deleted_old_logs', v_row_count);
END IF;
END;
2. Error Handling dalam Events
CREATE EVENT safe_maintenance_task
ON SCHEDULE EVERY 1 WEEK
DO
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- Log error
    INSERT INTO event_errors (event_name, error_time, error_message)
    VALUES ('safe_maintenance_task', NOW(), 'SQL Exception occurred');
-- Continue dengan tindakan recovery jika perlu
END;
-- Main task
CALL maintenance_procedure();
-- Log success
INSERT INTO event_log (event_name, status)
VALUES ('safe_maintenance_task', 'completed');
END;
3. Events dengan Stored Procedures
-- Create stored procedure
DELIMITER $$
CREATE PROCEDURE ArchiveOldRecords()
BEGIN
START TRANSACTION;
-- Archive data
INSERT INTO archive_table
SELECT * FROM main_table
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
-- Delete dari main table
DELETE FROM main_table
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
COMMIT;
END$$
DELIMITER ;
-- Create event yang call procedure
CREATE EVENT archive_old_records_event
ON SCHEDULE EVERY 1 MONTH
STARTS '2024-02-01 00:00:00'
DO
CALL ArchiveOldRecords();
Monitoring dan Logging
1. Event Status Monitoring
-- Show all events dengan status
SELECT 
  EVENT_NAME,
  EVENT_SCHEMA,
  STATUS,
  EVENT_TYPE,
  EXECUTE_AT,
  INTERVAL_VALUE,
  INTERVAL_FIELD,
  STARTS,
  ENDS,
  LAST_EXECUTED,
  CREATED,
  LAST_ALTERED
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'your_database';
2. Create Event Logging Table
CREATE TABLE event_execution_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_name VARCHAR(100),
  execution_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status VARCHAR(20),
  duration_ms INT,
  rows_affected INT,
  error_message TEXT,
  INDEX idx_event_time (event_name, execution_time)
) ENGINE=InnoDB;
3. Events dengan Built-in Logging
DELIMITER $$
CREATE PROCEDURE LoggedMaintenance()
BEGIN
DECLARE start_time TIMESTAMP DEFAULT NOW();
DECLARE v_rows INT DEFAULT 0;
DECLARE v_status VARCHAR(20) DEFAULT 'success';
DECLARE v_error TEXT DEFAULT NULL;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET v_status = 'error';
GET DIAGNOSTICS CONDITION 1
v_error = MESSAGE_TEXT;
INSERT INTO event_execution_log 
  (event_name, status, duration_ms, error_message)
VALUES 
  ('LoggedMaintenance', v_status, 
   TIMESTAMPDIFF(MICROSECOND, start_time, NOW()) / 1000, 
   v_error);

RESIGNAL;
END;
-- Main logic
DELETE FROM temp_table WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY);
SET v_rows = ROW_COUNT();
-- Log success
INSERT INTO event_execution_log
(event_name, status, duration_ms, rows_affected)
VALUES
('LoggedMaintenance', v_status,
TIMESTAMPDIFF(MICROSECOND, start_time, NOW()) / 1000,
v_rows);
END$$
DELIMITER ;
Event Scheduler Best Practices
1. Design Principles
-- Keep events simple
-- Call stored procedures untuk complex logic
-- Use transactions untuk data consistency
-- Implement error handling
-- Log execution untuk monitoring
2. Performance Considerations
-- Avoid long-running events selama peak hours
CREATE EVENT nightly_batch_job
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 3 HOUR  -- 3 AM
DO
  CALL batch_process();  -- Long running task
-- Use batch operations untuk large datasets
CREATE EVENT batch_cleanup
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
-- Process dalam chunks
REPEAT
DELETE FROM large_table 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH)
LIMIT 1000;
UNTIL ROW_COUNT() = 0 END REPEAT;
END;
3. Security
-- Grant privileges untuk event management
GRANT EVENT ON mydb.* TO 'event_manager'@'localhost';
-- Revoke jika tidak perlu
REVOKE EVENT ON mydb.* FROM 'event_manager'@'localhost';
-- Definer security
CREATE EVENT secure_event
ON SCHEDULE EVERY 1 DAY
DEFINER = 'admin'@'localhost'
DO
-- Runs dengan admin privileges
DELETE FROM sensitive_table WHERE expired = 1;
Troubleshooting
1. Event Not Running
-- Check scheduler status
SHOW VARIABLES LIKE 'event_scheduler';
-- Check event status
SHOW EVENTS WHERE Name = 'your_event';
-- Check last execution
SELECT LAST_EXECUTED FROM information_schema.EVENTS 
WHERE EVENT_NAME = 'your_event';
-- Enable jika disabled
ALTER EVENT your_event ENABLE;
2. Events Stuck atau Hanging
-- Check processlist
SHOW PROCESSLIST;
-- Look for event_scheduler atau event processes
-- Kill hanging process jika perlu
KILL <process_id>;
-- Restart event scheduler
SET GLOBAL event_scheduler = OFF;
SET GLOBAL event_scheduler = ON;
3. Time Zone Issues
-- Check current timezone
SELECT @@global.time_zone, @@session.time_zone;
-- Set timezone untuk events
SET GLOBAL time_zone = '+07:00';  -- Jakarta time
-- Atau dalam event
CREATE EVENT timezone_aware_event
ON SCHEDULE EVERY 1 DAY
STARTS CONVERT_TZ('2024-01-01 00:00:00', '+00:00', @@global.time_zone)
DO
-- Event logic
Comparison: Event Scheduler vs Cron
Feature
MySQL Event Scheduler
Linux Cron
Dependency
MySQL server running
OS-level
Integration
Native SQL context
External scripts
Logging
Built-in information_schema
External (syslog)
Portability
Database portable
OS specific
Complexity
Simple untuk SQL tasks
Flexible untuk system tasks
Monitoring
SQL queries
External tools
Kesimpulan
MySQL Event Scheduler adalah tools yang powerful untuk:
– Database maintenance automation
– Scheduled data processing
– Report generation
– Cleanup tasks
– Data aggregation
Dengan proper design dan monitoring, events bisa menggantikan banyak cron jobs dan lebih terintegrasi dengan database operations.