<span style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">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:</span><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
<br></div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><div>#!/usr/bin/perl -w</div><div>#</div><div># Connects to the mythtv database and repairs/optimizes the tables that it</div>
<div># finds. Suggested use is to cron it to run once per day.</div><div>#</div><div># Includes</div><div>use DBI;</div><div>use MythTV;</div><div># Connect to mythbackend</div><div>my $Myth = new MythTV({'connect' => 0});</div>
<div># Connect to the database</div><div>$dbh = $Myth->{'dbh'};</div><div># Repair and optimize each table</div><div> foreach $table ($dbh->tables) {</div><div> unless ($dbh->do("REPAIR TABLE $table")) {</div>
<div> print "Skipped: $table\n";</div><div> next;</div><div> };</div><div> if ($dbh->do("OPTIMIZE TABLE $table")) {</div><div> print "Repaired/Optimized: $table\n";</div>
<div> }</div><div> if ($dbh->do("ANALYZE TABLE $table")) {</div><div> print "Analyzed: $table\n";</div><div> }</div><div> }</div><div><br></div></div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
After running this script, MySQL reports all mythconverg tables are "crashed" and need to be repaired:</div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
<div>130101 12:29:11 [ERROR] /usr/libexec/mysqld: Table './mythconverg/archiveitems' is marked as crashed and last (automatic?) repair failed</div><div>[snip]</div><div>130101 12:29:32 [ERROR] /usr/libexec/mysqld: Table './mythconverg/websites' is marked as crashed and last (automatic?) repair failed</div>
<div><br></div><div>At the same time my cron job ran, I got the following in the mythbackend log:</div><div><div>2013-01-01 02:00:43.473444 E [7843/7866] JobQueue mythdbcon.cpp:837 (prepare) - Error preparing query: SELECT <a href="http://j.id">j.id</a>, 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, <a href="http://j.id">j.id</a>;</div>
<div>2013-01-01 02:00:43.473481 E [7843/7866] JobQueue mythdbcon.cpp:839 (prepare) - Driver error was [2/144]:</div><div>QMYSQL3: Unable to prepare statement</div><div>Database error was:</div><div>Table './mythconverg/jobqueue' is marked as crashed and last (automatic?) repair failed</div>
</div><div><br></div></div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">I had to run the following two commands (with mythbackend and mysqld stopped) in order to repair the tables:</div>
<div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><br></div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
cd /var/lib/mysql/mythconverg</div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><div>myisamchk --fast *.MYI</div><div>myisamchk --force --update-state --key_buffer_size=64M --sort_buffer_size=64M --read_buffer_size=1M --write_buffer_size=1M --fast *.MYI</div>
</div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><br></div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
If I run the <a href="http://optimize_mythdb.pl/" target="_blank" style="color:rgb(17,85,204)">optimize_mythdb.pl</a> 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.</div>
<div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)"><br></div><div style="color:rgb(34,34,34);font-family:arial,sans-serif;font-size:13px;background-color:rgb(255,255,255)">
/Brian/</div>