[mythtv-users] Problem with Myth Database

Craig Huff huffcslists at gmail.com
Fri Apr 9 00:07:16 UTC 2021


On Wed, Apr 7, 2021 at 10:56 PM Stephen Worthington <
stephen_agent at 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 at 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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.mythtv.org/pipermail/mythtv-users/attachments/20210408/e5c28d1c/attachment.htm>


More information about the mythtv-users mailing list