[mythtv-users] 0.26 optimize_mythdb.pl Crashes Tables

[Michael T. Dean]

On 01/01/2013 12:49 PM, Brian Long wrote:
> Over the weekend I upgraded to 0.26-fixes (RPMforge src.rpm + latest 
> git fixes).  Yesterday, I put back into place my cronjob which 
> optimizes the mythconverg tables (runs as mythtv user).  The script is 
> included in MythTV's contrib directory.  It uses the Perl API as follows:
>
> #!/usr/bin/perl -w
> #
> # Connects to the mythtv database and repairs/optimizes the tables that it
> # finds.  Suggested use is to cron it to run once per day.
> #
> # Includes
> use DBI;
> use MythTV;
> # Connect to mythbackend
> my $Myth = new MythTV({'connect' => 0});
> # Connect to the database
> $dbh = $Myth->{'dbh'};
> # Repair and optimize each table
>     foreach $table ($dbh->tables) {
>         unless ($dbh->do("REPAIR TABLE $table")) {
>             print "Skipped:  $table\n";
>             next;
>         };
>         if ($dbh->do("OPTIMIZE TABLE $table")) {
>             print "Repaired/Optimized: $table\n";
>         }
>         if ($dbh->do("ANALYZE TABLE $table")) {
>             print "Analyzed: $table\n";
>         }
>     }
>
> After running this script, MySQL reports all mythconverg tables are 
> "crashed" and need to be repaired:
> 130101 12:29:11 [ERROR] /usr/libexec/mysqld: Table 
> './mythconverg/archiveitems' is marked as crashed and last 
> (automatic?) repair failed
> [snip]
> 130101 12:29:32 [ERROR] /usr/libexec/mysqld: Table 
> './mythconverg/websites' is marked as crashed and last (automatic?) 
> repair failed
>
> At the same time my cron job ran, I got the following in the 
> mythbackend log:
> 2013-01-01 02:00:43.473444 E [7843/7866] JobQueue mythdbcon.cpp:837 
> (prepare) - Error preparing query: SELECT j.id, j.chanid, j.starttime, 
> j.inserttime, j.type, j.cmds, j.flags, j.status, j.statustime, 
> j.hostname, j.args, j.comment, r.endtime, j.schedruntime FROM jobqueue 
> j LEFT JOIN recorded r   ON j.chanid = r.chanid AND j.starttime = 
> r.starttime ORDER BY j.schedruntime, j.id;
> 2013-01-01 02:00:43.473481 E [7843/7866] JobQueue mythdbcon.cpp:839 
> (prepare) - Driver error was [2/144]:
> QMYSQL3: Unable to prepare statement
> Database error was:
> Table './mythconverg/jobqueue' is marked as crashed and last 
> (automatic?) repair failed
>
> I had to run the following two commands (with mythbackend and mysqld 
> stopped) in order to repair the tables:
>
> cd /var/lib/mysql/mythconverg
> myisamchk --fast *.MYI
> myisamchk --force --update-state --key_buffer_size=64M 
> --sort_buffer_size=64M --read_buffer_size=1M --write_buffer_size=1M 
> --fast *.MYI
>
> If I run the optimize_mythdb.pl script again, the tables are listed as 
> crashed again.  I've since disabled this cron job and would like to 
> understand if this script is known to be broken.

No, there's no issue with the script--it just calls some MySQL functions 
against the tables in your database.  If it's causing your database 
tables to be marked as crashed, you have some serious underlying MySQL 
and/or data storage issue.

Mike