| Bug #17835 | Event scheduler: start times don't match | ||
|---|---|---|---|
| Submitted: | 1 Mar 2006 19:13 | Modified: | 1 Apr 2006 10:09 |
| Reporter: | Markus Popp | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.1.7 | OS: | Windows (Windows, Linux) |
| Assigned to: | Jon Stephens | CPU Architecture: | Any |
[15 Mar 2006 15:07]
Tonci Grgin
Thank you for your report. Please provide us with info on server and client (session) time-zone as described in manual entry: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
[15 Mar 2006 22:22]
Markus Popp
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13536 to server version: 5.1.7-beta-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SELECT @@global.time_zone, @@session.time_zone; +--------------------+---------------------+ | @@global.time_zone | @@session.time_zone | +--------------------+---------------------+ | SYSTEM | SYSTEM | +--------------------+---------------------+ 1 row in set (0.06 sec) The system time-zone is CET (UTC + 1 hour).
[17 Mar 2006 9:44]
Tonci Grgin
Verified just as described.
Additional test done with CURRENT_TIMESTAMP as described below:
DELIMITER //
DROP EVENT IF EXISTS archiveGeneralLogTest //
CREATE EVENT archiveGeneralLogTest
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP
ENABLE
DO BEGIN
INSERT INTO slow_log.general_log_data
SELECT * FROM mysql.general_log
WHERE command_type = 'Connect';
TRUNCATE mysql.general_log;
END //
DELIMITER ;
*************************** 2. row ***************************
EVENT_CATALOG: NULL
EVENT_SCHEMA: test
EVENT_NAME: archiveGeneralLogTest
DEFINER: root@localhost
EVENT_BODY: BEGIN
INSERT INTO slow_log.general_log_data
SELECT * FROM mysql.general_log
WHERE command_type = 'Connect';
TRUNCATE mysql.general_log;
END
EVENT_TYPE: RECURRING
EXECUTE_AT: NULL
INTERVAL_VALUE: 1
INTERVAL_FIELD: DAY
SQL_MODE:
STARTS: 2006-03-17 09:36:28
ENDS: 0000-00-00 00:00:00
STATUS: ENABLED
ON_COMPLETION: NOT PRESERVE
CREATED: 2006-03-17 11:36:28
LAST_ALTERED: 2006-03-17 11:36:28
LAST_EXECUTED: NULL
EVENT_COMMENT:
2 rows in set (0.11 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2006-03-17 11:38:26 |
+---------------------+
1 row in set (0.00 sec)
Event start time is converted to UTC.
I am not sure this is a bug but rather not documented behaviour.
[1 Apr 2006 10:09]
Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant product(s). Additional info: Updated relevant portions of 5.1 Manual (create-events, events-table, show-events sections). Closed.
[5 Apr 2006 21:55]
Peter Gulutzan
Looks like bug#16420
Description: After defining an event, the SHOW EVENTS output shows the start time one hour ahead of the originally defined start time. The clock is set to CET, which is one hour ahead of UTC. I guess that the SHOW EVENTS output shows UTC instead of the current time zone. How to repeat: Define an event like this: DELIMITER // DROP EVENT archiveGeneralLog // CREATE EVENT archiveGeneralLog ON SCHEDULE EVERY 1 DAY STARTS '2006-03-02 00:00:00' ENABLE DO BEGIN INSERT INTO slow_log.general_log_data SELECT * FROM mysql.general_log WHERE command_type = 'Connect'; TRUNCATE mysql.general_log; END // DELIMITER ; SHOW EVENTS shows the start time one hour ahead. mysql> show events\G *************************** 1. row *************************** Db: mysql Name: archiveGeneralLog Definer: mpopp_admin@% Type: RECURRING Execute at: NULL Interval value: 1 Interval field: DAY Starts: 2006-03-01 23:00:00 Ends: 0000-00-00 00:00:00 Status: ENABLED 1 row in set (0.39 sec) Suggested fix: The start time (as well as all other date/time outputs) should be shown according the current time zone, not UTC, since this could easily mislead users.