Removing orphaned TV recordings in mythtv

[purps]

Had to restore LMCE from an image that was about a month old, and of course there are now a load of mythtv recordings in the frontend for which the associated file doesn't exist (they were stored on another drive). If I try to delete them, they just reappear, mocking me.

What is the best way to remove these orphaned recordings from the database? I did find a very handy find_orphans.py script, but it is intended for 0.24. I tried to run it anyway, but it gave errors as apparently the bindings are not the same. I understand that the script can be modified for 0.23, but have read horror stories of it messing myth up big time.

So how do you all do it? This must have been an issue for somebody else before. Manually deleting the recordings from the hard drive would also give the same problem I guess.

Cheers,
Matt.

[jimbodude]

Dealing with MythTV database inconsistencies is sometimes a pain, and usually takes some SQL skills.

The last time this happened to me, I seem to remember deleting all rows from a few tables to start over without losing my recording schedules.  I believe there is also an older version of the orphan script - you can probably get it from MythTV's SVN.  In either case - back up the database first so you don't screw yourself.

The MythTV database is pretty flexible, in my experience.  You can usually dump it out and put it on a new system pretty easily.  So long as you are going to the same MythTV version or newer, MythTV will accept the data without complaint.  Just make sure you only deal with the media-related tables for recordings and schedules and such, otherwise LMCE will get confused.  So next time you reinstall - just take the MythTV database with you.

[purps]

I think that is really good advice, thank you. I will just put up with deleting them one by one for now (I've been doing a few every night for about 2 weeks now...), and I will look into backing up and restoring the MythTV database next time.

I found this page useful if anybody else is interested http://www.mythtv.org/wiki/Database_Backup_and_Restore

Cheers,
Matt.

[blackoper]

I ran the myth cleanup script from the contribs section (nonlinuxmce backend though) - myth.find_orphans.pl

It took a while to get it to work as I recall and I had to mess with the usage

[purps]

That script is intended for 0.24; it wouldn't run with my 0.23.

[blackoper]

I've used that script on .18 .21 .22 and .23 I've never had a problem. Download .23 from mythtvs website and then use the myth.find_orphans.pl from that after you uncompress. It's in the /mythtv/contrib section somewhere. Maybe the newest version of the script has been modified

[l3mce]

I've used that script on .18 .21 .22 and .23 I've never had a problem. Download .23 from mythtvs website and then use the myth.find_orphans.pl from that after you uncompress. It's in the /mythtv/contrib section somewhere. Maybe the newest version of the script has been modified

Can you please post your working script here so that perhaps we can perhaps include it somewhere?

[blackoper]

It may take a bit. I'll have to hook back up my backend to pull it (its been taken down for a bit as I only have one projector at the moment and no other tvs)

[purps]

blackoper, does this look like it? https://github.com/MythTV/mythtv/blob/fixes%2F0.23/mythtv/contrib/maintenance/myth.find_orphans.pl

Apparently this is an "older perl script that only operated on the local backend and filesystem" - is that going to bother us?

Cheers,
Matt.

[purps]

Well I went for it, and got the message...

Code: [Select]

dcerouter_1016833:~/Documents# ./myth.find_orphans.pl
DBI connect('database=mythconverg:host=dcerouter','mythtv',...) failed: Access denied for user 'mythtv'@'192.168.80.1' (using password: YES) at ./myth.find_orphans.pl line 114
Cannot connect to database mythconverg on host dcerouter:
What does it mean? Line 114 is...

Code: [Select]

if (!($dbh = DBI->connect("dbi:mysql:database=$opt_database:host=$opt_dbhost","$opt_user","$opt_pass"))) {
Is it because there is no password as such for the user "mythtv"?

[l3mce]

Change lines 51 and 52 from:

Code: [Select]

my $opt_user = "mythtv";
my $opt_pass = "mythtv";to

Code: [Select]

my $opt_user = "";
my $opt_pass = "";
and run

Code: [Select]

./myth.find_orphans.pl --dodbdelete If you are trusting. Running the file by itself will first show you the list of orphans/unregistered files. Works well. Output is too verbose to show.
 

[purps]

Appears to work for me as well! Thank you very much.

New script here http://pastebin.com/a2XBuN6i (might make life a LITTLE bit easier for somebody).

Now then, is this the kind of thing that will be added in properly, or should I add this information to the wiki page?

Cheers,
Matt.

[l3mce]

I am working on putting a button in web admin under the "Delete ALL missing files from database". The first click runs the script to detect in an execute log window, then a popup confirmation should actually delete. I figure this is better than two separate buttons.

I don't know how people will hose their db in the future, and believe there should be a way to preview the changes before committing them.

[purps]

Legend.

[bulek]

Appears to work for me as well! Thank you very much.

New script here http://pastebin.com/a2XBuN6i (might make life a LITTLE bit easier for somebody).

Now then, is this the kind of thing that will be added in properly, or should I add this information to the wiki page?

Cheers,
Matt.

Hi,

thanks for effort. Is this supposed to work on 8.10Beta or maybe also on 7.10 ?

Thanks,

regards,
Bulek.