MySQL event_scheduler events are not automatically enabled after a failover
This is Onkai Yuta (@fat47) from the Service Reliability Group (SRG) of the Media Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
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 unaware that an event had been set.
A MySQL failover occurred in the service, and the writer server was replaced, but after that, the 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, similar to cron.
OFFONIf you look at SHOW PROCESS LIST you will see that the process is waiting.
This is how you create an event.
Example of an event that puts the current time in a column every minute:
Notes on 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 an event is created 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 event processing content, replication may stop with an error.
Reference URL
Conclusion
Although event_scheduler seems useful, you should be careful when using it.
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 easy to forget...
SRG is looking for people to work with us.
If you're interested, please contact us here.