Perl DBI connect and execute timeout

Question

At work we have a DBA who said his RAC working just fine, but the truth is it isn't. SQL IDEs like Toad or SQL Developer randomly dropping their connections (my suspicion is becouse of the inrorrect network settings of the RAC). I would like to prove my theory with a test. I guess a perl script would to the trick:

step 1. ping the db's IP

step 2. if IP is up attempt to connect to the db

step 3. if connected select sysdate from dual and close connection

step 4. wait some time and start it over again

I have managed to write this in Perl using DBI but I don't know how can I timeout connecting and query execution. Is there some solution for timing out those things?

Changeful

Answer 1

You can use signals in relation to the DBI for implementing a timeout using alarm() and $SIG{ALRM}.

From DBI module on cpan and cpan pod

Timeout

The traditional way to implement a timeout is to set $SIG{ALRM} to refer to some code that will be executed when an ALRM signal arrives and then to call alarm($seconds) to schedule an ALRM signal to be delivered $seconds in the future.

For example:

my $dbh = DBI->connect("DBI:SQLRelay:host=$hostname;port=$port;socket=", $user, $password) or die DBI->errstr;

my $sth = $dbh->prepare($query) or die $dbh->errstr;

  eval {
    local $SIG{ALRM} = sub { die "TIMEOUT\n" }; # \n is required
    eval {
         alarm($seconds);
         if(! $sth->execute() ) { # execute query
                print "Error executing query!\n"
         }
    };
    # outer eval catches alarm that might fire JUST before this alarm(0)
    alarm(0);  # cancel alarm (if code ran fast)
    die "$@" if $@;
  };
  if ( $@ eq "TIMEOUT\n" ) { ... }
  elsif ($@) { ... } # some other error

The first (outer) eval is used to avoid the unlikely but possible chance that the "code to execute" dies and the alarm fires before it is cancelled. Without the outer eval, if this happened your program will die if you have no ALRM handler or a non-local alarm handler will be called.

Dugger

Answer 2

It appears to depend on which DB backend you are connecting to. For instance, DBD::mysql does document such a timeout value:

mysql_connect_timeout

If your DSN contains the option "mysql_connect_timeout=##", the connect request to the server will timeout if it has not been

successful after the given number of seconds.

However, I don't see the same thing documented for Oracle.

I did find a discussion of doing this with signal handling in the DBI documentation.

Folketing