Whether you're running WordPress or any other software powered by MySQL, you are relying on MySQL tables always being available and problem free.

Unfortunately MySQL table corruption is a fact of life.

Network latency, server shutdown, hard drive running out of space and memory defragmentation are just some causes of MySQL table corruption.

A corrupted MySQL table could render your site useless, lead to loss of revenues and basically make your life a living hell.

MySQL offers two command-line tools to check for table corruption:

* mysqlcheck - uses the SQL statements CHECK TABLE, ANALYZE TABLE, and OPTIMIZE TABLE. To invoke mysqlcheck for checking all of your databases you run: mysqlcheck --all-databases

* myisamchk - runs on the raw MySQL files, bypassing the server, myisamchk is faster but unlike mysqlcheck cannot be executed while the server is running.

--

The worst part about MySQL table corruption is that you're often not going to know about it until your customers tell you or until it's already too late to restore yesterday's backup.

As part of this post I'd like to share how we automate MySQL table maintenance.

We will be running mysqlcheck in the background, twice a day, check for any problems and email the administrator in case any tables are corrupted.

Since our script will run on a live database we have to use 'mysqlcheck' and not 'myisamchk'.


Now all that's left is setting up the cronjob to run twice a day -

This code is very broken, but promising. Any chance you can post a working correct version of this script? Note the syntax errors that are in this version.

Thanks!

-- Kimball

As I was reading through this article I though how cool is this! it is just what I need to be able to keep my databases clean. I had just spent the last few days going through the 50 domains or so on a hosting server trying to make sure all the databases wouldnt spit errors. Every hour and every night we have scripts that run and if they come across a table that is corrupt the scripts bomb and the domains at the end of the list never get processed. All my trouble will go away said the rolling stones. Not so fast. I get through with the article and what do I see. A comment saying how the published script is broken. with a simple request for a repost of the broken code. Is there any other comments perhaps saying hey, thanks for catching that, I reposted a good script. No! argh. My troubles are coming back...

here is the corrected code, if it will fit in this little box.. The for i statements were missing closing parentheses, and there may have been a couple of missing semicolons. The path to the mysqlcheck was missing (note, if you have safe mode on you need to define the path in your php.ini of where commands can run from). And the timer isnt going to work with safe mode on unless you reconfigure the exec command. Oh, and the script is PHP. Never having programmed in PHP I didnt know what it was for a while. Anyway, enjoy

<?php
$username = "youruser";
$password = "yourpassword";
$host="localhost"; // your mysql server ip address
$adminemail = "[email protected]";

// No time limit
set_time_limit(0);

// Init
$error_str = "";

// Prepare command
$command = "/usr/bin/mysqlcheck -h$host -u$username -p$password -A";

// Run command
if (!exec($command, &$return_val)) $error_str = "Failed to execute command: $command";

// Iterate through return_val, line by line, looking for any errors
for ($i=0; $i;)
{
$line = $return_val[$i];

// Split output to tablename and result code
$line = str_replace(":", " ", $line)." ";
$pos = strpos($line, " ");
if ($pos<1) continue;
$tablename = trim(substr($line, 0, $pos));
$resultcode= trim(substr($line, $pos+1));
while (strlen($resultcode)>0 && $resultcode[0]==' ') $resultcode = substr($resultcode,1);

// If result code is not 'OK', add details to error_str
if (Strcasecmp($resultcode,'ok')!=0)
{
$error_str .= $line."\\r\\n";
$i++;
if ($i)
{
$line = $return_val[$i];
$error_str .= $line."\\r\\n";
}
}
}

// If we have any output, email administrator
if (!empty($error_str))
{
mail($adminemail, "MySQL Table Corruption", $error_str, "From: [email protected]");
}
?>

great job