Cannot connect: Time zone tables don't appear to be loaded in MySQL


(Todd Nemet) #1

This error appears when connecting Looker to a MySQL server that doesn’t have the timezone tables loaded. Before Looker connects to a MySQL database it will check for the presence of these tables and won’t connect if it doesn’t find them.

The solution is to have your MySQL administrator load these tables using a utility called mysql_tzinfo_to_sql, which needs to be run as the MySQL root user. More information is available at this page.

Note that in the comments of that MySQL docs page, a user listed the actual SQL that is generated by mysql_tzinfo_to_sql. The SQL they provided is:

CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1676 DEFAULT CHARSET=utf8 COMMENT='Time zones';

CREATE TABLE `time_zone_leap_second` (
  `Transition_time` bigint(20) NOT NULL,
  `Correction` int(11) NOT NULL,
  PRIMARY KEY (`Transition_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Leap seconds information for time zones';

CREATE TABLE `time_zone_name` (
  `Name` char(64) NOT NULL,
  `Time_zone_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone names';

CREATE TABLE `time_zone_transition` (
  `Time_zone_id` int(10) unsigned NOT NULL,
  `Transition_time` bigint(20) NOT NULL,
  `Transition_type_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`Time_zone_id`,`Transition_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transitions';

CREATE TABLE `time_zone_transition_type` (
  `Time_zone_id` int(10) unsigned NOT NULL,
  `Transition_type_id` int(10) unsigned NOT NULL,
  `Offset` int(11) NOT NULL DEFAULT '0',
  `Is_DST` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `Abbreviation` char(8) NOT NULL DEFAULT '',
  PRIMARY KEY (`Time_zone_id`,`Transition_type_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zone transition types';

(Todd Nemet) #2

A few more notes:

  • No permissions on the time zone tables need to be adjusted after loading them.

  • To test if the tables are loaded:

    $ mysql -u root -e “show tables in mysql like ‘time_zone%’”

+------------------------------+
| Tables_in_mysql (time_zone%) |
+------------------------------+
| time_zone                    |
| time_zone_leap_second        |
| time_zone_name               |
| time_zone_transition         |
| time_zone_transition_type    |
+------------------------------+

(Brett Sauve) #4

One of our users had difficulty getting mysql_tzinfo_to_sql to work on his Ubuntu install. He found this mysql convert_tz command returns NULL post on Stackoverflow to be helpful.

Essentially, the post describes an extra line break in mysql_tzinfo that needs to be removed manually. It looks like a fairly straightforward fix.


(Todd Nemet) #5

Step by step instructions to make things a bit more clear:

###Find the zone info files on the server running MySQL

They might be in /usr/share/zoneinfo or /usr/share/lib/zoneinfo

Here is an example on an Amazon EMR instance:

[hadoop@ip-10-55-0-168 ~]$ cd /usr/share/zoneinfo/
[hadoop@ip-10-55-0-168 zoneinfo]$ ls
Africa      Chile    GB         Indian       MST         PRC        UTC
America     CST6CDT  GB-Eire    Iran         MST7MDT     PST8PDT    WET
Antarctica  Cuba     GMT        iso3166.tab  Navajo      right      W-SU
Arctic      EET      GMT0       Israel       NZ          ROC        zone.tab
Asia        Egypt    GMT-0      Jamaica      NZ-CHAT     ROK        Zulu
Atlantic    Eire     GMT+0      Japan        Pacific     Singapore
Australia   EST      Greenwich  Kwajalein    Poland      Turkey
Brazil      EST5EDT  Hongkong   Libya        Portugal    UCT
Canada      Etc      HST        MET          posix       Universal
CET         Europe   Iceland    Mexico       posixrules  US

Here is an example on my laptop running 10.11.3:

[2001|20:09:43] ~$ cd /usr/share/zoneinfo
[2002|12:33:41] zoneinfo$ ls
+VERSION     Canada/      GB           Iran         NZ-CHAT      UCT
Africa/      Chile/       GB-Eire      Israel       Navajo       US/
America/     Cuba         GMT          Jamaica      PRC          UTC
Antarctica/  EET          GMT+0        Japan        PST8PDT      Universal
Arctic/      EST          GMT-0        Kwajalein    Pacific/     W-SU
Asia/        EST5EDT      GMT0         Libya        Poland       WET
Atlantic/    Egypt        Greenwich    MET          Portugal     Zulu
Australia/   Eire         HST          MST          ROC          iso3166.tab
Brazil/      Etc/         Hongkong     MST7MDT      ROK          posixrules
CET          Europe/      Iceland      Mexico/      Singapore    zone.tab
CST6CDT      Factory      Indian/      NZ           Turkey

###Make sure that you have the mysql_tzinfo_to_sql program in your path

On EMR machine:

[hadoop@ip-10-55-0-168 etc]$ which mysql_tzinfo_to_sql
/usr/bin/mysql_tzinfo_to_sql

On my laptop:

[2003|12:33:41] zoneinfo$ which mysql_tzinfo_to_sql 
/usr/local/mysql/bin/mysql_tzinfo_to_sql

###Verify that the program will run correctly
On Amazon EMR:

[hadoop@ip-10-55-0-168 etc]$ mysql_tzinfo_to_sql /usr/share/zoneinfo/ | wc -l
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.
137409
[hadoop@ip-10-55-0-168 etc]$ mysql_tzinfo_to_sql /usr/share/zoneinfo/ | head
TRUNCATE TABLE time_zone;
TRUNCATE TABLE time_zone_name;
TRUNCATE TABLE time_zone_transition;
TRUNCATE TABLE time_zone_transition_type;
INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_zone_id);
INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
 (@time_zone_id, -1830383032, 1)
;
[hadoop@ip-10-55-0-168 etc]$ mysql_tzinfo_to_sql /usr/share/zoneinfo/ | wc -l
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.
137409

On my laptop:

[2005|12:40:06] zoneinfo$ mysql_tzinfo_to_sql /usr/share/zoneinfo | head
Warning: Unable to load '/usr/share/zoneinfo/+VERSION' as time zone. Skipping it.
TRUNCATE TABLE time_zone;
TRUNCATE TABLE time_zone_name;
TRUNCATE TABLE time_zone_transition;
TRUNCATE TABLE time_zone_transition_type;
INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
SET @time_zone_id= LAST_INSERT_ID();
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_zone_id);
INSERT INTO time_zone_transition (Time_zone_id, Transition_time, Transition_type_id) VALUES
 (@time_zone_id, -1830383032, 1)
;
[2006|12:40:08] zoneinfo$ mysql_tzinfo_to_sql /usr/share/zoneinfo | wc -l
Warning: Unable to load '/usr/share/zoneinfo/+VERSION' 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/zone.tab' as time zone. Skipping it.
   45899

Note that some Ubuntu distros require editing the mysql_tzinfo_to_sql script. See the comments above for more information.

###Pipe the output from the script to mysql running as root into the mysql database

On Amazon EMR:

[hadoop@ip-10-55-0-168 etc]$ mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root 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.

On my laptop:

[2008|12:43:02] /usr/share/zoneinfo$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql
Warning: Unable to load '/usr/share/zoneinfo/+VERSION' 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/zone.tab' as time zone. Skipping it.

###Verify that the tables are loaded with show tables

On Amazon EMR:

[hadoop@ip-10-55-0-168 etc]$ mysql -u root -e "show tables in mysql like 'time_zone%'"
+------------------------------+
| Tables_in_mysql (time_zone%) |
+------------------------------+
| time_zone                    |
| time_zone_leap_second        |
| time_zone_name               |
| time_zone_transition         |
| time_zone_transition_type    |
+------------------------------+

On my laptop:

[2009|12:43:21] /usr/share/zoneinfo$ mysql -u root -e "show tables in mysql like 'time_zone%'"
+------------------------------+
| Tables_in_mysql (time_zone%) |
+------------------------------+
| time_zone                    |
| time_zone_leap_second        |
| time_zone_name               |
| time_zone_transition         |
| time_zone_transition_type    |
+------------------------------+

Verify the table counts if you want

On Amazon EMR:

[hadoop@ip-10-55-0-168 etc]$ for i in time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type
> do echo $i
> mysql -u root -e "select count(*) from mysql.$i"
> done
time_zone
+----------+
| count(*) |
+----------+
|     1747 |
+----------+
time_zone_leap_second
+----------+
| count(*) |
+----------+
|        0 |
+----------+
time_zone_name
+----------+
| count(*) |
+----------+
|     1747 |
+----------+
time_zone_transition
+----------+
| count(*) |
+----------+
|   116968 |
+----------+
time_zone_transition_type
+----------+
| count(*) |
+----------+
|     8572 |
+----------+

On my laptop

[2014|12:48:07] zoneinfo$ for i in time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type; do echo $i; mysql -u root -e "select count(*) from mysql.$i"; done
time_zone
+----------+
| count(*) |
+----------+
|      478 |
+----------+
time_zone_leap_second
+----------+
| count(*) |
+----------+
|        0 |
+----------+
time_zone_name
+----------+
| count(*) |
+----------+
|      478 |
+----------+
time_zone_transition
+----------+
| count(*) |
+----------+
|    33627 |
+----------+
time_zone_transition_type
+----------+
| count(*) |
+----------+
|     2370 |
+----------+

(Todd Nemet) #6

If you are on an OS that doesn’t have a zoneinfo directory, mainly Windows, then you will need to download the time zone information from this URL:

http://dev.mysql.com/downloads/timezones.html

You most likely need timezone_2016a_leaps_sql.zip, which is the POSIX standard for version 5.7+.

Unzip this file and then run mysql_tzinfo_to_sql on it to create the right tables in mysql.