Clever Geek Handbook

Perl DBI connect and timeout

At work, we have a DBA who said that his RAC works just fine, but the truth is that it isn't. SQL IDEs like Toad or SQL Developer are accidentally dropping their connections (my suspicion stems from incorrect RAC network settings). I would like to prove my theory with a test. I assume the perl script will hit:

step 1.ping db IP

step 2. If IP tries to connect to db

step 3. If connected, select sysdate from double and closed connection

Step 4. Wait for a while and start it again

I managed to write this in Perl using DBI, but I don't know how I can timeout to connect and execute the request. Is there some kind of solution to timeout these things?

+3


source to share


2 answers


You can use signals against DBI to implement timeout using alarm()

and $SIG{ALRM}

.

From DBI module to cpan and cpan pod



Time-out

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

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 an alarm is canceled before it is triggered. Without an external eval, if this happens, your program will die unless you have an ALRM handler or a non-local handler to be called.

+5


source


It seems to depend on which DB you are connecting to. For example, DBD :: mysql documents a timeout value like this:

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

successfully after the specified number of seconds.



However, I don't see the same document as Oracle.

I found a discussion about this with signal handling in the DBI documentation .

+2


source






More articles:


All Articles