Abe Miessler Abe Miessler - 4 months ago 122
SQL Question

Unable to load timezones?

I am attempting to load timezones into my MySql instance (that is running on Unbuntu) so that I can use the CONVERT_TZ function but when I run the command below:

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


I get the following output and when I try to use
CONVERT_TZ
it just returns
NULL
still:


Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh87' as time
zone. Skipping it. Warning: Unable to load
'/usr/share/zoneinfo/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Asia/Riyadh89' as time
zone. Skipping it. Warning: Unable to load
'/usr/share/zoneinfo/Mideast/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/Mideast/Riyadh88' as time
zone. Skipping it. Warning: Unable to load
'/usr/share/zoneinfo/Mideast/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time
zone. Skipping it. Warning: Unable to load
'/usr/share/zoneinfo/posix/Asia/Riyadh87' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Asia/Riyadh88' as
time zone. Skipping it. Warning: Unable to load
'/usr/share/zoneinfo/posix/Asia/Riyadh89' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/posix/Mideast/Riyadh87'
as time zone. Skipping it. Warning: Unable to load
'/usr/share/zoneinfo/posix/Mideast/Riyadh88' as time zone. Skipping
it. Warning: Unable to load
'/usr/share/zoneinfo/posix/Mideast/Riyadh89' as time zone. Skipping
it. Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh87'
as time zone. Skipping it. Warning: Unable to load
'/usr/share/zoneinfo/right/Asia/Riyadh88' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/right/Asia/Riyadh89' as
time zone. Skipping it. Warning: Unable to load
'/usr/share/zoneinfo/right/Mideast/Riyadh87' as time zone. Skipping
it. Warning: Unable to load
'/usr/share/zoneinfo/right/Mideast/Riyadh88' as time zone. Skipping
it. Warning: Unable to load
'/usr/share/zoneinfo/right/Mideast/Riyadh89' as time zone. Skipping
it. Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time
zone. Skipping it.


Does anyone have an idea why this would be happening?

PS:

I'm using the following method to convert datetimes:

CONVERT_TZ(NOW(),'UTC', 'PCT')


Is it possible that I'm just converting incorrectly and that is the cause of the NULL results?

Answer

This is a bug with the timezone files for Riyadh* on Debian:

http://bugs.mysql.com/bug.php?id=20545

The recommended workaround is to simply ignore the warning (if you don't need those timezone of course):

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

After importing TZ data with the --force option, you will be able to use CONVERT_TZ. Here is a transcription of what I've just done:

sh$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -uroot -p mysql --force 2> /dev/null
Enter password: ********
sh$ mysql -p
Enter password: ********
Server version: 5.1.49-3 (Debian)

mysql> select convert_tz(now(), 'UTC', 'CET');
+---------------------------------+
| convert_tz(now(), 'UTC', 'CET') |
+---------------------------------+
| 2013-08-13 21:04:34             |
+---------------------------------+
1 row in set (0.00 sec)