As a result of the Zap2It Labs shutdown, I recently had to upgrade my MythTV installation from 0.20.1 to 0.20.2.
Graciously, Zap2It Labs was scheduled to shut down on September 1, but they extended guide data past that date. For a few days after the shutdown, it was possible to fetch the full 14 days of upcoming data. Once the data service was deactivated, no more data was put into the database and the amount of remaining data dwindled. As I stared at the MythTV status screen, it was obvious that my schedule data would run out during an upcoming trip:
(Furthermore, the new TV season starts in late September, so I’d also miss all the season openers on the week of the September 24!)
I’d been delaying the upgrade because Gentoo’s Portage system wanted to upgrade from MySQL version 4 to MySQL version 5. The database upgrade seemed like it had high potential to go wrong, and would not add obvious functionality to MythTV. I’d already experienced one crash that took out the database, and it was painful enough to recover from that.
However, my hand was forced by the Zap2It shutdown. I was out of the country attending an IEEE ad hoc meeting, and I didn’t want to do the upgrade remotely. Fortunately, Zap2It extended listing data beyond their September 1 shutdown date, so I had a few days grace period after returning home.
I wasn’t concerned about the switchover to SchedulesDirect within MythTV, which was described as drop-dead simple everywhere. It was getting there.
MySQL upgrade. Thankfully, I heeded the advice to back up the database before starting. The upgrade process doesn’t convert over the data, and it turned out to be much simpler just to delete the existing database, install MySQL 5 from scratch, and have MythTV set up the new database.
When I attempted to restore the database, I received the following error message:
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
That’s in part because the default character set needs to be configured on the database. What happened is that the table definition for the “people” table uses the name as a primary key, and it doesn’t allocate enough space for the table unless you restrict the character set that field is stored in.
mysql> ALTER DATABASE mythconverg DEFAULT CHARACTER SET latin1;
However, when I attempted to restore the database from my backup snapshot, I received another error message:
ERROR 1062 (23000) at line 24800: Duplicate entry ‘Monika Sanchez’ for key 2
The error was caused by having two entries with the same text value:
mythtv@myth ~ $ cat mythtv_backup.sql | grep “Monika Sanchez”
INSERT INTO people (person, name) VALUES (2590,’Monika Sanchez’);
INSERT INTO people (person, name) VALUES (622,’MÃƒÂ³nika SÃƒÂ¡nchez’);
At first, I tried removing the name “Monika Sanchez” from the database, but as it turns out, there are lots of duplicate spelling. The easiest thing to do is to not restore the people table.
mythtv@myth ~ $ cat mythtv_backup.sql | grep -v “INSERT INTO people” > mythtv_backup-nopeople.sql
After restoring the database, I figured the next step was to try repopulating the TV listings.
mythtv@myth ~ $ mythfilldatabase
2007-09-08 08:43:42.604 Using runtime prefix = /usr
QSqlDatabase: QMYSQL3 driver not loaded
QSqlDatabase: available drivers:
2007-09-08 08:43:42.619 New DB connection, total: 1
2007-09-08 08:43:42.620 Unable to connect to database!
2007-09-08 08:43:42.620 No error type from QSqlError? Strange…
QSqlQuery::exec: database not open
QSqlQuery::exec: database not open
2007-09-08 08:43:42.674 DB Error (KickDatabase):
No error type from QSqlError? Strange…
2007-09-08 08:43:42.730 Failed to init MythContext, exiting.
According to the Gentoo wiki, that error message indicates that there is no support for MySQL in the QT library. So, I tried rebuilding QT, and and figured that I’d go all the way and upgrade MythTV, too.
At this point, I’m about six hours and fifteen minutes into the upgrade.
MythTV upgrade. This is one of the most uneventful builds I’ve ever watched. Interesting, the Gentoo system started by checking out the 0.20.2 source from the subversion repository, rather than downloading a compressed source code archive.
Myth plugins. Most of the plugins were uneventful, though the MythWeb plugin broke. To use it, I had to remove PHP4 and the older version of Apache that was installed, and then reinstall Apache and PHP5 from scratch. One small change was needed in php.ini to view pages. The “allow_url_fopen” parameter needs to be set to “on” instead of “off.”
MythTV reconfiguration. With MythTV running again, it seemed anticlimactic to switch over to Schedules Direct. Signing up for the service took a couple of minutes, and Bruce Markey’s Schedules Direct configuration procedure took another few. In about 8 minutes, it was over.
By running mythfilldatabase with the SQL debug command, I even noticed that the “people” table I’d been unable to restore was recreated without intervention.
Finally: THE END!
Finally, after about seven hours of work, only 15 minutes of which were related to signing up for and configuring SchedulesDirect, I once again had a two-week window of guide data:
My lesson: don’t let MythTV upgrades slide so much in the future!