Recovering lost MythTV recordings

    I had one of those moments tonight, and accidentally dropped the mythconverg database on my production MythTV instance, not the development one. This made me sad. Luckily I had a backup which was only a week old (although I am now running night backups of that database).

    Recovery wasn't too bad once I wrote some code. The steps:

    • Restore from backup
    • Don't run mythfilldatabase (it will clear out old guide data, and we need it)
    • Apply my funky patch to
    • Run
    • Run mythfilldatabase

    And all is well again. The patch uses the guide data from the database to make an educated guess about the title, subtitle and description of the recordings which are missing from the database. Here's the patch:

      ---     (revision 11681)
      +++     (working copy)
      @@ -185,6 +185,7 @@
      +print "db = dbi:mysql:database=$database:host=$dbhost user = $user pass = $pass\n";
       my $dbh = DBI->connect("dbi:mysql:database=$database:host=$dbhost",
                      "$user","$pass") or die "Cannot connect to database ($!)\n";
      @@ -314,6 +315,7 @@
           # have enough to look for an past recording?
           if ($ssecond) {
      +        print "Checking for a recording...\n";
               $starttime = "$syear$smonth$sday$shour$sminute$ssecond";
               my $guess = "select title, subtitle, description from oldrecorded where chanid=(?) and starttime=(?)";
      @@ -333,6 +335,24 @@
               print "End time:   $emonth/$eday/$eyear - $ehour:$eminute:$esecond\n";
      +    # what about checking for guide data?
      +    if($guess_description =~ /^Recovered file/) {
      +        print "Checking for guide data...\n";
      +        my $guess = "select title, subtitle, description from program where chanid='$channel'".
      +                    " and starttime='$syear-$smonth-$sday $shour:$sminute:$ssecond'";
      +        print "$guess\n";
      +        $sth = $dbh->prepare($guess);
      +        $sth->execute()
      +            or die "Could not execute ($guess)\n";
      +        if (my @row = $sth->fetchrow_array) {
      +            $guess_title = $row[0];
      +            $guess_subtitle = $row[1];
      +            $guess_description = $row[2];
      +            print "Using guide data informaton for defaults\n";
      +        }
      +    }
           my $newtitle = $guess_title;
           my $newsubtitle = $guess_subtitle;
           my $newdescription = $guess_description;

    You can download the patch here or read the bug report.

    Tags for this post: mythtv database restore
    Related posts: MythBuntu 8.10 just made me sad; Nova database continuous integration; Time to document my PDF testing database; Juno nova mid-cycle meetup summary: DB2 support; Exploring a single database migration

posted at: 22:12 | path: /mythtv | permanent link to this entry