MySQL Sessions Caused by Event Scheduler During Failover

Background

This article explores a perplexing issue encountered during MySQL primary-secondary failovers: an unkillable session persisting despite standard termination attempts. While our proprietary cluster management platform, CloudTree® DMP, follows typical failover steps—such as VIP rebinding, read-only mode enforcement, and connection cleanup—unexpected warnings like [warn] kill process warning: Error 1094: Unknown thread id:4 emerged during testing. These warnings were traced back to the event_scheduler thread, a MySQL internal process responsible for executing scheduled events.

Unlike MySQL 5.7 (where event_scheduler defaults to OFF), MySQL 8.0 enables it by default, making this issue more prevalent in newer versions.

Understanding event_scheduler

The event_scheduler is a background daemon that triggers predefined events (similar to Linux cron jobs). It operates independently of user-initiated connections, which explains why it resists manual termination attempts. Key characteristics include:

• ​Command Type: Marked as Daemon in SHOW PROCESSLIST.
• ​Persistence: Runs continuously until MySQL service stops.
• ​Replication Behavior: Events created on the primary are replicated to secondaries but remain disabled (SLAVESIDE_DISABLED) to prevent duplication.

Practical Examples

Creating and Managing Events

-- Enable event_scheduler dynamically  
SET GLOBAL event_scheduler = 1;  

-- Create an event to delete old logs  
CREATE EVENT delete_logs_event  
ON SCHEDULE EVERY 1 MINUTE  
STARTS '2023-06-19 00:00:00'  
DO  
  DELETE FROM logs  
  WHERE log_time < DATE_SUB(NOW(), INTERVAL 7 DAY) LIMIT 1;  

-- Modify an existing event (requires ALTER privilege)  
ALTER EVENT delete_logs_event  
ON SCHEDULE EVERY 1 DAY  
DO  
  DELETE FROM logs  
  WHERE log_time < DATE_SUB(NOW(), INTERVAL 7 DAY);  

-- Drop an event  
DROP EVENT delete_logs_event;  

‍

Observing Event Behavior

-- Check event details  
SELECT * FROM information_schema.events WHERE event_name = 'delete_logs_event';  

-- View event execution status in processlist  
SELECT * FROM information_schema.processlist WHERE user = 'event_scheduler';

‍

Key Considerations for Failovers

1. ​Event Persistence: Events created on the primary remain inactive (SLAVESIDE_DISABLED) on secondaries post-failover.
2. ​Manual Cleanup: Terminating event_scheduler threads is impossible due to their daemon nature. Instead, disable them via SET GLOBAL event_scheduler = 0; before failover.
3. ​Replication Safety: Ensure events do not disrupt secondary operations by verifying SLAVESIDE_DISABLED status.

Conclusion

While MySQL’s event_scheduler simplifies automation, its interaction with failover mechanisms requires caution. By disabling the scheduler during failovers and leveraging replication flags like SLAVESIDE_DISABLED, teams can avoid unkillable sessions and maintain cluster stability. For deeper insights into MySQL internals, explore the official documentation or engage with the community.

‍