Robust Method For Testing Database Handles

Discussion in 'Perl Misc' started by Keg, Apr 21, 2004.

  1. Keg

    Keg Guest

    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
     
    Keg, Apr 21, 2004
    #1
    1. Advertising

  2. Keg

    Tore Aursand Guest

    On Wed, 21 Apr 2004 07:09:44 -0700, Keg wrote:
    > 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
    }


    --
    Tore Aursand <>
    "The road to hell is full of good intentions." (Bruce Dickinson)
     
    Tore Aursand, Apr 21, 2004
    #2
    1. Advertising

  3. Keg

    Guest

    (Keg) wrote:
    > 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

    --
    -------------------- http://NewsReader.Com/ --------------------
    Usenet Newsgroup Service $9.95/Month 30GB
     
    , Apr 22, 2004
    #3
  4. Keg

    pkent Guest

    In article <>,
    Tore Aursand <> wrote:

    > On Wed, 21 Apr 2004 07:09:44 -0700, Keg wrote:
    > > 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
    > }


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

    P

    --
    pkent 77 at yahoo dot, er... what's the last bit, oh yes, com
    Remove the tea to reply
     
    pkent, Apr 22, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Larry Maturo
    Replies:
    1
    Views:
    455
    Jason Newell
    Nov 4, 2005
  2. Martin Smith

    robust iterator implementation

    Martin Smith, Jan 24, 2005, in forum: C++
    Replies:
    4
    Views:
    4,172
    msalters
    Jan 25, 2005
  3. Tony
    Replies:
    43
    Views:
    863
  4. eino
    Replies:
    1
    Views:
    454
    =?ISO-8859-15?Q?=22Martin_v=2E_L=F6wis=22?=
    May 8, 2007
  5. Chamnap
    Replies:
    0
    Views:
    106
    Chamnap
    Jul 16, 2007
Loading...

Share This Page