[mythtv-users] 0.26 optimize_mythdb.pl Crashes Tables
Michael T. Dean
mtdean at thirdcontact.com
Thu Apr 4 14:04:00 UTC 2013
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
More information about the mythtv-users
mailing list