Salin dan Bagikan

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.

MySQL Event Scheduler: Panduan Lengkap untuk Scheduled Tasks dan Automation

MySQL Event Scheduler: Panduan Lengkap untuk Scheduled Tasks dan Automation

Enable Event Scheduler

1. Enable Globally

2. Verify Scheduler

Basic Event Syntax

1. One-Time Event

2. Recurring Event

3. Event dengan Specific Time

Event Management

1. Create Event dengan Options

2. Alter Event

3. Drop Event

Common Use Cases

1. Data Cleanup

2. Maintenance Tasks

3. Data Aggregation

4. Notification/Alert Generation

5. User Activity Reporting

Advanced Event Features

1. Conditional Logic dalam Events

2. Error Handling dalam Events

3. Events dengan Stored Procedures

Monitoring dan Logging

1. Event Status Monitoring

2. Create Event Logging Table

3. Events dengan Built-in Logging

Event Scheduler Best Practices

1. Design Principles

2. Performance Considerations

3. Security

Troubleshooting

1. Event Not Running

2. Events Stuck atau Hanging

3. Time Zone Issues

Comparison: Event Scheduler vs Cron

Kesimpulan

Artikel Terkait