Migrating data from ancient database to current

[mathog]

Our DVR computer glitched last week, apparently from a SATA cable that slowly worked its way loose since the system was assembled eight years ago. The database had to be repaired through all sorts of heroic methods so I don't have a whole lot of confidence in parts of it. There are saved dumps (*.sql) to work with, but the only complete ones are after the corruption was repaired. Still, the external names for the saved recordings and their times and dates are in there. Since the system was already in pieces it is going to get a new disk, fresh OS, and new MythTV version. The old one was version 0.21 of MythTV, so I'm going to go out on a limb here and guess that the schema in the current version is pretty different. I'm fine with manually configuring the new MythTV, but would really like to be able to import the descriptions of the saved programs.

How would I go about that?

Thanks.

[pvr4me]

The current version is 29.0. The release notes say:

Data Migration: 29 will only support direct upgrades from 0.22 or higher. If you are running an older version, you will need to upgrade to 0.22, 0.23 or 0.24 then upgrade to 29.

https://www.mythtv.org/wiki/Release_Notes_-_29

So you will need to do your upgrade in two stages: first to (probably) 0.24 and then to 29.0. The following page has a good discussion of full and partial restore strategies:

https://www.mythtv.org/wiki/Database_Backup_and_Restore

Have you run optimize_mythdb.pl ? It uses MySQL utilities to repair and optimize each of Myth's db tables. If it runs successfully on your database, I'd say your best bet is to migrate the entire database. Only go with a partial restore if problems are indicated.

Craig

[mathog]

Even after the database was repaired as best I could the back end would not start. Sad experience has taught me that when a complex package is broken to that extent upgrading is not at all likely to succeed. It might also appear to succeed but result in hidden defects which come back to screw things up later. Who knows if I can fix the existing problems, and since the software is so old, does it really make sense to try?

My thinking was that it would be best to start with a clean database and then import just enough information about the recordings to be able to view them. The recordings should mostly be fine since they were not stored in the database.

Just for the sake of argument, let's say that from the sql dump information for each program is extracted like:

external file names (mpg, png, if there is one)
name
date
channel
description
length

Is there some script around for importing it? If not, my vague plan was to get mythtv 29 running, record just one or two shows, dump that, find the relevant tables, then insert with some scripts the above data into the corresponding tables in the dumped sql file, and restore from that.

[pvr4me]

https://www.mythtv.org/wiki/Database_Ba ... f_a_backup

[mathog]

This worked:

Code: Select all

grep 'INSERT INTO `recorded`' old_dump.sql >/tmp/insert_recorded.sql
gunzip -c $MOST_RECENT_0.29_BACKUP.sql.gz \
  | grep 'INSERT INTO `recorded`' >/tmp/example.sql
nedit /tmp/insert_recorded.sql /tmp/example.sql
# copy the record description section from the latter to the former and insert before "values"
# delete these fields, which were not present in 0.21: season, episode, inetref, bookmarkupdate, recgroupid, recordedid, inputname
gunzip -c $MOST_RECENT_0.29_BACKUP.sql.gz >/tmp/fix.sql
# Using whichever tools you want, insert the "insert_recorded.sql" line after the existing INSERT INTO `recorded` line
gzip /tmp/fix.sql
/usr/share/mythconverg_restore.pl --directory /tmp --filename fix.sql.gz --create_database --drop_database

after that, when mythfrontend started it had all the old programs and they all played.

[Dave Coleman]

after that, when mythfrontend started it had all the old programs and they all played.

Got it working! Thanks for sharing!