How can I detect an inaccessible database using PHP PDO?
I am using PHP to create a process that monitors our database and hosts a service page on our website if the database is down.
I am using PDO and Oracle.
I am trying to use a single database connection and query it once a minute, alerting people if there is a problem. However, if the database goes down, the script waits 15 minutes before figuring out what the problem is, so my process, which has to check every minute, looks 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 receive my email immediately, not 15 minutes later. Is there a way to do this while maintaining a persistent connection to the database, or should I create a new one every time I prepare and execute a query?
Here's a snippet of code in case 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);
}
+2
source to share