Bug #27103 mysqld does not read changes to system's zoneinfo file
Submitted: 13 Mar 2007 20:43 Modified: 19 Mar 2007 19:22
Reporter: Devananda van der Veen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:4.1.14, 5.0.34 OS:Linux (Fedora Core 4)
Assigned to: Paul Dubois CPU Architecture:Any
Tags: system_time_zone, time, time_zone
Triage: D4 (Minor)

[13 Mar 2007 20:43] Devananda van der Veen
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.
[14 Mar 2007 8:41] Sveta Smirnova
Thank you for the report.

Verified as described on 5.0.34 and virtual Ubuntu.

To repeat not needed to reinstall timezone files: just change timezone manually.
[19 Mar 2007 19:22] Paul Dubois
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 products.

Added this note to the time zone support section:

If you replace the system's /etc/localtime timezone file with a
verion that uses rules differing from those in effect at mysqld
startup, you should restart mysqld so that it uses the updated rules.
Otherwise, mysqld might not notice when the system changes its time.