Experienced Programmer with some availability

[sarettah]

Thanx for the sticky G

[sarettah]

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.

";
//
// 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 .= "";
$outstr .= "";
$outstr .= "";
$outstr .= "";                
$outstr .= "";
$outstr .= "\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 .= "\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 .= "\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 .= "";
        //
        // 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 .= "\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 .= "\n";
            $outstr .= "";
            $last_status=$rprrow[3];
          }
          if(strtoupper($last_status)<>"OK")
          {
             // Repair failed.  Report it in our output string
             $outstr .= "";
          } 
          else
          {
             // The repair was successful
             // increment our repaired counter
             $repaired++;
             // write our success message to our output string
             $outstr .= "";              
          }
        }
      } 
    }
  }
}
//
// Ok all done now
// so lets close our table
$outstr .= "
Table Name
Operation
Message Type
Message
Database " . trim($dbname) . "
Problem checking table " . $tblrow[0] . "
" . $chkrow[0] . "
" . $chkrow[1] . "
" . $chkrow[2] . "
" . $chkrow[3] . "
Attempting to repair table " . $tblrow[0] . "
Problem repairing table " . $tblrow[0] . "
" . $rprrow[0] . "
" . $rprrow[1] . "
" . $rprrow[2] . "
" . $rprrow[3] . "
Repair Failed " . $tblrow[0] . "
Repair Successful " . $tblrow[0] . "
\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
?>