MySQL event_scheduler events are not automatically enabled on failover
This is Onkai Yuta (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article describes a case where a minor accident occurred with MySQL's event_scheduler.
I hope this helps in some way.
overview
A certain service was using the event_scheduler function in a MySQL 8.0.28 environment, but the person in charge was not aware that an event had been set.
A MySQL failover occurred in that service, and the writer server was replaced, but after that, event_scheduler events were disabled and not executed.
What is event_scheduler?
MySQL's event_scheduler has a long history.MySQL5.1This is a feature that has been implemented since.
You can execute SQL at a specified time, just like with cron.
OFFONIf you look at SHOW PROCESS LIST you will see that the process is waiting.
You can create an event like this:
Example of an event that puts the current time in a column every minute:
Points to note about event_scheduler
In a replication configuration, after a failover occurs and the writer server is replaced, events will not be executed unless they are explicitly enabled on the new writer server.
It is important to be aware of this.
In a replication configuration, when you create an event on the writer side, the event status will be as follows:
You can see that Status: ENABLED.
Status: SLAVESIDE_DISABLEDSLAVESIDE_DISABLEDThe server that becomes the new writer must change the status to ENABLE for each event.
SLAVESIDE_DISABLEDIf the event status is ENABLE on both the writer and replica, it will be executed on each server, and depending on the content of the event processing, replication may stop with an error.
Reference URL
Conclusion
Although event_scheduler seems useful, it should be used with care.
Personally, I don't like putting application logic on the MySQL side, such as event_scheduler or stored routines.
It would be good if there was a proper handover, but I think it's something that is inevitably easily forgotten...
SRG is looking for people to work with us. If you are interested, please contact us here.