Fixing MySQL Crashed Tables

Back

Got home tonight and mythtv machine was locked up solid. Rebooted it and noticed I had no program data. Tried deleting the data in the program guide and refilling it to no avail. Then I noticed this at the end of running mythfilldatabase:

2007-03-18 21:03:52.424 DB Error (DelLogEntry#1):
Query was:
SELECT logid FROM mythlog WHERE module= 'mythfilldatabase' ORDER BY logdate ASC;
Driver error was [2/145]:
QMYSQL3: Unable to execute query
Database error was:
Table './mythconverg/mythlog' is marked as crashed and should be repaired

Crashed table. Yuck!

Tried fixing that one with the command

mysqlcheck --auto-repair --check -u mythtv -pmysqlpass

To which I got back

mythconverg.mythlog
error    : Table './mythconverg/mythlog' is marked as crashed and should be repaired
Repairing tables
mythconverg.mythlog                                OK

Then I ran mythfilldatabase again and went to my mythtv webpage to look for program data.

It told me another table had crashed and needed to be repaired. So I figured other tables must have crashed as well so I just wanted to find a blanket command to check and fix all the tables with problems. I put this one together from the --help page and it did the trick. Everything is back up and working. The following command that did it was

mysqlcheck --auto-repair -A -u mythtv -pmysqlpass

It finds all corrupted tables and fixes them if possible. Output from command is below. Output from this command is below. The "..." is space where lot's of other tables were.

mythconverg.gameplayers                            OK
mythconverg.housekeeping                           OK
...
mythconverg.inuseprograms
error    : Table './mythconverg/inuseprograms' is marked as crashed and should
be repaired
mythconverg.oldfind                                OK
mythconverg.oldprogram                             OK
...
mythconverg.oldrecorded
error    : Table './mythconverg/oldrecorded' is marked as crashed and should be
repaired
mythconverg.recordedrating                         OK
...
mythconverg.recordedseek
error    : Table './mythconverg/recordedseek' is marked as crashed and should be
repaired
...
mythconverg.videosource                            OK
mythconverg.videotypes                             OK

Repairing tables
mythconverg.inuseprograms
warning  : Number of rows changed from 3 to 1
status   : OK
mythconverg.oldrecorded
warning  : Number of rows changed from 5454 to 5455
status   : OK
mythconverg.recordedseek
warning  : Number of rows changed from 471859 to 471860
status   : OK