Bug / Error when generating mysql.time_zone* tables | convert_tz() returns NULL

mysql

#1

I am trying to install the MySql timezone tables into my workspace to enable comparison of requests from various time zones.

WORKSPACE NAME:
MySQL Version is: mysql Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3

Following the recommended way to do so I have tried:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql   
sudo mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

mysql_tzinfo_to_sql /usr/share/lib/zoneinfo | mysql -uroot --force mysql
sudo mysql_tzinfo_to_sql /usr/share/lib/zoneinfo | mysql -uroot --force mysql

Essentially, all commands fail with a similar message to below:

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.

In a totally separate environment outside Cloud9 which is hosted on Amazon AWS, I ran the exact commands and achieved a successful installation, however I did still receive a similar error message, in fact the identical error message was received.

The MySQL version in the other environmment is:
mysql Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (x86_64) using readline 6.3

The difference between Cloud9 and the separate AWS Instance is that when I’m logged into MySQL I receive the following responses from running a convert_tz() function call:

Cloud9:
mysql> select DATE_FORMAT(convert_tz(now(), ‘UTC’, ‘Australia/Perth’), ‘%e/%c/%Y %H:%i’) AS PERTH_TIME;
±---------------------------------------------------------------------------+
| PERTH_TIME |
±---------------------------------------------------------------------------+
| NULL |
±---------------------------------------------------------------------------+
1 row in set (0.01 sec)

Amazon AWS:

mysql> select DATE_FORMAT(convert_tz(now(), ‘UTC’, ‘Australia/Perth’), ‘%e/%c/%Y %H:%i’) AS PERTH_TIME;
±---------------------------------------------------------------------------+
| PERTH_TIME |
±---------------------------------------------------------------------------+
| 18/7/2016 12:25 |
±---------------------------------------------------------------------------+
1 row in set (0.00 sec)

The obvious differences between environments are the database versions, but I would have assumed with Cloud9’s database being a newer version that I would have had success with it. Am I missing something here? Is there a way of getting this functioning in Cloud9, like I can in an external Amazon AWS Instance?


#2

I was able to get everything to populate by inputting the following in the C9 shell:

mysql_tzinfo_to_sql /usr/share/zoneinfo

it’s exactly what you have before you added the pipe symbol and mysql permissions. you don’t need that. all tables were instantaneously populated.