[mythtv-users] 0.26 optimize_mythdb.pl Crashes Tables

[Brian Long]

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.

/Brian/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20130101/641a4f52/attachment.html>