Description:
With the recent unexpected need to change the PST/PDT switchover date, we had to replace the system time zone file (/etc/localtime) on a few of our MySQL database servers. When the newly appointed date arrived, the operating system updated it's clock appropriately (moved 1hr ahead), but mysqld did not.
The mysqld processes' time_zone is set to SYSTEM; mysql's 'time_zone%' tables are empty and we do not use named timezones; the system time was read with the linux `date` command; mysqld's time was read with the SQL statement "SELECT NOW();".
As I understand http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html, mysqld's time should change when the system time changes; in fact, if the system time is manually updated (ie, with `ntp`) mysqld reports the modified time, and when the system changes time according to the same timezone file that was present at mysqld startup, mysqld adjusts accordingly. It is only when the system's timezone changes due to a timezone (zoneinfo) file whose rules differ from those present at mysqld startup that a problem occurs.
Regarding mysql.time_zone% tables, the documentation clearly states, "A count of zero indicates that the table is empty. In this case, no one can be using named time zones, and you don't need to update the tables." Since all our servers do not use named timezones (and thus rely on the system time), we believed there would not be a problem on March 11. (There was...)
How to repeat:
* put old zoneinfo file in place ** old = Daylight savings change on first Sunday in April **
* set system time zone to PST, or any other affected zone *
* set system time to shortly before 2007 March 11, 2:00am. *
* start mysqld *
* put new zoneinfo file in place ** new = Daylight savings change on second Sunday in March **
]# mysql
> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | SYSTEM |
+---------------+--------+
1 row in set (0.00 sec)
>exit
Bye
]# mysql -e "show variables like 'system_time%'" -NBr && mysql -e 'select now()' -NBr && date
system_time_zone PST
2007-03-11 01:58:33
Sun Mar 11 01:58:33 PST 2007
]# mysql -e "show variables like 'system_time%'" -NBr && mysql -e 'select now()' -NBr && date
system_time_zone PST
2007-03-11 01:59:34
Sun Mar 11 01:59:34 PST 2007
]# mysql -e "show variables like 'system_time%'" -NBr && mysql -e 'select now()' -NBr && date
system_time_zone PST
2007-03-11 02:00:01
Sun Mar 11 03:00:01 PDT 2007
* notice how the operating system's time, and timezone, changed, but mysql's did not *
* restart mysqld *
]# mysql -e "show variables like 'system_time%'" -NBr && mysql -e 'select now()' -NBr && date
system_time_zone PDT
2007-03-11 03:00:50
Sun Mar 11 03:00:50 PDT 2007
Suggested fix:
Restarting mysqld after replacing the system's timezone file (/etc/localtime) fixes the problem.
Perhaps just a warning about this in the documentation would be sufficient. I would expect such a warning to be on http://dev.mysql.com/doc/refman/4.1/en/time-zone-support.html, along with the many other (helpful) notes relating to time-zone issues.