Bug #48816 | INSERT query run from an event appears to use GMT time data for NOW | ||
---|---|---|---|
Submitted: | 16 Nov 2009 19:59 | Modified: | 27 Nov 2009 19:17 |
Reporter: | Timothy Loach | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Stored Routines | Severity: | S3 (Non-critical) |
Version: | 5.1.11, 5.1.40 | OS: | Windows (XP PRO) |
Assigned to: | CPU Architecture: | Any | |
Tags: | Event, event scheduler, GMT, time |
[16 Nov 2009 19:59]
Timothy Loach
[16 Nov 2009 21:45]
MySQL Verification Team
Thank you for the bug report. The version reported 5.1.11 is quite older, could you please try the release 5.1.40 and comment the results. Thanks in advance.
[18 Nov 2009 16:10]
Timothy Loach
Same behaviour in 5.1.40. Taking a closer look at the mysql.event table I see that the time_zone field is set to '+00:00'. When I set it to '-05:00' the correct time is set. Is it expected behaviour for events to use their own time zone settings rather than @@time_zone? Is this bug simlpy that event.time_zone isn't being populated properly from the system time at event creation? (reducing severity since this I now have a work-around)
[18 Nov 2009 19:02]
Valeriy Kravchuk
Please, check @@time_zone value before you create event. Our manual (http://dev.mysql.com/doc/refman/5.1/en/create-event.html) says: "Times in the ON SCHEDULE clause are interpreted using the current session time_zone value. This becomes the event time zone; that is, the time zone that is used for event scheduling and is in effect within the event as it executes. These times are converted to UTC and stored along with the event time zone in the mysql.event table."
[18 Nov 2009 19:10]
Timothy Loach
I took that section of the manual to mean that the time that the event triggers at is set in the event table. In this case the event runs every minute starting when it is created, I could really care less what time zone the scheduler's time is in. I would not take this statement to mean that any access to the NOW or SYSDATE functions would use the event.time_zone. My @@time_zone is set to SYSTEM, and my system time is set to GMT-05:00, so even if the date functions are supposed to use event.time_zone the event.time_zone value is not setting properly when creating an event.
[27 Nov 2009 8:49]
Sveta Smirnova
Thank you for the feedback. I can not repeat described behavior: just created event uses correct value of time_zone: mysql> select @@time_zone; +-------------+ | @@time_zone | +-------------+ | SYSTEM | +-------------+ 1 row in set (0.04 sec) mysql> create event ev2 on schedule every 1 minute do select 1; Query OK, 0 rows affected (0.11 sec) mysql> select * from mysql.event where name='ev2'\G *************************** 1. row *************************** db: test name: ev2 body: select 1 definer: root@localhost execute_at: NULL interval_value: 1 interval_field: MINUTE created: 2009-11-27 11:47:16 modified: 2009-11-27 11:47:16 last_executed: NULL starts: 2009-11-27 08:47:16 ends: NULL status: ENABLED on_completion: DROP sql_mode: comment: originator: 0 time_zone: SYSTEM character_set_client: latin1 collation_connection: latin1_swedish_ci db_collation: utf8_unicode_ci body_utf8: select 1 1 row in set (0.36 sec) Please try to create event after you are sure session @@time_zone value is not GMT and if you still have the problem paste output of the session as I did.
[27 Nov 2009 19:17]
Timothy Loach
I am now unable to reproduce, so closing the ticket.