Failed to load time zones?

Question

I am trying to load timezones into my MySql instance (which runs on Unbuntu) so that I can use the CONVERT_TZ function, but when I run the following command:

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 :

Warning: it is not possible to load '/ usr / share / zoneinfo / Asia / Riyadh87' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / Asia / Riyadh88' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / Asia / Riyadh89' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / Mideast / Riyadh87' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / Mideast / Riyadh88' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / Mideast / Riyadh89' as a time zone. Skip it. Warning: it is not possible to load '/usr/share/zoneinfo/iso3166.tab' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / posix / Asia / Riyadh87' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / posix / Asia / Riyadh88' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / posix / Asia / Riyadh89' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / posix / Mideast / Riyadh87' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / posix / Mideast / Riyadh88' as a time zone. Skipping This. Warning: it is not possible to load '/ usr / share / zoneinfo / posix / Mideast / Riyadh89' as a time zone. Skipping This. Warning: it is not possible to load '/ usr / share / zoneinfo / right / Asia / Riyadh87' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / right / Asia / Riyadh88' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / right / Asia / Riyadh89' as a time zone. Skip it. Warning: it is not possible to load '/ usr / share / zoneinfo / right / Mideast / Riyadh87' as a time zone. Skipping This. Warning: it is not possible to load '/ usr / share / zoneinfo / right / Mideast / Riyadh88' as a time zone. Skipping This. Warning: it is not possible to load '/ usr / share / zoneinfo / right / Mideast / Riyadh89' as a time zone. Skipping This. Warning: it is not possible to load '/usr/share/zoneinfo/zone.tab' as a time zone. Skip it.

Does anyone have an idea why this is happening?

PS:

I use the following method to convert datetimes:

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

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

Answer 1

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

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

The recommended workaround is to simply ignore the warning (unless you need this time zone, of course):

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

After importing TZ data with the --force option --force you can use CONVERT_TZ . Here is a transcription of what I just did:

 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) 

Answer 2

Unfortunately, Sylvain's solution did not work for me.

I managed to populate the time zone tables of our production server by exporting the tables from PHPMyAdmin my development computer (on Windows) and changing the script creation a bit.

Since the script is too long to share in the message, I will post it on my Google Drive.

Script installation (with new lines and comments):

https://drive.google.com/file/d/0B7iwL5F-hwr_YkItRXk2Z1VZdlE/view?usp=sharing

script with comments:

https://drive.google.com/file/d/0B7iwL5F-hwr_dWdjTDREcXNHQmM/view?usp=sharing

Be sure to use the version without comment. The comment version takes about 5 minutes to start and fails for some reason, while the split version takes less than 5 seconds to start.

Note To do this, you probably need to work as root .