[mythtv-users] How do I remove recordings without files

[Owen Townend]

Kirk Fitzpatrick wrote:
> Henrik Beckman wrote:
>> Ie got a bunch of these
>>
>> 2009-03-04 13:05:11.587 ERROR when trying to delete file:
>> /GetPlaybackURL/UNABLE/TO/FIND/LOCAL/FILE/ON/media/2098_20081123180345.mpg.
>>
>> File does
>> n't exist.  Database metadata will not be removed.
>>
>> How can I remove them.
>
> Here's a script I use that checks all DB entries have a file associated
> with them, otherwise it deletes them (the DB entries);
>

Hey,
Handy script, but it doesn't deal well with storage groups...
Perhaps something like this:

#!/bin/bash
# remove database listings that have no file associated with them
#
RECORDINGDIR="/myth/tv"
MYSQLUSER="mythtv"
MYSQLPASS="mythtv"
SQLSCRIPT="/tmp/remove_invalid_database_entries.sql"
#
# Create empty file or remove one that already exists
echo " " > $SQLSCRIPT
#
# Create a list of filenames from the database and parse them
#
for filename in `mysql -u $MYSQLUSER -p$MYSQLPASS --batch -e "SELECT
basename FROM recorded ORDER BY basename" mythconverg`
do
    # echo "record is $filename"
    # See if the file exists
    FOUND=1
    for dirname in `mysql -u $MYSQLUSER -p$MYSQLPASS --batch -e
"SELECT dirname FROM storagegroup ORDER BY dirname" mythconverg`
        if [ -s "${dirname}/${filename}" ]
        then
            # Found the file
            FOUND=0;
        fi
    done
    if [ $FOUND -eq 1 ]
    then
        # Add the delete query to the sql script
        echo "$filename does not exist in $RECORDINGDIR, removing.."
        echo "DELETE FROM mythconverg.recorded WHERE basename =
'$filename';" >> $SQLSCRIPT
    fi
done

    # Execute the SQL to remove database entries
    mysql -u $MYSQLUSER -p$MYSQLPASS mythconverg < $SQLSCRIPT

# Clean up the tmp file
rm -f $SQLSCRIPT