Thanks for reply. You are right about clean up, but is there cleaner way to timeout the connection? My application is single threaded and can't afford to block and wait too long on connect (which it does if I don't raise an alarm).
Thanks, J -----Original Message----- From: Hendrik Schumacher [mailto:h...@activeframe.de] Sent: Thu 1/15/2009 4:00 AM To: Martin Gainty Cc: Jaya Meghani; dbi-users@perl.org Subject: RE: DBI connect and ports issue Just an idea: You raise an alarm when DBD::Oracle/OCI takes too long waiting for a connection. If you just quit the connection attempt then, OCI may never have a chance to clean up its connection attempt (especially the opened UDP port). Am Do, 15.01.2009, 04:01, schrieb Martin Gainty: > > Jaya- > > Port 1526 sounds odd as the default port for oracle is 1521 > > Anyone see anything obvious? > Martin > ______________________________________________ > Disclaimer and confidentiality note > Everything in this e-mail and any attachments relates to the official > business of Sender. This transmission is of a confidential nature and > Sender does not endorse distribution to any party other than intended > recipient. Sender does not necessarily endorse content contained within > this transmission. > > > > >> Subject: DBI connect and ports issue >> Date: Wed, 14 Jan 2009 16:31:12 -0500 >> From: jaya.megh...@vonage.com >> To: dbi-users@perl.org >> >> Hi, >> >> >> >> >> >> My first post too the list! >> >> >> >> First some environment details: >> >> Red Hat Enterprise Linux WS release 4 (Nahant Update 4) >> >> Perl Version = 5.008007 >> >> DBI Version = 1.48 >> >> DBD::Oracle Version = 1.16 >> >> Issue: >> >> My application connects to Oracle and some times the DB host is down. I >> use sigaction to put a timeout on DBI->connect and retry again till >> connection is established. >> >> The problem is: I see a lot of unused ports for the application process. >> >> 1). TCP ports in CLOSE_WAIT and ESTABLISHED states. >> >> 2). Ephemeral UDP ports. >> >> These ports accumulate over time as DB connection is lost and >> re-established. >> >> I use lsof and netstat commands to check the stats on ports. >> >> >> >> To reproduce the issue I have this script (see below), I don't see any >> TCP ports waiting in CLOSE_WAIT and ESTABLISHED states but each time the >> script tries to connect (and is timed-out) a new ephemeral UDP port is >> created. >> >> I use iptables to DROP any packets to DB host to simulate DB host down >> scenario. >> >> ************************************************************************ >> ****** >> >> Output: >> >> Here is the output of script and Port stats: >> >> >> >> $perl sigTry.perl >> >> I am in handler >> >> Trying to connect >> >> Error connecting to DB >> >> I out of handler >> >> <Hit Return key> >> >> I am in handler >> >> Trying to connect >> >> Error connecting to DB >> >> I out of handler >> >> <Hit Return key> >> >> I am in handler >> >> Trying to connect >> >> Error connecting to DB >> >> I out of handler >> >> #Command to see ports stats >> >> $lsof -p `pgrep perl` | egrep "TCP|UDP" >> >> perl 28572 jmeghani 4u IPv4 2132909 UDP >> localhost:33544 >> >> perl 28572 jmeghani 5u IPv4 2132913 TCP host1:33466-> >> someDBhost:1526 (SYN_SENT) >> >> perl 28572 jmeghani 7u IPv4 2132924 UDP >> localhost:33545 >> >> perl 28572 jmeghani 8u IPv4 2132926 TCP host1-> >> someDBhost:1526 (SYN_SENT) >> >> perl 28572 jmeghani 10u IPv4 2132927 UDP >> localhost:33546 >> >> perl 28572 jmeghani 11u IPv4 2132929 TCP >> host1:33468->someDBhost:1526 (SYN_SENT) >> >> SYN_SENT ports go away with time but UDP ports (33544-33546) don't. >> >> ************************************************************************ >> ****** >> >> Finally actual script (I have removed actual DB details) >> >> Script: >> >> ************************************************************************ >> ****** >> >> $ENV{ORACLE_HOME} = '/usr/vendor/pkg/oracle/product/10.2.0'; >> >> use strict; >> >> use warnings; >> >> use POSIX; >> >> use DBI; >> >> my $db_hostname = 'someDBhost'; >> >> my $db_port = 1526; >> >> my $db_user = 'test'; >> >> my $db_pass = 'test'; >> >> my $db_sid = 'SID'; >> >> my $dbh = undef; >> >> sub finished { >> >> print "I am outta here\n"; >> >> exit(1); >> >> } >> >> sub handler { >> >> print "I am in handler\n"; >> >> if (!defined($dbh)) { >> >> print "Trying to connect\n"; >> >> my $old_action = POSIX::SigAction->new(); >> >> my $action = POSIX::SigAction->new( sub{die 'TIMEOUT'; >> }); >> >> sigaction(SIGALRM,$action, $old_action); >> >> my $old_t; >> >> eval { >> >> sigprocmask(SIG_UNBLOCK, >> POSIX::SigSet->new(SIGALRM)); >> >> $old_t = alarm(2); >> >> $dbh = >> DBI->connect("dbi:Oracle:host=$db_hostname;sid=$db_sid;port=$db_port", >> >> $db_user, $db_pass, >> >> {PrintError => 0, RaiseError => 1, >> AutoCommit => 0}); >> >> alarm(0); >> >> print "I am connected\n"; >> >> }; >> >> alarm(0); >> >> sigaction(SIGALRM, $old_action); >> >> alarm($old_t); >> >> if ($@) { >> >> if(defined($dbh)){ >> >> print "dbh was defined\n"; >> >> $dbh=undef; >> >> } >> >> print("Error connecting to DB\n"); >> >> } >> >> } >> >> else { >> >> print "Already connected\n"; >> >> } >> >> print "I out of handler\n"; >> >> } >> >> sub main { >> >> my $action_die = POSIX::SigAction->new(\&finished); >> >> sigaction(SIGINT,$action_die); >> >> my $old_action = POSIX::SigAction->new(); >> >> my $action = POSIX::SigAction->new(\&handler); >> >> sigaction(SIGALRM,$action,$old_action); >> >> while(1) >> >> { >> >> alarm(3); >> >> my $foo = <STDIN>; >> >> } >> >> } >> >> exit(main()); >> >> ********************************************************************** >> >> >> >> If you are still reading ;) any help is greatly appreciated. >> >> >> >> >> >> Jaya >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > > _________________________________________________________________ > Windows Live*: Keep your life in sync. > http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t1_allup_howitworks_012009
