[mythtv-users] Database damage?

[Stephen Worthington]

On Wed, 10 Feb 2021 11:44:30 -0500, you wrote:

>Hi:
>
>I’m trying to resurrect my Myth system (0.28-fixes with MariaDB 5.5 on OS X).  Some months ago, I noticed that all of my recordings were failing.  I then found that the mythbackend log had hundreds of pairs of entries like the following:
>
>2021-02-08 13:44:12.761647 E [55541/775] CoreContext programinfo.cpp:2616 (GetPlaybackURL) - ProgramInfo(1411_20190311025900.ts): GetPlaybackURL: '1411_20190311025900.ts' should be local, but it can not be found.
>2021-02-08 13:44:12.761688 E [55541/775] CoreContext mainserver.cpp:3061 (DoHandleDeleteRecording) - MainServer: ERROR when trying to delete file: GetPlaybackURL/UNABLE/TO/FIND/LOCAL/FILE/ON/MediumMini.local/1411_20190311025900.ts. File doesn't exist.  Database metadata will not be removed.
>
>In mythfrontend, Watch Recordings/All Programs says I have 1,132 recordings consuming 4,028.30 GB.  All my recordings are stored on an external drive and I have triple checked the path to the recordings directory in mythtv-setup.  There are 2,090 files—recordings and previews--in the relevant directory consuming 3.96 TB.  Note that I can successfully play various old recordings in mythfrontend.  I don’t have MythWeb running.  I think my recordings started failing when the number of bad entries got so large that it took too long trying to go through them.
>
>I tried to run find_orppans.py.  It found 673 recordings with missing files.  If I select "Delete orphaned recording entries”, I get the message:
>
>Warning: Failed to delete 'MediumMini.local: Republic of Doyle - The Driver'
>
>Then the list of orphaned recordings, etc, is re-displayed—nothing has apparently been fixed.

It takes time for mythbackend to delete things, so when you are
deleting a long list of missing file recordings, only one or two will
have actually been deleted by the time that find_orphans.py creates
its new list of missing file recordings.  So you need to re-run
find_orphans.py and get it to re-create the list, until you see that
the deletions have been completed.  When you delete recordings, you
are actually just telling mythbackend to add the delete requests to a
delete queue, which it processes in the background until it is empty.
The delete queue is saved over a shutdown of mythbackend and will be
restarted on the startup of mythbackend.

>Checking the MariaDB error log, quite a few tables are marked as crashed, e.g. settings, recorded, oldrecorded, etc.  I have a daily backup of the database (using mythconverg_backup.pl) and I run optimize_mythdb.pl on a daily basis.  Even though optimization complete successfully, it MariaDB complains the next time I start the database.

I have been in the situation you find yourself in and I did manage to
repair the database without having to restore from backup.  So I have
some suggestions.

If you are still getting crashed tables after optimize_mythdb.pl has
been run, then it has not successfully completed optimisations.  There
will likely be various log messages about this in the MariaDB logs.  A
common cause of failed table repairs is running out of space on the
partition containing the database - that is what happened to me.  The
process of repairing a table requires copying the table and all its
indexes to new files, deleting the old table and index files, and then
renaming the repaired files back to the original names.  Tables are
repaired one at a time.  The largest table is normally the
recordedseek table, so you need to go to the location of the database
tables and find out how much space recordedseek is using.  This is
what I get:

root at mypvr:/var/lib/mysql/mythconverg# du -cb recordedseek*
1038    recordedseek.frm
8741298214      recordedseek.MYD
7864638464      recordedseek.MYI
16605937716     total
root at mypvr:/var/lib/mysql/mythconverg# du -ch recordedseek*
4.0K    recordedseek.frm
8.2G    recordedseek.MYD
7.4G    recordedseek.MYI
16G     total
root at mypvr:/var/lib/mysql/mythconverg# df -h .
Filesystem      Size  Used Avail Use% Mounted on
/dev/nvme0n1p4  100G   74G   22G  78% /

So the total space used by my recordedseek table and its index is 16
Gbytes, and I have 22 Gbytes free, so the recordedseek table can be
repaired.

You should also look for old files left behind by failed repair
attempts.  These can need to be manually deleted.  Failed copies of
recordedseek can use up a lot of space.  If you find you have other
recordedseek.* files than the ones above, they are likely the result
of failed repairs and need deleting.  Once all the space has been used
up by failed copies of tables, all table repairs will then fail after
that, creating more problems.

I now have a script that is run hourly by systemd to check that there
is sufficient free space on my system partition.  It emails me if
there is a problem.

If the optimize_mythdb.pl script does not repair everything, you can
manually run mysqlanalyze/mysqlcheck/mysqlrepair which have more
options that can help.

Before attempting any drastic repairs, it is best to shut down MariaDB
and make a complete copy of the database files to another drive.  That
way if you make a mistake you can just shut down MariaDB and copy the
backup files back to their original location and then try again.

>Any suggestions?  I’m thinking to nuke the database and do a partial restore and then see if find_orphans.py will give me a true list of my recordings.  Or is there an easy database tool that might fix whatever is wrong in situ?
>
>If I can get the recordings back, I’m going to look into migrating to 31.
>
>Craig