| Bug #95649 | MySQL 8.0 INFORMATION_SCHEMA.EVENTS not observing custom timezone for last_execu | ||
|---|---|---|---|
| Submitted: | 5 Jun 2019 9:40 | Modified: | 9 Oct 2019 15:00 |
| Reporter: | Egidijus Grismanauskas | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S3 (Non-critical) |
| Version: | 8.x | OS: | Linux |
| Assigned to: | Paul DuBois | CPU Architecture: | x86 |
[5 Jun 2019 12:06]
MySQL Verification Team
Hi, Thank you for your bug report. Last_executed column is based on the system timezone and hence it works as expected. However, it is not documented properly. Also, your last comment points to yet another small inconsistency in our documentation. Hence, I am verifying this bug as a documentation bug.
[5 Jun 2019 14:58]
MySQL Verification Team
Hi, Our documentation should also state whether global or session settings are used.
[9 Aug 2019 13:08]
Paul DuBois
Posted by developer: re: "Based on MySQL 8 documentation, Last executed from INFORMATION_SCHEMA.EVENTS should return using the event timezone however regardless of set time_zone parameter last executed always seems to return in UTC." If you find someplace in the documentation that states last executed uses the event time zone, please reopen this bug and provide the URL of the page that says so. Otherwise, last executed is documented to use UTC. * The INFORMATION_SCHEMA.EVENTS page (https://dev.mysql.com/doc/refman/8.0/en/events-table.html) says this under Notes: "Times in the EVENTS table are displayed using the event time zone, the current session time zone, or UTC, as described in Section 24.4.4, “Event Metadata”." * The event metadata page (https://dev.mysql.com/doc/refman/8.0/en/events-metadata.html) says this: "For representation of event information in the mysql.event table, the execute_at, starts, and ends times are converted to UTC and stored along with the event time zone. This enables event execution to proceed as defined regardless of any subsequent changes to the server time zone or daylight saving time effects. The last_executed time is also stored in UTC." Note that last sentence.
[9 Aug 2019 13:12]
MySQL Verification Team
Thank you, Paul.
[9 Aug 2019 14:39]
Paul DuBois
Posted by developer: Actually, after further investigation, I'm re-opening this bug and reclassifying it as a server bug, because there are two differences in the I_S.EVENTS table between MySQL 5.7 and 8.0. I suspect these have to do with the transition from the mysql.event table in 5.7 to the data dictionary in 8.0. I do see now that the documentation at https://dev.mysql.com/doc/refman/8.0/en/events-metadata.html has a table at the end indicating the I_S.EVENTS.LAST_EXECUTED should be in the ETZ. Summary: * I_S.EVENTS.LAST_EXECUTED is in UTC, not the ETZ. * I_S.EVENTS.LAST_ALTERED is in the STZ, but it does not get updated. (Appears to be initialized to the CREATED time, but not updated when ALTER TABLE is executed). Test script: SET sql_mode='STRICT_TRANS_TABLES'; DROP EVENT IF EXISTS myevt; CREATE EVENT myevt ON SCHEDULE EVERY 5 SECOND STARTS '2019-08-01 00:00:00' ENDS '2019-08-31 00:00:00' DO DO 1+1; DO SLEEP(10); ALTER EVENT myevt COMMENT 'event comment'; SELECT * FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME='myevt'\G SELECT * FROM mysql.event WHERE name='myevt'\G Run this script at the same time in 5.7 and 8.0 (the SELECT from mysql.event will fail in 8.0, of course), and diff the results. Here are the important differences in I_S.EVENT contents (5.7: - lines; 8.0: + lines) CREATED: 2019-08-09 09:27:37 - LAST_ALTERED: 2019-08-09 09:27:47 - LAST_EXECUTED: 2019-08-09 09:27:45 + LAST_ALTERED: 2019-08-09 09:27:37 + LAST_EXECUTED: 2019-08-09 14:27:45 Note that LAST_ALTERED in 5.7 is 10 seconds later than CREATED, as it should be. In 8.0, it did not get updated. Note that LAST_EXECUTED in 8.0 gets converted to UTC (2019-08-09 14:27:45); it should be reported in the ETZ (2019-08-09 09:27:45).
[9 Aug 2019 14:55]
Paul DuBois
Posted by developer: I will take care of removing references to the mysql.event table in the MySQL 8.0 documentation. That table's contents now are represented in the data dictionary.
[12 Aug 2019 12:15]
MySQL Verification Team
Paul, I do agree that it is a code bug .....
[9 Oct 2019 15:00]
Paul DuBois
Posted by developer: Fixed in 8.0.19. The LAST_EXECUTED value in the INFORMATION_SCHEMA.EVENTS table was incorrectly reported in UTC, not in the event time zone.
[10 Oct 2019 12:04]
MySQL Verification Team
Thank you, Paul .........
Description: Based on MySQL 8 documentation, Last executed from INFORMATION_SCHEMA.EVENTS should return using the event timezone however regardless of set time_zone parameter last executed always seems to return in UTC. This is not the case on on MySQL 5.7.x where last executed is returned correctly depending on the time_zone set. How to repeat: Client Version mysql --version mysql Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL) Server Version mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.16 | +-----------+ 1 row in set (0.00 sec) SET @@global.time_zone = 'US/Pacific'; SET @@session.time_zone = 'US/Pacific'; mysql> SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone; +--------------------+---------------------+--------------------+ | @@global.time_zone | @@session.time_zone | @@system_time_zone | +--------------------+---------------------+--------------------+ | US/Pacific | US/Pacific | UTC | +--------------------+---------------------+--------------------+ 1 row in set (0.00 sec) CREATE EVENT IF NOT EXISTS event_01 ON SCHEDULE EVERY 1 MINUTE DO INSERT INTO test(message) VALUES(NOW()); mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS where EVENT_NAME='event_01'\G; *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: mysql EVENT_NAME: event_01 DEFINER: root@localhost TIME_ZONE: US/Pacific EVENT_BODY: SQL EVENT_DEFINITION: INSERT INTO test(message) VALUES(NOW()) EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 1 INTERVAL_FIELD: MINUTE SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION STARTS: 2019-05-17 08:22:59 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2019-05-17 08:22:59 LAST_ALTERED: 2019-05-17 08:22:59 LAST_EXECUTED: 2019-05-17 15:22:59 EVENT_COMMENT: ORIGINATOR: 1 CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) SET @@system_time_zone = 'US/Pacific'; (read only) sudo ln -sf /usr/share/zoneinfo/US/Pacific /etc/localtime SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone; +--------------------+---------------------+--------------------+ | @@global.time_zone | @@session.time_zone | @@system_time_zone | +--------------------+---------------------+--------------------+ | SYSTEM | SYSTEM | PDT | +--------------------+---------------------+--------------------+ 1 row in set (0.00 sec) SET @@global.time_zone = 'UTC'; SET @@session.time_zone = 'UTC'; mysql> SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone; +--------------------+---------------------+--------------------+ | @@global.time_zone | @@session.time_zone | @@system_time_zone | +--------------------+---------------------+--------------------+ | UTC | UTC | PDT | +--------------------+---------------------+--------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.EVENTS where EVENT_NAME='event_01'\G; *************************** 1. row *************************** EVENT_CATALOG: def EVENT_SCHEMA: mysql EVENT_NAME: event_01 DEFINER: root@localhost TIME_ZONE: US/Pacific EVENT_BODY: SQL EVENT_DEFINITION: INSERT INTO test(message) VALUES(NOW()) EVENT_TYPE: RECURRING EXECUTE_AT: NULL INTERVAL_VALUE: 1 INTERVAL_FIELD: MINUTE SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION STARTS: 2019-05-17 08:22:59 ENDS: NULL STATUS: ENABLED ON_COMPLETION: NOT PRESERVE CREATED: 2019-05-17 15:22:59 LAST_ALTERED: 2019-05-17 15:22:59 LAST_EXECUTED: 2019-05-17 15:35:59 EVENT_COMMENT: ORIGINATOR: 1 CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) Suggested fix: Update the information_schema last_executed to observe ETZ or update the matrix in the MySQL documentation to say that last_executed will always show as UTC and not ETZ.