Difference between revisions of "MySQL Time Zone Tables"
(0.26 MySQL Requirement, based on -dev and -commit lists info from Mike Dean, tested on ubuntu 12.04) |
|||
| (10 intermediate revisions by 6 users not shown) | |||
| Line 1: | Line 1: | ||
| − | [[Category:MySQL]] | + | [[Category:MySQL]] [[Category:Troubleshooting]] |
Starting with MythTV version 0.26, the time zone tables must be loaded | Starting with MythTV version 0.26, the time zone tables must be loaded | ||
| − | in MySQL. | + | 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, enter mysql and type: | To see if the tables are already loaded, enter mysql and type: | ||
| Line 26: | Line 24: | ||
</pre> | </pre> | ||
the tables are loaded and no further action is required. | the tables are loaded and no further action is required. | ||
| − | |||
{{Note box|If the time zone tables are updated, then the | {{Note box|If the time zone tables are updated, then the | ||
procedure below should be repeated.}} | procedure below should be repeated.}} | ||
If the response looks like: | If the response looks like: | ||
| − | |||
<pre> | <pre> | ||
+--------------------------------------------------------------+ | +--------------------------------------------------------------+ | ||
| Line 47: | Line 43: | ||
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p<yourpassword> mysql | mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p<yourpassword> mysql | ||
</pre> | </pre> | ||
| + | |||
| + | Remove the <code>-p<yourpassword></code> from the above if you don't have one. | ||
Restart MySQL, and repeat the initial test. | Restart MySQL, and repeat the initial test. | ||
| + | |||
| + | == Missing UTC Time Zone == | ||
| + | |||
| + | It appears that some distributions do not contain info for the UTC time zone, which seems to be the timezone that mythbackend and mythtv-setup check for before starting. Some distributions may have something called Etc/UTC, but not UTC! | ||
| + | |||
| + | This can be added manually by executing the following: | ||
| + | |||
| + | <pre>> mysql -u root -p mysql | ||
| + | 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 ('UTC', @time_zone_id); | ||
| + | INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES | ||
| + | (@time_zone_id, 0, 0, 0, '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; | ||
| + | </pre> | ||
| + | |||
| + | WARNING - this fix is currently unsupported and untested! | ||
References: | References: | ||
| Line 55: | Line 71: | ||
*http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html | *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://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 | ||
Revision as of 21:23, 26 November 2012
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, enter mysql and type:
SELECT CONVERT_TZ('2012-06-07 12:00:00', 'GMT', 'America/New_York');
If the response looks like:
+--------------------------------------------------------------+
| CONVERT_TZ('2012-06-07 12:00:00', 'GMT', 'America/New_York') |
+--------------------------------------------------------------+
| 2012-06-07 08:00:00 |
+--------------------------------------------------------------+
the tables are loaded and no further action is required.
Note: If the time zone tables are updated, then the
procedure below should be repeated.
If the response looks like:
+--------------------------------------------------------------+
| CONVERT_TZ('2012-06-07 12:00:00', 'GMT', 'America/New_York') |
+--------------------------------------------------------------+
| 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<yourpassword> mysql
Remove the -p<yourpassword> from the above if you don't have one.
Restart MySQL, and repeat the initial test.
Missing UTC Time Zone
It appears that some distributions do not contain info for the UTC time zone, which seems to be the timezone that mythbackend and mythtv-setup check for before starting. Some distributions may have something called Etc/UTC, but not UTC!
This can be added manually by executing the following:
> mysql -u root -p mysql
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 ('UTC', @time_zone_id);
INSERT INTO time_zone_transition_type (Time_zone_id, Transition_type_id, Offset, Is_DST, Abbreviation) VALUES
(@time_zone_id, 0, 0, 0, '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;
WARNING - this fix is currently unsupported and untested!
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
