Ok, so I am bored.
I recently had a situation where MYSql tables were crashing on me and because of that all the applications that were accessing the affected tables would grind to a big messy halt.
So, I invented a little automated table checking and repair utility that I could launch from the crontab on a regular interval to try to keep the database up and running.
My original app checks multiple databases and has a lot of process checking to make sure that no collisions occur.
I have modified it down to just check all the tables in one database.
And now, I give it to you.
Code:
";
//
// This is the address to send the report and errors to
// *** change the line below to indicate the addresses you wish to send reports to
$toaddress="email@address2reportto.com;secondemail@address2reportto.com";
//
// This is the process file. we put this out there at the beginning of the run
// and remove it at the end of the run. If this file exists then
// a maintenance run is already occurring
// **** Change the path below to the proper path on your server
// usually something like /user/web/domain/running
// If you only use it from the browser then this could be
// just running/maint.txt if you create the running folder as a
// subfolder of the folder the app is running from
$processfl="/user/web/domain/running/maint.txt";
//
// database information
//
// *** change the information below to reflect your database settings
//
// database host
$dbhost = "localhost";
//
// database user
$dbuser = "dbusername";
//
// database password
$dbpass = "dbpassword";
//
// database name
$dbname = "dbname";
//
// *** end of data that needs to be modified
//
// End of housekeeping
//
//
// Mainline
//
// check to see if process file already exists.
// if it already exists then email an error report and blow it off
if(file_exists($processfl))
{
$errorstr .="Maintenance tried to start while another instance was running\n\n";
mail_it($errorstr,0,0,1, $fromaddress, $toaddress);
die("Process already running\n\n");
}
//
// ok, another instance is not running so it is ok to run
echo "creating process file\n";
//
// create the process file
// first you open it
$fp=fopen($processfl,'a');
if($fp)
{
// then you close it
fclose($fp);
}
// Check to see if file created ok. I know I know I could just
// look at $fp. But there is always the possibillity that the
// file handler came back ok but the file did not get created
// this covers that.
// if the file did not get created then email an error report and blow it off
if(!file_exists($processfl))
{
$errorstr .="Maintenance could not create the process file\n\n";
mail_it($errorstr,0,0,1, $fromaddress, $toaddress);
die("Could Not Create Process File\n\n");
}
else
{
echo "created the process file\n";
}
// lets hook up to MYSql
// If we dont get a good connection we will
// mail an error report and blow it off
$db = mysql_connect($dbhost, $dbuser, $dbpass);
if(!$db)
{
$errorstr .="Could not connect to MYSQL\n\n";
$errorstr .="MySql error was " . mysql_error() . "\n\n";
mail_it($errorstr,0,0,1, $fromaddress, $toaddress);
die("Could not connect to mysql\n\n");
}
//
// Now we select the database that we are going to check
$selected=mysql_select_db($dbname);
//
// ok, we are good to go
//
// Turn off the server timeout in case of a long winded check or repair operation
set_time_limit(0);
//
// Yeah I know, I am combining appearance and logic here. Bite me.
// This is old school baby, think CGI ;p
// Lets format a table to make our output all need and pretty
// we want a four (4) column table because the commands we will
// use return multiple lines of 4 column output.
$outstr .= "";
$outstr .= "";
$outstr .= "| Table Name | ";
$outstr .= "Operation | ";
$outstr .= "Message Type | ";
$outstr .= "Message | ";
$outstr .= "
";
$outstr .= "| Database " . trim($dbname) . " |
\n";
//
// We first do a show tables query to get a list of the tables in the database
$sql_str="SHOW TABLES";
$tblresult=mysql_query($sql_str, $db);
if($tblresult)
{
// ok the query was good, it didnt error
// now we want to loop through all the tables
while($tblrow=mysql_fetch_array($tblresult))
{
// for each table we do a check tables query
$sql_str="Check Table " . $tblrow[0];
$chkresult=mysql_query($sql_str, $db);
if(!$chkresult)
{
// error in the query, check your syntax ;p
$outstr .= "| Problem checking table " . $tblrow[0] . " |
\n";
}
else
{
// the check table query completed and returned output
// we need to loop through the output.
// Check table will return multiple lines
// if there is an error in the table
//
// clear out our last status holder
$last_status="";
//
// loop through the results
while($chkrow=mysql_fetch_array($chkresult))
{
// format the ouput nice and pretty
// put the result in the 4 columns of our table
// column 0 = Table Name
// column 1 = Operation (either Check or Repair)
// column 3 = Message Type (status or error)
// column 4 = Status - we want this to always say OK
$outstr .= "";
$outstr .= "| " . $chkrow[0] . " | " . $chkrow[1] . " | " . $chkrow[2] . " | " . $chkrow[3] . " | \n";
$outstr .= "
";
//
// we want to capture the very last status returned so we just keep
// overlaying our last status holder
$last_status=$chkrow[3];
}
// Once we are done looping through the check table results the
// last status returned is in our last status holder. we want this to be OK
if(strtoupper($last_status)<>"OK")
{
// OOps, status was not OK so we need to try a repair
// first we increment our counter to keep track of things
$needrepair++;
// Then lets throw a line in our output to indicate we are repairing something
$outstr .= "| Attempting to repair table " . $tblrow[0] . " |
";
//
// Now we run a Repair table query against the table
$sql_str="Repair Table " . $tblrow[0];
$rprresult=mysql_query($sql_str, $db);
if(!$rprresult)
{
// error in the sql. Check your syntax.
$outstr .= "| Problem repairing table " . $tblrow[0] . " |
\n";
}
else
{
// Ok the repair table completed so we have to loop through the result
// set just like we did on the check table
// in fact this could be modularized to do both operations
// from one function. But I'm lazy right now and this is already written
// clear out our last status holder
$last_status="";
while($rprrow=mysql_fetch_array($rprresult))
{
// format the ouput nice and pretty
// put the result in the 4 columns of our table
// column 0 = Table Name
// column 1 = Operation (either Check or Repair)
// column 3 = Message Type (status or error)
// column 4 = Status - we want this to always say OK
$outstr .= "";
$outstr .= "| " . $rprrow[0] . " | " . $rprrow[1] . " | " . $rprrow[2] . " | " . $rprrow[3] . " | \n";
$outstr .= "
";
$last_status=$rprrow[3];
}
if(strtoupper($last_status)<>"OK")
{
// Repair failed. Report it in our output string
$outstr .= "| Repair Failed " . $tblrow[0] . " |
";
}
else
{
// The repair was successful
// increment our repaired counter
$repaired++;
// write our success message to our output string
$outstr .= "| Repair Successful " . $tblrow[0] . " |
";
}
}
}
}
}
}
//
// Ok all done now
// so lets close our table
$outstr .= "
\n";
//
// lets close the database connection
if($db)
{
mysql_close($db);
}
//
// delete the process file to indicate the run is complete
unlink($processfl);
//
// finish off our report with an ending time
$outstr.="End of run " . date("M d Y H:i:s", time()) . "\n";
//
// now lets mail the report
mail_it($outstr, $needrepair, $repaired, 0, $fromaddress, $toaddress);
//
// then put it up on the screen or send to a dump file if running from cron
echo $outstr;
//
// End of mainline
//
// Start of user defined funstions
//
// UDF mail_it mails either our pretty report or an error message
// to whomevers address is in $toaddress
// pass ins are the email message itself - either our report or an error
// The needed repair counter
// The repaired counter
// and a switch telling us whether this is an error report or not $err=0 is a report, 1 is an eror
// from address
// to address
function mail_it($emailmessage, $needed_repair, $were_repaired, $err, $from, $to)
{
// initialize our email subject line
$emailsubj="";
if($err==1)
{
// if we have an error lets indicate it in the subject line
$emailsubj="Maintenance ERROR " . date("M d Y H:i:s", time());
//
// we tack the current time to the email message here so we know when the error occurred
$emailmessage .=date("M d Y H:i:s", time()) . "\n\n";
}
else
{
// There is no error so this is a report lets give a fitting subject line
$emailsubj="Database Maintenance Run";
//
// Now, i want to indicate on the subject line if the report needs to be
// looked at or not. I am going to dfo this by comparing my counters
if(intval($needed_repair)-intval($were_repaired)>0)
{
// If the number of tables needing repair exceeds the tables repaired
// then some were not repaired. Bummer. So lets say that on the
// subject line
$emailsubj .=" - Some Tables Could Not Be Repaired - Please Review";
}
else
{
if(intval($needed_repair)>0)
{
// If we are here then there were some that needed repair
// but all the repairs worked. Life is good.
// Lets say so on the subject line
$emailsubj .=" - Repairs Occurred Successfully";
}
else
{
// if we are here then no repairs were needed
// Life is great. Again lets indicate it on the subject line
$emailsubj .=" - All Is Ok";
}
}
}
//
// set up theheaders for an html mail
$headers = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=iso-8859-1\r\n";
$headers .="From: " . $from . "\r\n";
//
// and let's mail the sucker
mail ( $to, $emailsubj, $emailmessage, $headers);
}
// End of program
?>