| Bug #82686 | convert_tz returns null - timezones are loaded | ||
|---|---|---|---|
| Submitted: | 22 Aug 2016 23:56 | Modified: | 23 Aug 2016 15:56 |
| Reporter: | Dan Weber | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.6.30 | OS: | FreeBSD |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | convert_tz | ||
[22 Aug 2016 23:56]
Dan Weber
[23 Aug 2016 15:42]
MySQL Verification Team
Hi Dan, Thank you for the report. I'm not seeing the reported issue at my end after following steps from http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html -- [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysql_tzinfo_to_sql /usr/share/zoneinfo |bin/mysql -uroot -S /tmp/mysql_ushastry.sock mysql [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 5.6.32-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT COUNT(*) FROM mysql.time_zone_name; +----------+ | COUNT(*) | +----------+ | 1747 | +----------+ 1 row in set (0.00 sec) mysql> select convert_tz(now(), 'UTC', 'America/New_York'); +----------------------------------------------+ | convert_tz(now(), 'UTC', 'America/New_York') | +----------------------------------------------+ | 2016-08-23 13:38:03 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> select now() as Now, -> @@global.time_zone as Global, -> @@system_time_zone as System, -> convert_tz(now(), 'UTC', 'America/New_York') as Converted; +---------------------+--------+--------+---------------------+ | Now | Global | System | Converted | +---------------------+--------+--------+---------------------+ | 2016-08-23 17:38:22 | SYSTEM | CEST | 2016-08-23 13:38:22 | +---------------------+--------+--------+---------------------+ 1 row in set (0.00 sec) How did you load populate timezone tables? If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL. Thanks, Umesh
[23 Aug 2016 15:56]
Dan Weber
Umesh, Thank you for your response. I used the following to load the timezone tables. mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql mysql Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it. As you can see, there was only the one warning. Also both time zones in my example show up in the table... mysql> select * from mysql.time_zone_name where name like '%UTC%' or name like '%New_York%'; +------------------+--------------+ | Name | Time_zone_id | +------------------+--------------+ | America/New_York | 153 | | Etc/UTC | 350 | | UTC | 466 | +------------------+--------------+ 3 rows in set (0.00 sec)
[23 Aug 2016 16:24]
MySQL Verification Team
Truncated and tried again, and you see warning here as well: mysql> truncate mysql.time_zone_name; Query OK, 0 rows affected (0.00 sec) mysql> \q Bye [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysql_tzinfo_to_sql /usr/share/zoneinfo |bin/mysql -uroot -S /tmp/mysql_ushastry.sock mysql Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it. Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it. [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-advanced-5.6.32: bin/mysql -uroot -S /tmp/mysql_ushastry.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 Server version: 5.6.32-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT COUNT(*) FROM mysql.time_zone_name; +----------+ | COUNT(*) | +----------+ | 1747 | +----------+ 1 row in set (0.00 sec) mysql> select convert_tz(now(), 'UTC', 'America/New_York'); +----------------------------------------------+ | convert_tz(now(), 'UTC', 'America/New_York') | +----------------------------------------------+ | 2016-08-23 13:59:19 | +----------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from mysql.time_zone_name where name like '%UTC%' or name like '%New_York%'; +------------------------+--------------+ | Name | Time_zone_id | +------------------------+--------------+ | America/New_York | 168 | | Etc/UTC | 415 | | UTC | 578 | | posix/America/New_York | 750 | | posix/Etc/UTC | 997 | | posix/UTC | 1160 | | right/America/New_York | 1333 | | right/Etc/UTC | 1580 | | right/UTC | 1743 | +------------------------+--------------+ 9 rows in set (0.01 sec) I'm not sure whether your system has an outdated zoneinfo database. Can you try on some other box to see if it is that case?
