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 myth.rebuilddatabase.pl
• Run myth.rebuilddatabase.pl
• 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:

Index: myth.rebuilddatabase.pl
===================================================================
--- myth.rebuilddatabase.pl     (revision 11681)
+++ myth.rebuilddatabase.pl     (working copy)
@@ -185,6 +185,7 @@
                'norename'=>\$norename
                );

+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.