[mythtv-users] Recordseek table problem

[Michael T. Dean]

On 03/23/2016 07:11 AM, Phill Edwards wrote:
> I have noticed recently that the seek forwards and backwards is 
> misbehaving on my Mythbuntu 0.27 installation. Symptoms are:
>
> - When seeking forward the OSD seems to struggle/flicker while 
> seeking, but it used to just be instantaneous.
> - Occasionally, when seeking forward it goes all the way to the end of 
> the program instead of the 15 seconds it's supposed to go.
>
> I run optimize_mythdb as a daily cron job. I just ran it now and saw 
> no errors in the output and this is what it showed for the 
> recordedseek table:
>
> Repaired/Optimized: `mythconverg`.`recordedseek`
> Analyzed: `mythconverg`.`recordedseek`
>
> BUT... when I go to MySQL and try to do something with the table such 
> as count the rows I get this error:
>
> mysql> select count(*) from recordedseek;
> ERROR 144 (HY000): Table './mythconverg/recordedseek' is marked as 
> crashed and last (automatic?) repair failed
>
> That seems totally at odds with the output of optimize_mythdb, but 
> clearly there's a problem so I have two questions:
>
> 1) How do I fix it?

This is the challenging question.  You'll probably need to run 
mysqlrepair/mysqlcheck --repair manually and specify (probably even 
scary) options, and you may actually lose some (or all) of the data in 
the recordedseek table.  On the bright side, though, all the data in 
that table is just pre-generated data pulled from your recording files, 
so it can be re-created by running mythcommflag or mythtranscode (see 
https://www.mythtv.org/wiki/Repairing_the_Seektable ).

https://dev.mysql.com/doc/refman/5.5/en/mysqlcheck.html

Note that it may be easier (rather than digging through to get the right 
mysqlcheck options) to run:

myisamchk --safe-recover mythconverg.recordedseek

(assuming your table is using the MyISAM storage engine, as it should 
be).  See https://dev.mysql.com/doc/refman/5.5/en/myisamchk.html

(If your table is using the InnoDB storage engine, repairing is more 
challenging and that's probably why the REPAIR TABLE isn't working.)

Generally get the table working, then make sure you have some rows 
(should be a lot of rows--around 2 per second of recordings for most 
users).  If you have a bunch of rows, I'd recommend not trying to 
rebuild any seek tables.  If you eventually find a recording where 
seeking doesn't work properly, you can then repair its seek table or 
just suffer through the one recording before you delete it.

> 2) Why does optimize_mythdb say all is OK when it isn't?

The script simply said it ran the MySQL command, "REPAIR TABLE 
recordedseek;" and that MySQL didn't report any errors when it did. The 
fact that the REPAIR TABLE was unsuccessful--because the damage is too 
severe for an automatic repair (meaning there may be loss of data when 
it is completed) isn't reported as an error.  Instead, REPAIR TABLE 
returns a result set that includes Msg_type and Msg_text, which 
optimize_mythdb.pl ignores.

If you (or anyone else) would like to improve optimize_mythdb.pl, feel 
free to create a patch that outputs "Msg_type: Msg_text" indented after 
heading "Repaired: `mythconverg`.`recordedseek`" and modify the script 
to decide based on the Msg_type and Msg_text (if it's not status/OK) 
whether to run the OPTIMIZE and ANALYZE commands.

https://dev.mysql.com/doc/refman/5.5/en/repair-table.html

Note, too, that the OPTIMIZE and ANALYZE commands give the same result 
set as REPAIR TABLE, so you could output the Msg_type and Msg_text for 
those, also.

Mike