Difference between revisions of "MySQL Time Zone Tables"

From MythTV Official Wiki
Jump to: navigation, search
(Add warning template, adjust for 0.27+)
m
 
(5 intermediate revisions by one user not shown)
Line 5: Line 5:
 
message will apppear in the respective log explaining that.
 
message will apppear in the respective log explaining that.
  
This is important for users building from source, users loading MythTV from a package,
+
This is important for users building from source; users loading MythTV from a package,
 
for example Mythbuntu, should expect the tables to be loaded automatically.
 
for example Mythbuntu, should expect the tables to be loaded automatically.
  
 
To see if the tables are already loaded (using the same query
 
To see if the tables are already loaded (using the same query
 
that ''mythbackend''/''mythtv-setup'' uses to check them) start
 
that ''mythbackend''/''mythtv-setup'' uses to check them) start
''mysql'' and type:
+
''mysql''.
 
+
 
<pre>
 
<pre>
 +
For 0.27+, type:
 
SELECT CONVERT_TZ(NOW(), 'SYSTEM', 'Etc/UTC');
 
SELECT CONVERT_TZ(NOW(), 'SYSTEM', 'Etc/UTC');
 +
 +
For 0.26, type:
 +
SELECT CONVERT_TZ(NOW(), 'SYSTEM', 'UTC');
 
</pre>
 
</pre>
  
If the response looks similar to this:
+
If the response looks similar to this 0.27+ response:
  
 
<pre>
 
<pre>
Line 43: Line 46:
 
type:
 
type:
 
<pre>
 
<pre>
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p<yourpassword> mysql
+
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
 
</pre>
 
</pre>
  
Remove the <code>-p<yourpassword></code> from the above if you don't have one.
+
If the <code>mysql</code> command above fails, adjust it for the distribution in use.
 +
Try: <code>man mysql</code>.
  
 
Restart MySQL, and repeat the initial test.
 
Restart MySQL, and repeat the initial test.
Line 66: Line 70:
 
mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/tz.fix
 
mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/tz.fix
 
edit /tmp/tz.fix
 
edit /tmp/tz.fix
cat /tmp/tz.fix | mysql -u root -p<yourpassword> mysql
+
cat /tmp/tz.fix | mysql -u root -p mysql
 
</pre>
 
</pre>
  
== Missing UTC (0.26) or Etc/UTC (0.27+) Time Zone ==
+
== Missing UTC (0.26) or Etc/UTC (0.27 and above) Time Zone Definitions ==
  
 
It appears that some distributions do not contain information for the timezone that ''mythbackend'' and
 
It appears that some distributions do not contain information for the timezone that ''mythbackend'' and
''mythtv-setup'' check for before starting. In 0.27 and above the test is for: ''Etc/UTC', and in 0.26
+
''mythtv-setup'' check for before starting. In 0.27 and above the test is for: ''Etc/UTC'', and in 0.26
 
it's: ''UTC''.
 
it's: ''UTC''.
  
{{Warning box|This fix is not supported. At least one user has reported success using it.}}
+
To solve the problem, link an existing definition to the required one.
  
The 0.26 UTC entry can be added with the following (replace the two 'UTC' values with 'Etc/UTC' for 0.27+):
+
For 0.27+:
  
<pre>
+
  cd /usr/share/zoneinfo/Etc
&gt; mysql -u root -p mysql
+
  sudo ln -s ../Zulu UTC
INSERT INTO time_zone (Use_leap_seconds) VALUES ('N');
+
 
SET @time_zone_id= LAST_INSERT_ID();
+
For 0.26:
INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('UTC', @time_zone_id);
+
 
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
+
    cd /usr/share/zoneinfo
(@time_zone_id, 0, 0, 0, 'UTC');
+
    sudo ln -s Zulu UTC
ALTER TABLE time_zone_transition ORDER BY Time_zone_id, Transition_time;
+
 
ALTER TABLE time_zone_transition_type ORDER BY Time_zone_id, Transition_type_id;
+
Then, re-run the ''mysql_tzinfo_to_sql'' command above.
</pre>
+
  
 
References:
 
References:

Latest revision as of 23:12, 10 May 2014

Starting with MythTV version 0.26, the time zone tables must be loaded in MySQL. This should be done before attempting the upgrade. mythtv-setup and mythbackend will not start without the tables loaded and an error message will apppear in the respective log explaining that.

This is important for users building from source; users loading MythTV from a package, for example Mythbuntu, should expect the tables to be loaded automatically.

To see if the tables are already loaded (using the same query that mythbackend/mythtv-setup uses to check them) start mysql.

For 0.27+, type:
SELECT CONVERT_TZ(NOW(), 'SYSTEM', 'Etc/UTC');

For 0.26, type:
SELECT CONVERT_TZ(NOW(), 'SYSTEM', 'UTC');

If the response looks similar to this 0.27+ response:

+----------------------------------------+
| CONVERT_TZ(NOW(), 'SYSTEM', 'Etc/UTC') |
+----------------------------------------+
| 2013-11-14 14:07:58                    |
+----------------------------------------+ 

the tables are loaded and no further action is required.

Important.png Note: If the time zone tables are updated, then the procedure below should be repeated.

If the response looks like:

+----------------------------------------+
| CONVERT_TZ(NOW(), 'SYSTEM', 'Etc/UTC') |
+----------------------------------------+
| NULL                                   |
+----------------------------------------+

then the tables must be loaded.

Assuming the distribution's time zone information is in /usr/share/zoneinfo, type:

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

If the mysql command above fails, adjust it for the distribution in use. Try: man mysql.

Restart MySQL, and repeat the initial test.

Data too long for column 'Abbreviation' Error

Some users are getting the above message. The solution is to save the output of the 1st command in a file. Edit the file, search and delete all offending entries. Then cat that file to the 2nd command. Note that the bad entries could be on one line or be spread over several. Three line example:

INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
(@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page')
;
mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/tz.fix
edit /tmp/tz.fix
cat /tmp/tz.fix | mysql -u root -p mysql

Missing UTC (0.26) or Etc/UTC (0.27 and above) Time Zone Definitions

It appears that some distributions do not contain information for the timezone that mythbackend and mythtv-setup check for before starting. In 0.27 and above the test is for: Etc/UTC, and in 0.26 it's: UTC.

To solve the problem, link an existing definition to the required one.

For 0.27+:

  cd /usr/share/zoneinfo/Etc
  sudo ln -s ../Zulu UTC

For 0.26:

   cd /usr/share/zoneinfo
   sudo ln -s Zulu UTC

Then, re-run the mysql_tzinfo_to_sql command above.

References: