Mailing List Archive

Problem with Myth Database
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.
Re: Problem with Myth Database [ In reply to ]
On 4/7/21, Craig Huff <huffcslists@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
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: Problem with Myth Database [ In reply to ]
On Wed, 7 Apr 2021 16:10:55 -0400, you wrote:

>On 4/7/21, Craig Huff <huffcslists@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.
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org
Re: Problem with Myth Database [ In reply to ]
On Wed, Apr 7, 2021 at 10:56 PM Stephen Worthington <
stephen_agent@jsw.gen.nz> wrote:

>
> 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.
> _______________________________________________
> mythtv-users mailing list
> mythtv-users@mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-users
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org
>

Stephen,

Thank you so much! mysql is definitely not my forte.

I only needed to go as far as "repair table recordedseek;" as confirmed by
the re-run of the check command and the analyze command reported the table
was up to date.

Depending on how you look at it, (I'll take the optimistic view here)
there's only 86 recordings for which I need to create recordedseek entries.

I'll save that list of recordings to a file and nibble away at it for the
next few days (weeks?) until they're all addressed.

BTW, I'm saving the list by selecting it in one xterm window and pasting it
into a new file vi session in another. Is there a way to
direct/redirect/tee the mysql output to a file instead? That would be
useful for future reference.

In any case, I'm off to get the list saved, exit out of mysql, restart
mythtv-backend, and start an invocation or two of mythcommflag.

Thanks once again. I really appreciate the help.

--
Craig.
Re: Problem with Myth Database [ In reply to ]
On Thu, 8 Apr 2021 19:07:16 -0500, you wrote:


>Stephen,
>
>Thank you so much! mysql is definitely not my forte.
>
>I only needed to go as far as "repair table recordedseek;" as confirmed by
>the re-run of the check command and the analyze command reported the table
>was up to date.

So it was likely that the existence of the recordedseek.TMD file was
what was causing the regular check and repair to fail. That file gets
left behind when a check and repair fails for some reason, so if you
are having problems, you should always look for *.TMD files, and
delete them (with MySQL/MariaDB shut down first). The most common
reason for having .TMD files left behind is that the partition they
are on is out of space - the recordedseek table keeps growing and is
larger than the rest of the database put together, so it pays to keep
an eye on its size and make sure your database partition always has
rather more space than that. I have a systemd space check that runs
regularly and will email me if the space gets too small.

>Depending on how you look at it, (I'll take the optimistic view here)
>there's only 86 recordings for which I need to create recordedseek entries.
>
>I'll save that list of recordings to a file and nibble away at it for the
>next few days (weeks?) until they're all addressed.
>
>BTW, I'm saving the list by selecting it in one xterm window and pasting it
>into a new file vi session in another. Is there a way to
>direct/redirect/tee the mysql output to a file instead? That would be
>useful for future reference.
>
>In any case, I'm off to get the list saved, exit out of mysql, restart
>mythtv-backend, and start an invocation or two of mythcommflag.
>
>Thanks once again. I really appreciate the help.

There are several methods, but the easiest way to get the output sent
to a file do this before the select command:

tee name_of_output_file.log

and this after:

notee
_______________________________________________
mythtv-users mailing list
mythtv-users@mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-users
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org