Unable to load timezones?

database, mysql

Solution

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)

Problem

I am attempting to load timezones into my MySql instance (that is running on Ubuntu) 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?

Original source