Bug #108949 Recuring events (every x seconds/minute) STOPS working when changing DST to ST!
Submitted: 1 Nov 2022 9:48 Modified: 2 Nov 2022 12:57
Reporter: Bazard Shoxer Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:8.0.29-21 OS:Ubuntu (Ubuntu 20.04.1 LTS)
Assigned to: CPU Architecture:x86
Tags: bug, daylight savings, events_bugs, failing, timezone

[1 Nov 2022 9:48] Bazard Shoxer
A while ago, we stumbled upon an issue where recurring events in MySQL stopped working, but this turned out to be because of incorrect NTP configuration. When the time was set back from, for example, 10:00 to 08:00, all recurring events were interrupted for 2 hours. So, when it got 08:01, 08:02, 08:03 etc, it wasn't fired, probably because the event was fired at 10:00 already. inconvenient, but because this happened because of a failing NTP configuration, we didn't pay much attention to it.

Last weekend, in Europe, we changed from daylight saving time (DST) back to standard time (ST). So, after the first time, it got 02:59:59, a second later, the time went back to 02:00:00 instead of 03:00:00. This also led to the fact that recurring events, scheduled to repeat every x second or x minute, were interrupted for a full hour! This is a serious issue, which makes MySQL's recurring events an unreliable feature!

How to repeat:
In order to resolve this issue, follow the steps below:

Step 1] Set the timezone in Ubuntu/Linux to Europe/Amsterdam, using the command "sudo timedatectl set-timezone Europe/Amsterdam". No additional MySQL configuration is needed. Before continuing, restart MySQL.

Step 2] Create a test-database and create a new table named "log". Using the queries below, this will create a table with 3 columns. The column 'message' will be used to insert a logmessage, the column id and column datetime will be automatically filled with the correct data.

CREATE TABLE `log` (`id` int NOT NULL, `message` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Step 3] Create a recurring event named "heatbeat" using the query below. This will create an event that will (or at least should...) be executed once every 10 seconds. The only thing this event will do is log a message onto the table named "log", telling them it's still running. We'll use the CONCAT function in order to include the current UNIX Timestamp in the message.

CREATE DEFINER=`root`@`` EVENT `heartbeat` ON SCHEDULE EVERY 10 SECOND STARTS '2000-01-01 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO INSERT INTO `log` (`message`) VALUES (CONCAT('Still running - Timestamp ', UNIX_TIMESTAMP()))

Step 4] See the table "log". If all goes well, a new message will be inserted every 10 seconds. If not, enable the event scheduler in MySQL.

Step 5] Now, change the time in Ubuntu/Linux (make sure NTP is disabled) with the command "date -s '2022-10-30T01:55:00+02:00'". This is just before the moment the times will change. After this, restart MySQL again. 

Step 6] See the table "log" again. It now should show messages with datetimes like this:

id  |  message                               |  datetime
9   |  Still running - Timestamp 1667087800  |  2022-10-30 01:56:40
10  |  Still running - Timestamp 1667087810  |  2022-10-30 01:56:50
11  |  Still running - Timestamp 1667087820  |  2022-10-30 01:57:00
12  |  Still running - Timestamp 1667087830  |  2022-10-30 01:57:10

Step 7] Wait until the time increases. Up until datetime 2022-10-30 01:59:50, a new message will be inserted every second.

Step 8] After datetime 2022-10-30 01:59:50, no new messages are incoming anymore. The event, configured to run every 10 seconds, has stopped!

Step 9] Now, wait 1 hour... after 1 hour, new messages will be inserted in the table "log" again. As soon as new messages come in, you can see the timestamp in the message. The last timestamp was 1667087800, the newest message will have timestamp 1667091600. So, we're missing 3600 seconds here! The table will look like this:

id  |  message                               |  datetime
26  |  Still running - Timestamp 1667087970  |  2022-10-30 01:59:30
27  |  Still running - Timestamp 1667087980  |  2022-10-30 01:59:40
28  |  Still running - Timestamp 1667087990  |  2022-10-30 01:59:50
29  |  Still running - Timestamp 1667091600  |  2022-10-30 02:00:00
30  |  Still running - Timestamp 1667091610  |  2022-10-30 02:00:10
31  |  Still running - Timestamp 1667091620  |  2022-10-30 02:00:20

Suggested fix:
After a lot of testing, I managed to repeat the issue. With the earlier issues with the NTP configuration, it's clear that MySQL's recuring event a unreliable feature. So, this should be solved. When creating a database engine which is able to execute something every x time, it should work as configured.

Also, when looking at the EVENTS table in the information_schema table, I noticed that the LAST_EXECUTED column is a datetime, not holding any information about the timezone. A datetime without any information about the timezone is useless. Because 2202-10-30 02:10:00 can be timestamp  1667088600 (when still in DST, using GMT+02:00) but is also can be timestamp 1667092200 (when back to ST, using GMT+01:00.) So when it says 2202-10-30 02:10:00… which one is it?

A good start would be using the UNIX timestamp here instead of the datetime. This won't solve the issue with the NTP-configuration, but will fix the issues when the times changes from daylight saving time back to standard time, once every year.
[2 Nov 2022 12:57] MySQL Verification Team
Hi Mr. Shoxer,

Thank you for your bug report.

However, it is not a bug.

MySQL has to obey all the settings for the given timezone. That is documented in our Reference Manual. Millions of our customers and users rely on that feature. Hence, we can not change that .......

What you can do is to try and set your timezone to one which does not have DST involved. 

Not a bug.
[2 Nov 2022 14:02] MySQL Verification Team
Hi Mr. Shoxer,

We have considered your request and concluded that there could be some middle ground.

You could ask us to treat this bug report as a new feature request, so that in cases of DST zones, recurring events are still obeyed, by other means. That means that no longer we are ask the OS for the time() service, but we could measure time in some other manner and continue with events. This is valid only for the events that execute in the recurring fashion for the duration of less then one hour.

If you could define such a feature request, we would be happy to verify it.