Bug #49621 | time settings in GB timezone is incorrect even though system_time_zone says GMT | ||
---|---|---|---|
Submitted: | 11 Dec 2009 14:33 | Modified: | 14 Dec 2009 11:36 |
Reporter: | Geoff Winkless | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
Version: | 5.0.88, 5.0.85, 5.0.15 | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[11 Dec 2009 14:33]
Geoff Winkless
[11 Dec 2009 17:26]
Geoff Winkless
(updated to version 5.0.88 and checked problem still exists)
[13 Dec 2009 7:34]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior. Please send us result of query SHOW GLOBAL VARIABLES LIKE '%time%zone%';
[14 Dec 2009 10:40]
Geoff Winkless
Apologies: I had assumed the server was set up with GMT locale but when I just checked it is actually set up with GB. OS is CentOS 4.4 but also fails on RHEL5.3, on an old legacy RH9 server and on a Solaris 9 box. It's possible there's a problem with the GB zoneinfo file (I assume both Solaris and RH use the same Olsen source?) but why does mysql tell me that the setting is GMT when if it were GMT the time would be correct? [root@geofftest etc]# rm /etc/localtime rm: remove regular file `/etc/localtime'? y [root@geofftest etc]# ln -s /usr/share/zoneinfo/GMT /etc/localtime [root@geofftest etc]# /etc/init.d/mysqld stop Stopping MySQL: [ OK ] [root@geofftest etc]# /etc/init.d/mysqld start Starting MySQL: [ OK ] [root@geofftest etc]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.88 MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select 86400-unix_timestamp("1970-01-02 00:00:00"); +---------------------------------------------+ | 86400-unix_timestamp("1970-01-02 00:00:00") | +---------------------------------------------+ | 0 | +---------------------------------------------+ 1 row in set (0.00 sec) mysql> Bye [root@geofftest etc]# rm /etc/localtime rm: remove symbolic link `/etc/localtime'? y [root@geofftest etc]# ln -s /usr/share/zoneinfo/GB /etc/localtime [root@geofftest etc]# /etc/init.d/mysqld stop Stopping MySQL: [ OK ] [root@geofftest etc]# /etc/init.d/mysqld start Starting MySQL: [ OK ] [root@geofftest etc]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.88 MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select 86400-unix_timestamp("1970-01-02 00:00:00"); +---------------------------------------------+ | 86400-unix_timestamp("1970-01-02 00:00:00") | +---------------------------------------------+ | 3600 | +---------------------------------------------+ 1 row in set (0.01 sec) mysql> show global variables like '%time%zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | GMT | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.00 sec)
[14 Dec 2009 10:47]
Geoff Winkless
Modified synopsis because it turns out that the reason setting TZ explicitly appeared to work was because I was actually forcing the server to GMT locale not GB... To be clear, explicitly setting $TZ to GB produces the same incorrect value. [root@geofftest etc]# export TZ=GB [root@geofftest etc]# /etc/init.d/mysqld start Starting MySQL: [ OK ] [root@geofftest etc]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.88 MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select 86400-unix_timestamp("1970-01-02 00:00:00"); +---------------------------------------------+ | 86400-unix_timestamp("1970-01-02 00:00:00") | +---------------------------------------------+ | 3600 | +---------------------------------------------+ 1 row in set (0.00 sec) mysql> show global variables like '%time%zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | GMT | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.00 sec) mysql> Bye
[14 Dec 2009 11:03]
Sveta Smirnova
Thank you for the feedback. Please send us result of queries show status like 'uptime and select now();
[14 Dec 2009 11:07]
Geoff Winkless
Thanks for the speedy response! mysql> show status like 'uptime%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Uptime | 1265 | +---------------+-------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2009-12-14 10:48:12 | +---------------------+ 1 row in set (0.00 sec)
[14 Dec 2009 11:36]
Geoff Winkless
Discovered my own reason. The UK stupidly was in "British Summer Time" permanently between 1968 and 1971. As such in "GB" locale 1970-01-02 00:00:00 is indeed 82800. Sorry to have wasted your time and thanks for your efforts.