[mythtv-users] Problem with Myth Database

Stephen Worthington stephen_agent at jsw.gen.nz
Thu Apr 8 03:54:22 UTC 2021


On Wed, 7 Apr 2021 16:10:55 -0400, you wrote:

>On 4/7/21, Craig Huff <huffcslists at gmail.com> wrote:
>> I finally checked into why I was having job queue errors on recordings for
>> commercial flagging operations.  I saw a lot of errors in mythbackend.log
>> about the recordedseek table, so I manually ran optimize_mythdb and it
>> reported:
>> DBD::mysql::db do failed: Table './mythconverg/recordedseek' is marked as
>> crashed and last (automatic?) repair failed at /usr/bin/optimize_mythdb
>> line 38.
>>
>> I still have multiple gigabytes in / (root), /var, and the multiple /video*
>> directories, FWIW.  I _do_ have a large backlog of videos to watch when
>> time permits, so could it be that the table has gotten too big / full for
>> mysql to handle?
>>
>> The .TMD file is 2.1G (2209087488),
>> the  .MYD file is 2.1G (2209887450), and
>> the  .MYI  file is 2.1G (2147819520).
>>
>> There's also a .BAK file from 2018 lingering around.  Not sure what that
>> means, either.
>>
>> What do I do now?
>>
>> --
>> Craig.
>>
>
>There's no way the size of the table is an issue. In my work I've
>dealt with MySQL MyISAM tables in the TB range with as many as 7
>billion rows.
>
>A few things to note about what you have there:
>
>The only files that are really used for a MySQL MyISAM table are the
>.frm, the .MYD, and the .MYI. The only time you'd normally have a .TMD
>file is in a case where MySQL was rebuilding the .MYD file...for
>example if you were in the middle or doing a "REPAIR TABLE". That may
>build a .TMD file and at the very end rename it to the .MYD table.
>
>What's the time on that .TMD table? Depending on what distribution you
>have, some will (like Debian I believe) may check for crashed tables
>on startup and may automatically try to repair something. If the time
>on that .TMD file is current, you may want to log into the mysql
>command line and enter "show processlist;" to see if there might be a
>repair running.
>
>Oh...and I'd say that .BAK file is just cruft for sure.
>
>Tom

Do not despair - recordedseek is fixable.  If necessary, mythbackend
can completely recreate it.

The first thing to try is to see if you can get the recordedseek table
repaired.  Shut down mythbackend.  Shut down MySQL/MariaDB.

Optional but highly recommended step - if you have enough room
somewhere on the system, make a copy of all the files in
/var/lib/mysql/mythconverg.  You can not run the database anywhere
except in the mythconverg directory, but if you make a mistake trying
to fix it, you can always copy back this backup copy and start the
repairs again.

Then delete the *.BAK and *.TMD files in the mythconverg directory.
From the /var/lib/mysql/mythconverg directory do these commands:

du -b recordedseek*
df -b .

You must have at least as much room left on that partition as the size
of the files for the recordedseek table, as repairing a table makes a
copy of all the existing files (except the .frm file, which is
information about the format of the table).  If you are out of space,
that will be the most likely cause of your problems.

Restart MySQL/MariaDB.

Then do:
sudo mysql
use mythconverg;
check table recordedseek;

It is possible (but unlikely) that the table will report OK at this
point, simply due to the removal of the recordedseek.TMD temporary
file.  If so, just exit and restart mythbackend.

If recordedseek is still crashed, do:

repair table recordedseek;

If that works, then everything is likely OK again.  Do this to check:

check table recordedseek;
analyze table recordedseek;

If not, then shut down MySQL/MariaDB again and delete any new
recordedseek temporary tables.  It should only have .frm, .MYD and
.MYI files.  The from the /var/lib/mysql/mythconverg directory, run
myisamchk.  I have never had to use myisamchk so I can not really
recommend the right options, but its man page suggests this:

myisamchk --silent --force --fast --update-state \
  --key_buffer_size=64M --sort_buffer_size=64M \
  --read_buffer_size=1M --write_buffer_size=1M \
  recordedseek.MYI

I think I would not put the --silent option so I could see what it was
doing, and I would keep a log file of the results:

myisamchk --silent --force --fast --update-state \
  --key_buffer_size=64M --sort_buffer_size=64M \
  --read_buffer_size=1M --write_buffer_size=1M \
  recordedseek.MYI 2>&1 | tee recordedseek.log

If that works, good.  Otherwise, the next thing to try is to restore
an old pre-crash copy of recordedseek if you have one.  I presume that
you have been backing up the database regularly.  I have daily and
weekly backups.  But it is possible that the table has been crashed
for a long time and it is bad in all the backups also.  That may or
may not be a problem, as restoring from a backup can make a table
usable so that it can then be repaired as above, because the process
actually involves deleting the table and recreating it, then reloading
the data from the backup.  But you may need to try restoring the
recordedseek table from all the backups to find a good copy.
Unfortunately restoring one table is not simple as there is no tool
that does it.  The process involves uncompressing the database backup
file, then editing the uncompressed .sql file so that only the
commands applying to that table are left - you delete all the other
lines in the backup file.  But to do that, you need to have an editor
program capable of editing massive (multi-gigabyte) text files with
quite long lines.  Most text editors can not do that.  I use my copy
of SlickEdit which is a professional programmer's editor, and even it
takes quite a while.  I think it is also possible to do the editing
using awk, but I have never done that.  If the worst comes to it, you
could give me a copy of your database backup and have me edit it for
you.

Once you have a .sql file with just the lines for recordedseek, then
you do this (with MySQL/MariaDB running but mythbackend shut down):

sudo mysql
use mythconverg;
source /path/to/the/backup/recordedseek.sql
quit

The edited backup file should have all the commands to delete the
existing recordedseek table, re-create it and then load all the data.

After that, you need to figure out which (recent) recordings no longer
have recordedseek table entries and get mythbackend to re-create them.

sudo mysql
use mythconverg;
select chanid,starttime,title,basename from recorded r where (select
count(*) from recordedseek s where r.chanid=s.chanid and
r.starttime=s.starttime)=0;

The above is a big query - it will take a long time to produce any
results.  It should produce a list of basenames you can then use with
the mythcommflag --rebuild command to rebuild the recordedseek entries
for those recordings.  Alternatively you can these days also try:

mythutil --checkrecordings

which is supposed to tell you which recordings do not have
recordedseek entries, and then it also has a --fixrecordings option. I
have never used this, so I am not quite sure how it works, but it was
added just for situations like this.

If you do not have a backup copy that works, then you will just have
to re-create the entire recordedseek table.  The --fixrecordings
option should do that, but it will take ages (days? weeks?) as it will
have to read all your recording files.  I think that it is possible to
do new recordings at the same time as that is happening, but I expect
that the disk load will be very high and there might be problems.  And
playback will likely be affected also.


More information about the mythtv-users mailing list