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:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.7 OS:Windows (Windows, Linux)
Assigned to: Jon Stephens CPU Architecture:Any

[1 Mar 2006 19:13] Markus Popp
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.
[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