test_message.php

<?php
$valid_request = FALSE;
// check the legacy use first
if (isset($_GET['checkdb']) || isset($_GET['checkreplication'])) {
    $valid_request = TRUE;
}

// check new use
if (isset($_GET['interrogate']) && $_GET['interrogate'] == 1) {
    $valid_request = TRUE;
}

$LOCK_FILE = dirname(__FILE__) . '/.test_message.lock';
// not a valid request? exit!
if (!$valid_request) {
    header('HTTP/1.0 200 OK');
    echo 'the return code was 200';
    exit;
}

if (is_file($LOCK_FILE)) {
    # if the file was last modified < 55 secs ago, then show a 500 error.
    # we want to limit it to 1 request every minute
    # (55 secs used so we have a little leeway in case a request comes in slightly early)
    $one_min_ago = time() - 55;
    if (filemtime($LOCK_FILE) > $one_min_ago) {
        header('HTTP/1.0 500 Internal Server Error');
        exit;
    }
}

touch($LOCK_FILE);

if (!empty($_GET['checkdb']) || !empty($_GET['checkreplication'])) {

    define('SQ_SYSTEM_ROOT', '/app/matrix/');
    define('SQ_LOG_PATH', SQ_SYSTEM_ROOT.'/data/private/logs');
    $return_code = '200';

    require_once SQ_SYSTEM_ROOT.'/core/include/init.inc';
    require SQ_SYSTEM_ROOT . '/data/private/conf/db.inc';

    if (isset($_GET['checkdb']) && $_GET['checkdb'] == 1) {
        // we are checking for database availablity

        $error = FALSE;
        try {
            MatrixDAL::dbConnect('db');
        } catch (Exception $e) {
            $return_code = '500';
            $error = TRUE;
        }

        if (!$error) {
            $query = MatrixDAL::preparePdoQuery('SELECT assetid FROM sq_ast WHERE assetid = 1');
            try {
                $res = MatrixDAL::executePdoOne($query);
            } catch (Exception $e) {
                $return_code = '500';
            }
            MatrixDAL::dbClose('db');
        }
    }

    if (isset($_GET['checkreplication']) && $_GET['checkreplication'] == 1) {
        // we are checking that replication is working
        $file_system_okay = TRUE;

        error_reporting(15);
        // find the last line in the rsync.log file and parse it for an error
        // we need to do a bit of extra work so that we dont read the whole file
        // into memory as its likely to be big.
        $fp = fopen('/var/log/rsync.log', 'r');

        if ($fp) {
            $info = fstat($fp);
            // if the modified time of the file is greater than 3 minutes
            // then rsyncing has not occured

            if (time() > $info['mtime'] + (3 * 60)) {
                $file_system_okay = FALSE;
            } else {
                // set the file pointer to the end of the file
                fseek($fp, 0, SEEK_END);
                $c = null;
                // find the beginning of the last line
                while ($c != "\n") {
                    $c = fgetc($fp);
                    fseek($fp, ftell($fp) - 2);
                }
                // we are at the last char of the last line so go forward two places
                fseek($fp, ftell($fp) + 2);
                $last_line = fgets($fp);

                list($date, $time, $pid, $msg) = preg_split('/\s+/', $last_line, 4);

                $error_msg = 'rsync error';
                if (substr(trim($msg), 0, strlen($error_msg)) == $error_msg) {
                    $file_system_ok = FALSE;
                }
            }
        } else {
            $file_system_okay = FALSE;
        }

        $db_okay = TRUE;

        // connect to the database as the REPADM user
        $dsn_info = $db_conf['db'];

        $dsn_info['username'] = 'repadm';
        $dsn_info['password'] = 'repadm';

        try {
            MatrixDAL::dbConnect($dsn_info, 'dbrepl');
            $db = MatrixDAL::getDb('dbrepl');
            $conn_ok = TRUE;
        } catch (Exception $e) {
            $db_okay = FALSE;
            $conn_ok = FALSE;
        }

        if ($conn_ok) {
            // check whether there is a normal status
            $status = '';
            $query = MatrixDAL::preparePdoQuery('SELECT status FROM sys.dba_repcat');
            try {
                $status = MatrixDAL::executePdoOne($query);
            } catch (Exception $e) {
                $db_okay = FALSE;
            }

            if (strtoupper($status) !== 'NORMAL') {
                $db_okay = FALSE;
            }

            // verify that there are no errors in the queue
            $sql = 'SELECT count(*) FROM deferror';
            $errors = -1;
            $query = MatrixDAL::preparePdoQuery($sql);
            try {
                $errors = MatrixDAL::executePdoOne($query);
            } catch (Exception $e) {
                $db_okay = FALSE;
            }

            if ($errors != 0) {
                $db_okay = FALSE;
            }

            // verify that there are no transactions that have not been processed in 5 minutes
            $sql = 'SELECT count(*) FROM deftrandest';
            $trans = -1;

            $query = MatrixDAL::preparePdoQuery($sql);
            try {
                $trans = MatrixDAL::executePdoOne($query);
            } catch (Exception $e) {
                $db_okay = FALSE;
            }

            // since the default is -1, we need to check both conditions.
            if ($trans < 0 || $trans > 200) {
                $db_okay = FALSE;
            }
        }

        if ($file_system_okay) {
            if (!$db_okay) {
                $return_code = '502';
            }
        } else {
            if ($db_okay) {
                $return_code = '501';
            } else {
                $return_code = '503';
            }
        }
    }

    if ($return_code == '200') {
        header('HTTP/1.0 200 OK');
    } else {
        header('HTTP/1.0 '.$return_code.' Internal Server Error');
    }
    echo 'the return code was '.$return_code;
    exit;
}

