[mythtv-users] Help needed with error in recordedseek table not fixed by optimize_mythdb

Stephen Worthington stephen_agent at jsw.gen.nz
Mon Jan 29 04:13:38 UTC 2018


On Sun, 28 Jan 2018 19:59:40 -0600, you wrote:

>On Sun, Jan 28, 2018 at 6:12 PM, Craig Huff <huffcslists at gmail.com> wrote:
>
>> On Sun, Jan 28, 2018 at 5:04 PM, Paul Harrison <mythtv at sky.com> wrote:
>>
>>> On 28/01/18 22:17, Craig Huff wrote:
>>>
>>> On Sun, Jan 28, 2018 at 4:09 PM, Craig Huff <huffcslists at gmail.com>
>>> wrote:
>>>
>>>> On Sun, Jan 28, 2018 at 4:00 PM, Paul Harrison <mythtv at sky.com> wrote:
>>>>
>>>>>
>>>>> If you can't repair the recordedseek table one option is to truncate it
>>>>> and restore just that table from your old backup. That way only the
>>>>> seektables for new recordings will be missing which you can fix by running
>>>>> 'mythutil --checkrecordings  --fixseektable'
>>>>>
>>>>> Paul H.
>>>>>
>>>>>
>>>> Paul-
>>>>
>>>> Re-creating the seektable data for the new recordings it what I was
>>>> hoping to do, but the trick I haven't figured out yet is how to restore
>>>> just the recordedseek table.
>>>>
>>>> Can you give me more details on that?
>>>>
>>>>    - How do I truncate it (presumably the recordedseek table)?
>>>>    - How would you suggest I restore the recordedseek table from the
>>>>    backup?
>>>>
>>>> --
>>>> Craig.
>>>>
>>>
>>> Looks like truncation would involve launching an interactive mysql
>>> session and invoking the command "TRUNCATE TABLE recordedseek;".  Correct?
>>>
>>> --
>>> Craig.
>>>
>>>
>>> Yes that will remove all the data from the recordedseek table. I would
>>> also drop the table as well since the backup will recreate it.
>>>
>>> Your backup will contain all the data for all tables so you will have to
>>> create a new one with just the recordedseek data in it. That's the tricky
>>> bit because if you have a lot of recordings the backups are very large. You
>>> can if they are small enough edit it in a text editor but most are to large
>>> to do that.
>>>
>>> Easiest way is probably to run a sed command to extract it something like
>>> this works for me
>>>
>>> sed -n -e '/CREATE TABLE.*`recordedseek`/,/UNLOCK TABLES;/p' mythconverg-1348-20171215001052.sql > recordedseek.sql
>>>
>>>
>>> Paul H.
>>>
>>> Paul,
>>
>> I thought to use mythconverg_restore.pl to restore the recordedseek table
>> after replacing the contents of my equivalent of  mythconverg-1348-20171215001052.sql
>> with the recordedseek.sql contents (after making a backup copy of
>> mythconverg-1348-20171215001052.sql elsewhere).
>>
>> That didn't work because the rest of the database is still there and the
>> restore program didn't like that -- not really a surprise.  So, I guess I
>> need to feed the recordedseek.sql file into a mysql command line as a
>> command file.  I can figure that out.  I think I've even done it before.
>>
>> Many thanks to all for your input.  If this works, I won't clutter the
>> maillist with more traffic.  If you hear from me, it'll be because I'm
>> stuck again.
>>
>> --
>> Craig.
>>
>
>Just to close the loop so to speak, "mysql ..... < recordedseek.sql" didn't
>work either.  I think the problem was that several records got lost in the
>recordedseek table that were "known" to other table(s) and so they would
>always result in detected corruption.  I finally had to restore the
>previous day's backup as resign myself to having a couple of recordings
>orphaned.  No big deal.  They'll come around again and get recorded when
>they do.

Do you still have any temporary files hanging around in the
/var/lib/mysql/mythconverg directory?  If so, that can cause problems.
If you have a crash where temporary tables are left behind, it is
normally a good idea to delete them - leaving them around can cause
problems with subsequent repairs.  Especially with recordedseek, as it
is huge and the temporary files created while optimising or repairing
it can make you run out of space on the system partition if they get
left behind.  Which will then cause all subsequent attempts at repair
to fail.  This may be what happened to you.  When you restored the old
backup database, it is likely any temporary files lying around may
have been cleaned up as part of re-initialising the entire database.

What I do with a crash like this is to shut down MySQL (actually
MariaDB these days), then copy all the files in
/var/lib/mysql/mythconverg to another location where I have plenty of
space (on a recording drive) before I try fixing it.  If I have
temporary files like your #sql-5de_e6a.MYI file, I delete them before
trying to run optimize_mythdb.  From the name of that file, it will be
a temporary copy of an index file (.MYI), and my understanding of how
MySQL works is that when it is doing a repair or optimise operation,
it will copy the data out of the original table or index file, repair
it and write the repaired data to a temporary file.  When the repair
is successfully completed, it will delete the original table or index
file and rename the temporary one to the original name.  If the repair
fails, or is stopped in the middle, the old untouched table files are
always then still available.  So it is normally safe (and a good idea)
to delete any left over temporary files (but only while MySQL is shut
down).  Also, index files can be re-created from the table files - so
if there is only a corrupt index file, you can (with the right
command) make MySQL re-create the index files from the original table
data.  I have never had to do that though - either optimize_mythtb
(with sufficient spare space to run properly) has fixed things, or
manually running mysqlcheck.

And always keep an eye on how large recordedseek is getting and how
much free space you have on the same partition as the database.  You
have to have more than the combined size of all the recordedseek.*
files available as spare space at all times.  As recordedseek grows,
the size of your backups also grows and if they are on the same
partition as the database (which is the default location), you will
eventually run out of space as a result of that.  Especially if you
have upgraded to an SSD as the system partitions on SSDs can be
smaller than you have been used to using on rotating rust.


More information about the mythtv-users mailing list