HOME/Technical article/MySQL's event_scheduler feature does not automatically activate events during failover.

MySQL's event_scheduler feature does not automatically activate events during failover.

2024/3/7 15:032024/9/20 8:56

This is Yuta Kikai (@fat47) from the Service Reliability Group (SRG) of the Media Management Division.

#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).

This article describes a minor incident that occurred with MySQL's event_scheduler.

I hope this is of some help.

overview What is event_scheduler?Points to note about event_scheduler Reference URL In conclusion

overview

In a certain service using MySQL 8.0.28, the event_scheduler function was being used, but the person in charge was unaware that an event had been set up.

A MySQL failover occurred in that service, and the writer's server was replaced. As a result, the event_scheduler events were disabled and not executed.

What is event_scheduler?

MySQL's event_scheduler has a long history.MySQL5.1This feature has been implemented since [date/time].

You can execute SQL at a specified time, similar to cron.

OFF

ON

The SHOW PROCESS LIST command shows that processes are waiting.

Events can be created in this way.

Example of an event that inserts the current time into a "minutes" column:

Points to note about event_scheduler

In a replication configuration, after a failover occurs and the writer server is replaced, the event will not be executed unless the event is explicitly enabled on the new writer server.

This is something to be aware of.

In a replication configuration, when an event is created on the writer side, the event status will be as follows:

You can see that the status is set to ENABLED.

Status: SLAVESIDE_DISABLED

SLAVESIDE_DISABLED

On the server where the new writer is located, you need to change the status to ENABLE for each event.

SLAVESIDE_DISABLED

If the event status is ENABLE on both the writer and the replica, the event will be executed on each server, which may cause replication to stop with an error depending on the event's processing content.

Reference URL

121st event | gihyo.jp

This time, we'll create a query that runs periodically using event_scheduler, and then we'll look at how it works and some points to keep in mind when configuring it.

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0121

MySQL :: MySQL 8.0 Reference Manual :: 25.4.1 Overview of the Event Scheduler

MySQL events are tasks that run according to a schedule; therefore, they are sometimes called scheduled events. When you create an event, you create a named database object that contains one or more SQL statements that start and end at a specific date and time and run at one or more regular intervals. Conceptually, this is similar to the idea of crontab in Unix (also known as "cron jobs") or the Task Scheduler in Windows.

https://dev.mysql.com/doc/refman/8.0/ja/events-overview.html

MySQL :: MySQL 8.0 Reference Manual :: 13.1.13 CREATE EVENT Statement

This statement creates and schedules a new event. The event will not run unless the Event Scheduler is enabled. For information on how to check the status of the Event Scheduler and enable it if necessary, see Section 25.4.2, “Configuring the Event Scheduler.”

https://dev.mysql.com/doc/refman/8.0/ja/create-event.html

MySQL :: MySQL 8.0 Reference Manual :: 13.7.7.18 SHOW EVENTS statement

This statement displays information about the Event Manager events described in Section 25.4, “Using the Event Scheduler.” This requires EVENT privileges on the original database from which these events are displayed.

https://dev.mysql.com/doc/refman/8.0/ja/show-events.html

In conclusion

The `event_scheduler` seems useful, but you need to be very careful when using it.

Personally, I don't like putting application logic on the MySQL side, such as using event_scheduler or stored routines.

It would be great if the handover process were done properly, but I think it's something that's easily forgotten...

SRG is looking for new team members. If you are interested, please contact us here.

Recruitment Information - CyberAgent SRG #ca_srg

About SRG: SRG (Service Reliability Group) operates under the vision of "improving reliability across media businesses" and promotes the introduction of SRE into media businesses as a cross-functional SRE, working to improve reliability. Our work primarily revolves around the following three areas: Gathering and deploying technical know-how from each business.

https://ca-srg.dev/careers