Location: PHPKode > scripts > Database servers down alarm > database-servers-down-alarm/as_dbserver_check.php
<?php
/**
* @package as_dbserver_check.php - checking DBMS server(s) live state
* @version 1.00.001
* first creation date: 17.06.2009 (dd.mm.yyyy)
* modified 18.06.2009
* @Author Alexander Selifonov,  <hide@address.com>
* @link http://www.selifan.ru
**/
if(!defined('DBTYPE_MYSQL')) define('DBTYPE_MYSQL',1);
if(!defined('DBTYPE_MSQL')) define('DBTYPE_MSQL',2);
if(!defined('DBTYPE_POSTGRESQL')) define('DBTYPE_POSTGRESQL',3);
if(!defined('DBTYPE_ORACLE')) define('DBTYPE_ORACLE',4);
if(!defined('DBTYPE_MSSQL')) define('DBTYPE_MSSQL',5);
if(!defined('DBTYPE_DB2')) define('DBTYPE_DB2',6);

class CDbChecker {
  var $title = '';
  var $servers = array();
  var $ajaxflags = array();
  var $homefolder = './'; # here all 'flag' files for last fb state will be auto-created.
  var $adminemail = ''; # primary Admin email(s), all alarms will be sent to this addr.
  var $ispemail = ''; # email of your ISP support, to notify them about DB problems
  var $isptext = ''; # if message is sent to ISP, Here must be Your text containing client name and/or ID
  var $emailcharset = '';
  var $sms_engine = ''; # sending SMS engine can be attached (external function name !)
  var $b_errors = false;
  function CDbChecker($title='', $homefolder='', $isptext='') {
    $this->title=$title;
    if($homefolder!='') $this->homefolder = $homefolder;
    $this->isptext = $isptext;
  }
  /**
  * adds server description to check state
  *
  * @param integer $dbtype (see DBTYPE_xxx constants)
  * @param string $address hostname, connection string (depends on db type)
  * @param string $login
  * @param string $passwd
  * @param string $connfunc explicit function that tries to connect to DB server (used if dbtype undefined)
  * @param string $downtime "hh:mm-hh:mm" - known time interval for database maintenance, when it CAN be down
  */
  function AddServer($dbtype,$address,$login='',$passwd='',$connfunc=false,$downtime='') {
    $this->servers[] = array('dbtype'=>$dbtype, 'address'=>$address,'login'=>$login,'password'=>$passwd, 'connfunc'=>$connfunc,'downtime'=>$downtime);
  }
  function SetEmails($adminemail,$ispemail='', $emailcset='') {
    $this->adminemail=$adminemail;
    $this->ispemail=$ispemail;
    if(!empty($emailcset)) $this->emailcharset=$emailcset;
  }
  function SetSMSEngine($smsfunc='') { $this->sms_engine = $smsfunc; }
  /**
  * main method, it checks all DB servers for 'alive' state and sends alarm merssage if anyone is down.
  *
  */
  function CheckDbState() { #<2>
    $alarmtext = '';
    $oktext = '';
    $dbtypestr = 'undefined type';
    foreach($this->servers as $no=>$srv) { #<3-foreach>
      $address = $srv['address'];
      $downtime = $srv['downtime'];
      if(!empty($downtime)) {
        $allow_arr = is_array($downtime)? $downtime: split('-',$downtime);
        $now = date('H:i');
        if(count($allow_arr)>1 && $now>=$allow_arr[0] && $now<=$allow_arr[1])
          echo "Database $server can be in maintenance mode ({$allow_arr[0]}-$allow_arr[1]), checking skipped...<br />\n";
          continue; # registered down time. Don't check
      }
      $login = $srv['login'];
      $lnk = false;
      $dberr = '';
      switch($srv['dbtype']) {

        case DBTYPE_MYSQL:
          $dbtypestr='MySQL';
          $lnk = @mysql_connect($address, $srv['login'],$srv['password']);
          if(is_resource($lnk)) mysql_close($lnk);
          else $dberr = @mysql_error();
          break;

        case DBTYPE_MSQL:
          $dbtypestr='MSQL';
          $lnk = @msql_connect($address, $srv['login'],$srv['password']);
          if(is_resource($lnk)) msql_close($lnk);
          else $dberr = @msql_error();
          break;

        case DBTYPE_MSSQL:
          $dbtypestr='MS SQL';
          if(function_exists('mssql_connect')) {
            $lnk=mssql_connect($address, $srv['login'],$srv['password']);
            if(is_resource($lnk)) mssql_close($lnk);
          }
          elseif(!empty($srv['connfunc']) && function_exists($srv['connfunc'])) {
            $lnk = @call_user_func($srv['connfunc'], $address,$srv['login'],$srv['password']);
          }
          else continue; # MSSQL extension not loaded, just skip this check
          break;

        case DBTYPE_DB2:
          $dbtypestr='DB2';
          $lnk = @db2_connect($address, $srv['login'],$srv['password']);
          if(is_resource($lnk)) db2_close($lnk);
          break;

        case DBTYPE_ORACLE:
          $dbtypestr='Oracle';
          if(function_exists('oci_connect')) {
            $lnk= @oci_connect ( $srv['login'],$srv['password'],$address);
            if(is_resource($lnk)) oci_close($lnk);
          }
          elseif(function_exists('ocilogon')) {
            $lnk= @ocilogon( $srv['login'],$srv['password'],$address);
            if(is_resource($lnk)) ocilogoff($lnk);
          }
          elseif(!empty($srv['connfunc']) && function_exists($srv['connfunc'])) {
            $lnk = @call_user_func($srv['connfunc'], $address,$srv['login'],$srv['password']);
          }
          else continue; # MSSQL extension not loaded, just skip this check
          break;

        case DBTYPE_POSTGRESQL:
          $dbtypestr='PostgreSQL';
          if(strpos($address,'=')) $addrstrk = $address; # user passed full connect string: host=XXX user=xxx ..."
          else {
            $addrstk = '';
            if($address) $addrstrk.=" host=$address";
            if(!empty($srv['login'])) $addrstrk.= ' user='.$srv['login'];
            if(!empty($srv['password'])) $addrstrk.= ' password='.$srv['password'];
          }
          $lnk = @pg_connect($addrstk);
          if(is_resource($lnk)) pg_close($lnk);
           break;

        default: # other DB types must have user defined function for making connect
          if(!empty($srv['connfunc']) && function_exists($srv['connfunc'])) {
            $lnk = @call_user_func($srv['connfunc'], $address,$srv['login'],$srv['password']);
          }
          break;
      }
      $flagfile = $this->homefolder."dbserver-down-$no.log";
      $laststate = !file_exists($flagfile); # true if last state was 'alive', and false if it was dead
      if($laststate) {
        if(empty($lnk)) { # DB server state changed from "alive" to "dead", alarm it !
          $this->b_errors=true;
          $fout = fopen($flagfile,'w');
          if($fout) {
            fwrite($fout, "Server $address down, ".date('Y-m-d H:i'));
            fclose($fout);
          }
          $alarmtext .= "Database Server $address may go DOWN, detected time: ".date('Y-m-d H:i').",\n  reason: ($dberr).\n";
        }
      }
      else { # previous state - "DEAD",
        if($lnk) { # DB server state changed from "dead" to "alive"
          @unlink($flagfile);
          $oktext .= "Database Server $address become alive, detected time: ".date('Y-m-d H:i')."\n";
        }
        else $this->b_errors=true;
      }
    } #<3-foreach>

    if(!empty($alarmtext)) { #<3>
      $headers = "Priority:High\r\nX-Mailer: as_dbserver_check";
      if(!empty($this->emailcharset)) $headers.="\r\nContent-Type:text/plain; charset=\"{$this->emailcharset}\"";
      $res1 = $res2 = '???';
      if($this->adminemail) $res1=@mail($this->adminemail,'DB server(s) down !',($this->title."\n".$alarmtext),$headers);
      if($this->ispemail) $res2=@mail($this->ispemail,'DB server(s) down !',($this->isptext."\n".$alarmtext),$headers);
      echo "email sending result: {$this->adminemail}: ($res1) {$this->ispemail}:($res2)"; # debug needs

      # call SMS sending function :
      if(!empty($this->sms_engine) && function_exists($this->sms_engine)) call_user_func($this->sms_engine,$alarmtext);
    } #<3>

    if(!empty($oktext) ) { #<3>
      if(!empty($this->adminemail)) $res = @mail($this->adminemail,'DB server up',$oktext,$headers);

      # call SMS sending function :
      if(!empty($this->sms_engine) && function_exists($this->sms_engine)) call_user_func($this->sms_engine,$oktext);
    } #<3>

    echo nl2br($alarmtest); echo nl2br($oktext);

    if(empty($alarmtext) && empty($oktext))
      echo "No changes in servers state, ".($this->b_errors?'Some servers are DOWN':'Everything is OK')."<br />\n";

    return $this->b_errors;
  } #<2>
} # CDbChecker class end
?>
Return current item: Database servers down alarm