Description:
In the INFORMATION_SCHEME.EVENTS, the field LAST_EXECUTED is a field of type 'datetime', which doesn't include information about the timezone.
In Europe for example, when we change from daylight saving time (DST) back to standard time (ST), after the first time it got 02:59:59, a second later, the time will be set back to 02:00:00 instead of 03:00:00.
Because while running events, MySQL will check on the seconds after this change in time if (for example) 02:00:01 > 02:59:59. This isn't the case, which will led to the fact that recurring events, scheduled to repeat every x second or x minute, are interrupted for a full hour.
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?
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:
The LAST_EXECUTED column in the EVENTS table in the information_schema database should be a 'timestamp' instead of a 'datetime'. This way, it won't be affected by changed in local times, for example when changing from daylight saving time (DST) back to standard time (ST).
Description: In the INFORMATION_SCHEME.EVENTS, the field LAST_EXECUTED is a field of type 'datetime', which doesn't include information about the timezone. In Europe for example, when we change from daylight saving time (DST) back to standard time (ST), after the first time it got 02:59:59, a second later, the time will be set back to 02:00:00 instead of 03:00:00. Because while running events, MySQL will check on the seconds after this change in time if (for example) 02:00:01 > 02:59:59. This isn't the case, which will led to the fact that recurring events, scheduled to repeat every x second or x minute, are interrupted for a full hour. 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? 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: The LAST_EXECUTED column in the EVENTS table in the information_schema database should be a 'timestamp' instead of a 'datetime'. This way, it won't be affected by changed in local times, for example when changing from daylight saving time (DST) back to standard time (ST).