$output = '';

define('SQ_SYSTEM_ROOT', realpath(dirname(__FILE__) . '/../..'));
$return_code = '200';

require_once SQ_SYSTEM_ROOT.'/core/include/init.inc';

require SQ_SYSTEM_ROOT . '/data/private/conf/db.inc';

$dsn_list = array(
    'db'       => 'SELECT count(msgid) FROM sq_internal_msg', //sq_internal_msg is a replicated target and commonly updated
    'db2'      => 'UPDATE sq_ast SET updated = NOW() WHERE type_code = \'root_user\'', //check write perms for main DSN
    'db3'      => 'UPDATE sq_ast SET updated = NOW() WHERE type_code = \'root_user\'', //check write perms for secondary DSN
    'dbcache'  => 'SELECT count(assetid) FROM sq_cache', //check cache size and accessibility
    'dbsearch' => 'SELECT count(assetid) FROM sq_sch_idx'
);

$db_type = $db_conf['db']['type'];

$all_db_queries = array();

if ($db_type == 'pgsql') {
    $all_db_queries[] = "SELECT count(*) as count from pg_stat_activity where current_query = '<IDLE>'";
}

if ($db_type == 'oci8' || $db_type == 'oci') {
    $dsn_list['db2'] = str_replace('NOW()', 'SYSDATE', $dsn_list['db2']);
    $dsn_list['db3'] = str_replace('NOW()', 'SYSDATE', $dsn_list['db3']);
}

$last_dsn = null;
foreach ($dsn_list as $dsn_name => $query) {
    $start_time = time();
    $output .= $dsn_name.':';

    switch ($dsn_name) {
        case 'dbcache':
        case 'dbsearch':
            if ($dsn_name == 'dbcache') {
                $alt_name = 'db2';
            }
            if ($dsn_name == 'dbsearch') {
                $alt_name = 'db';
            }

            if (!isset($db_conf[$dsn_name])) {
                $dsn_name = $alt_name;
                break;
            }
            if ($db_conf[$dsn_name] === null) {
                $dsn_name = $alt_name;
                break;
            }
        break;
    }

    $dsn = $db_conf[$dsn_name]['DSN'];

    if ($dsn !== $last_dsn) {
        $GLOBALS['SQ_SYSTEM']->changeDatabaseConnection($dsn_name);
    }

    $qry = MatrixDAL::preparePdoQuery($query);
    try {
        // if it's a select query, use executePdoOne
        // if it's not, use execPdoQuery - so it will return the number of affected rows.

        if (strtolower(substr($query, 0, 6)) === 'select') {
            $res = MatrixDAL::executePdoOne($qry);
        } else {
            $res = MatrixDAL::execPdoQuery($qry);
        }
    } catch (Exception $e) {
        $res = '-1';
        $return_code = '500';
    }

    if ($res === NULL && ($dsn_name == 'db2' || $dsn_name == 'db3')) {
        $res = 1;
    }

    $output .= $res.':'.ceil(time() - $start_time);

    foreach ($all_db_queries as $qry) {
        $query = MatrixDAL::preparePdoQuery($qry);
        try {
            $res = MatrixDAL::executePdoOne($query);
        } catch (Exception $e) {
            $return_code = '500';
            $res = '-1';
        }
        $output .= ':' . $res;
    }

    $output .="\n";

    $last_dsn = $dsn;
}

if ($db_type == 'pgsql') {
    // fairly broad brush used here, we know that slon uses '<schema_name>_(logtrigger|denyaccess)_[0-9]', so we'll use that to sniff for slon and it's schema name. denyaccess indicates slave.
    $slon_schema_query = 'SELECT REGEXP_REPLACE(trigger_name, \'(_logtrigger_|_denyaccess_)[0-9]+\', \'\') FROM information_schema.triggers WHERE (trigger_name LIKE \'%_logtrigger_%\' OR trigger_name LIKE \'%_denyaccess_%\') limit 1';

    // {SLON_SCHEMA} is replaced after the schema name is worked out.
    $slon_schema_lag_query = 'SELECT cast(extract(epoch from st_lag_time) as int8) FROM {SLON_SCHEMA}.sl_status WHERE st_origin = (SELECT last_value FROM {SLON_SCHEMA}.sl_local_node_id)';

    $output .= 'slon:';
    $lag = 0;

    // Slon test
    $db_conf['dbslon'] = $db_conf['db2'];
    MatrixDAL::dbConnect($db_conf['dbslon'], 'dbslon');
    $db = MatrixDAL::getDb('dbslon');

    $start_time = time();
    $query = MatrixDAL::preparePdoQuery($slon_schema_query);
    try {
        $slon_schema = MatrixDAL::executePdoOne($query);
    } catch (Exception $e) {
        $slon_schema = '';
        $return_code = '500';
    }

    if (!empty($slon_schema)) {
        $output .= $slon_schema.':';
        $query = MatrixDAL::preparePdoQuery(str_replace('{SLON_SCHEMA}', $slon_schema, $slon_schema_lag_query));
        try {
            $lag = MatrixDAL::executePdoOne($query);
        } catch (Exception $e) {
            $lag = '-1';
            $return_code = '500';
        }
    } else {
        $output .= '0:';
    }
    $output .= $lag.':'.ceil(time() - $start_time) ."\n";
}

if ($return_code == '200') {
    header('HTTP/1.0 200 OK');
} else {
    header('HTTP/1.0 '.$return_code.' Internal Server Error');
}

print $output;

?>