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:
None 
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
Description:
Suddenly, when we use convert_tz we are getting a null result returned. Everything seems to be in place so this isn't making much sense.

SELECT COUNT(*) FROM mysql.time_zone_name;
+----------+
| COUNT(*) |
+----------+
|      468 |
+----------+
1 row in set (0.00 sec)

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-22 23:55:37 | SYSTEM | UTC    | NULL      |
+---------------------+--------+--------+-----------+
1 row in set (0.00 sec)

How to repeat:
select convert_tz(now(), 'UTC', 'America/New_York');
[23 Aug 2016 15:42] Umesh Shastry
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] Umesh Shastry
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?