[mythtv-users] lost recordings and database orphans

[Michael T. Dean]

On 03/24/2010 12:27 AM, Greg Woods wrote:
> I've got a problem that the old myth.find_orphans.pl script was supposed
> to solve, but it no longer works:
>
> http://www.gossamer-threads.com/lists/mythtv/users/425156#425156
>    

Using the script to remove orphaned metadata is not nearly as dangerous 
as using those scripts to insert metadata into the database for orphaned 
files.

> What happened is that I lost a hard drive that had about 3/4 of my
> recordings on it. I don't mind losing the recordings as much as the fact
> that I now have a ton of entries in the database for recording files
> that no longer exist. Aside from totally cluttering up the Watch
> Recordings display, which is a major annoyance, even worse is that these
> episodes will not re-record, because the database has recorded that I
> already have them. There are over 600 lost recordings, so going through
> the GUI and manually removing the ones that are missing would take days.
> There has to be a better way.
>
> I would like to take a stab at cleaning up the database, but determining
> all the interrelationships between the tables is probably a bit beyond
> my SQL skills. Is there anyone who knows the database enough to at least
> tell me which tables need to be modified? For example, I can find the
> "basename" values in the "recorded" table and determine which entries in
> this table correspond to missing files. I could probably even script
> something that would check them and delete all the entries in this table
> that correspond to missing files. But I will bet there are references in
> other tables that point to the entries in this table and I will probably
> hose things up royally if I just do that.
>    

There is a lot more stuff...

Instead of deleting anything, just change recorded.recgroup to Deleted 
(exactly as shown--capitalization and all).  Then, you can use 
http://svn.mythtv.org/trac/browser/trunk/mythtv/contrib/maintenance/flush_deleted_recgroup.pl 
with the --force argument to have MythTV properly delete the metadata.

The only issue with that approach is that it won't mark the episodes to 
allow re-record.  I /think/ you can fix that with a slightly modified 
approach.

1. First, make sure your Deleted recording group is empty.  To do this, 
you have 2 options:
   a. If you'd like to keep any existing Deleted recordings you have:
     i. go to Watch Recordings
     ii. find the Deleted recording group in the left column (you may 
have to change Group View andor Group Filter to see it)
     iii. then MENU|Add this Group to Playlist
     iv. then MENU|Playlist options|Change Recording Group
     v. type in some temporary group name, like "old Deleted"
   b. If you don't care to keep existing Deleted recordings:
     i. go to Watch Recordings
     ii. find the Deleted recording group in the left column (you may 
have to change Group View andor Group Filter to see it)
     iii. then MENU|Add this Group to Playlist
     iv. then MENU|Playlist options|Delete (and don't worry--any that 
you had already deleted with "Delete, and allow re-record" will still be 
allowed to re-record)
2. Do as above to change the recorded.recgroup to Deleted for all 
recordings whose files are missing
3. Make a script to "touch <basename>" for each recording you put in the 
Deleted recording group in step 2 and run it in any directory in the 
Default storage group
4. Go to Watch Recordings
5. Find the Deleted recording group in the left column (you may have to 
change Group View andor Group Filter to see it)
6. Then select MENU|Add this Group to Playlist
7. Then MENU|Playlist options|Delete, and allow re-record
8. If you chose 1.a, above, repeat the steps for 1.a, except change the 
recording group back to Deleted

Mike