How to detect an unavailable database using PHP PDO?

Question

I am using PHP to create a process that monitors our database and puts up a maintenance page on our website if the database is down.

I'm using PDO and Oracle.

I am trying to use one database connection and query it once a minute, alerting people if there's a problem. However, if the database goes down, the script waits 15 minutes before figuring out that there's a problem, so my process that is supposed to check every minute ends up looking like this:

06:56:46: SUCCESS -- I take down the database after this success
07:12:48: FAILURE - sent email
07:13:48: FAILURE
...

I want to get emailed right away, rather than 15 minutes later. Is there any way to do this maintaining a persistent database connection or do I have to create a new one every time I prepare and run a query?

Here's a chunk of the code, if it's helpful:

$last_email_time = null; // the time of the last error email sent
$db_conn = null;
$script_start_time = time();

while(true) {
    $success = false;

    // attempt to create a database connection
    if(!$db_conn) {
        try {
            $db_connection_data = $g_pdo_connection_data['freedom'];
            $db_conn =  new PDO($db_connection_data['string'], $db_connection_data['user'], $db_connection_data['password']);
            $db_conn->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $db_conn->setAttribute (PDO::ATTR_TIMEOUT, 60);
            if(!$db_conn) {
                throw new Exception("Unable to create a database connection");
            }
        } catch(Exception $e) {
            $last_email_time = handle_error($last_email_time, $e->getMessage());
            $db_conn = null;
        }
    }

    // attempt a query
    if($db_conn) {
        try {
            $q = $db_conn->prepare("SELECT 1 FROM DUAL");
            $q->execute();
            $q->closeCursor();
            if(!$q) {
                throw new Exception("Unable to query the database");
            }
            $success = true;
        } catch(Exception $e) {
            $last_email_time = handle_error($last_email_time, $e->getMessage());
        }
    }

    // remove the maintenance page if we were successful, else clear the connection
    if($success) {
        handle_success();
        $last_email_time = null;
    } else {
        $db_conn = null;
    }

    flush();
    if(ob_get_contents() != '') {
        ob_flush();
    }
    sleep(60);
}

Answer 1

You could just connect and disconnect to the database each time through the loop. Then you'll catch your exception every minute. You'll have to test it to see if it affects performance negatively though.