Robust Method For Testing Database Handles

[Keg]

Hey all,

Trying to determine a reliable, rock-solid, low-cost method of testing
a database handle.

If the database is down or the connection fails or anything that will
cause operations on a database handle to fail, will my database handle
be undefined then? I wonder because the following code gives me
inconsistent results when the system load gets high:

if (!defined($dbh1)) {
.....


Is this a rock solid way of testing your database connection? Does
anyone use $dbh1->ping??? The documentation says that unless you know
for sure you need to use the ping() method, you likely don't.

Basically I have a daemon written in perl that runs 24/7 and analyzes
various processes and logs information to a mysql database. Inside my
main loop I check the database handle each iteration and if it is
down, I spin until a connection can be re-established. However, my
problem lies in the testing of the connection and how to properly do
this.

I can't afford to disconnect and reconnect, this is a very
high-throughput process. I also want to avoid using a SQL query just
to test if the database handle is still valid or not.

My environment is RH 9 running a vanilla 2.4.20-28.9smp kernel, a
vanilla build of mysql-max-4.0.18, perl DBI 1.42, DBD::mysql 2.9003,
and vanilla perl 5.8.

Thx,
Keg

 

[Tore Aursand]

Trying to determine a reliable, rock-solid, low-cost method of testing
a database handle.

I happen to use this all the time, which works all the time (and under
heavy load);

unless ( defined $dbh || $dbh->ping() ) {
# Connect
}

 

[ctcgag]

Hey all,

Trying to determine a reliable, rock-solid, low-cost method of testing
a database handle.

If the database is down or the connection fails or anything that will
cause operations on a database handle to fail, will my database handle
be undefined then?

Not necessarily. If Perl does not yet realize the handle is down, it can't
set the handle to undef.

I wonder because the following code gives me
inconsistent results when the system load gets high:

if (!defined($dbh1)) {
....

Is this a rock solid way of testing your database connection? Does
anyone use $dbh1->ping??? The documentation says that unless you know
for sure you need to use the ping() method, you likely don't.

The only way to robustly do this is to put recovery code into each and
every place that you access the database. If the database is in the habit
of disconnecting you, there is no way to know that a disconnection did not
happen between your connection-testing code and your connection-using code.
That being the case, all your connection-using code needs to be able to
detect and recover. Anything less than this might be
good-enough-for-what-it's-for, but it will not be robust. Once all your
connection-using code can detect and recover, there is no longer any point
in having separate connection-testing code.

Xho

 

[pkent]

I happen to use this all the time, which works all the time (and under
heavy load);

unless ( defined $dbh || $dbh->ping() ) {
# Connect
}

I second that - we use the same kind of thing at work. AFAIK that sort
of thing is exactly what ping() is for.

P