Description:
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;
ALTER TABLE `log` ADD PRIMARY KEY (`id`);
ALTER TABLE `log` MODIFY `id` int NOT NULL AUTO_INCREMENT;
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`@`127.0.0.1` 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.
Description: 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; ALTER TABLE `log` ADD PRIMARY KEY (`id`); ALTER TABLE `log` MODIFY `id` int NOT NULL AUTO_INCREMENT; 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`@`127.0.0.1` 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.