Difference between revisions of "MySQL Time Zone Tables"
(Added info about UTC being missing from some distros) |
m |
||
(9 intermediate revisions by 2 users 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 | + | 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, | + | To see if the tables are already loaded (using the same query |
+ | that ''mythbackend''/''mythtv-setup'' uses to check them) start | ||
+ | ''mysql''. | ||
+ | <pre> | ||
+ | For 0.27+, type: | ||
+ | SELECT CONVERT_TZ(NOW(), 'SYSTEM', 'Etc/UTC'); | ||
− | + | For 0.26, type: | |
− | SELECT CONVERT_TZ( | + | SELECT CONVERT_TZ(NOW(), 'SYSTEM', 'UTC'); |
</pre> | </pre> | ||
− | If the response looks | + | If the response looks similar to this 0.27+ response: |
<pre> | <pre> | ||
− | + | + | +----------------------------------------+ |
− | | CONVERT_TZ( | + | | CONVERT_TZ(NOW(), 'SYSTEM', 'Etc/UTC') | |
− | + | + | +----------------------------------------+ |
− | | | + | | 2013-11-14 14:07:58 | |
− | + | + | +----------------------------------------+ |
</pre> | </pre> | ||
the tables are loaded and no further action is required. | the tables are loaded and no further action is required. | ||
Line 29: | Line 34: | ||
If the response looks like: | If the response looks like: | ||
<pre> | <pre> | ||
− | + | + | +----------------------------------------+ |
− | | CONVERT_TZ( | + | | CONVERT_TZ(NOW(), 'SYSTEM', 'Etc/UTC') | |
− | + | + | +----------------------------------------+ |
− | | NULL | + | | NULL | |
− | + | + | +----------------------------------------+ |
</pre> | </pre> | ||
Line 41: | Line 46: | ||
type: | type: | ||
<pre> | <pre> | ||
− | mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p | + | mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql |
</pre> | </pre> | ||
− | + | 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. | ||
− | + | == 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 <code>cat</code> that file to the 2nd command. Note that the bad entries | ||
+ | could be on one line or be spread over several. Three line example: | ||
− | <pre> | + | <pre> |
− | |||
− | |||
− | |||
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES | INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES | ||
− | (@time_zone_id, 0, 0, 0, ' | + | (@time_zone_id, 0, 0, 0, 'Local time zone must be set--see zic manual page') |
− | + | ; | |
− | + | </pre> | |
− | </pre> | + | |
+ | <pre> | ||
+ | mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/tz.fix | ||
+ | edit /tmp/tz.fix | ||
+ | cat /tmp/tz.fix | mysql -u root -p mysql | ||
+ | </pre> | ||
+ | |||
+ | == 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: | References: |
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.
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:
- http://www.gossamer-threads.com/lists/mythtv/commits/519706
- http://www.gossamer-threads.com/lists/mythtv/dev/519711
- http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html
- http://dev.mysql.com/doc/refman/5.0/en/mysql-tzinfo-to-sql.html
- http://wiki.qnap.com/wiki/How_to_install_MythTV#Missing_timezone_support.C2.A0