daveime has asked for the wisdom of the Perl Monks concerning the following question:

I've been trying to implement a MySQL connection timeout using the Time::Hires module, as the one within the DBI module only has a granularity of seconds. This is to connect to a remote MySQL database on a VPN, as we've seen that the connection times can run as high as 3 seconds ... so we're trying to understand if there's any benefit from giving up after 200ms and trying a fresh attempt. I've got the guts of the script worked out, to timeout after n milliseconds, and retry up to n times ... I'm just a bit worried about resources getting gobbled up, forgotten about etc etc. Basically it's my first experience with signals in general, and they scare the hell out of me. Can someone review my code snippet, and tell me your thoughts ... 
use Time::HiRes qw ( gettimeofday tv_interval setitimer ITIMER_REAL );

use DBI;

$ConnectTimeOut = 0.2; # 200 milliseconds ...
$ConnectRetrys = 5;
$ConnectAttempts = 0;

$Handle = "";

while (($ConnectAttempts < $ConnectRetrys) && ($Handle eq "")) {
    eval {
        local $SIG{ALRM} = sub { die "DB CONN FAIL" };
        setitimer(ITIMER_REAL, $ConnectTimeOut);
        $Handle = DBI->connect( "DBI:mysql:mydatabase;host=111.111.111
+.111;port=3306","myusername","mypassword",{ RaiseError => 1});
        setitimer(ITIMER_REAL, 0);
    };
    if ($@ and $@ =~ /DB CONN FAIL/) {
        print "CONNECT TIMED OUT\n";
        $ConnectAttempts++;
    }
    if ($@ and $@ !~ /DB CONN FAIL/) {
        print "SOME OTHER CONNECTION PROBLEM\n";
        $ConnectAttempts++;
    }
    if (!$@) {
        print "CONNECT IS GOOD\n";
    }
}

[download]

Replies are listed 'Best First'.
Re: DBI TimeOuts
by kesterkester (Hermit) on Jun 22, 2004 at 19:49 UTC
    This doesn't deal with your main question, but your if-block could be rewritten a little more cleanly as: 

    if ( $@ ) {
    ++$ConnectAttempts;
    print "", ( $@ =~ /DB CONN FAIL/ )
                 ? "CONNECT TIMED OUT\n"
                 : "SOME OTHER CONNECTION PROBLEM\n";
}
else {
    print "CONNECT IS GOOD\n";
}

    [download]
    Update: fixed print statement.
[reply]
[d/l]
      Further to my original post, I've been logging the die statements ... I've seen occasionally that the cleanup fails with this error ... (in cleanup) dbih_getcom handle DBI::db=HASH(0x8388b30) is not a DBI handle (has no magic) My concern is that this cleanup failure may be wasting resources / memory etc., and eventually kill the server. Anyone have any views ? BTW, thanks for the if-block rewrite kesterkester.
[reply]

Back to Seekers of Perl Wisdom