Hi All,
I thought I would share this message with you, in case you
a) did not know of this problem, or
b) you know of a "cure" for this problem.
I do not think this a PERL or a DBI issue per se, but it affects the users
of DBI.
Comments/Information welcome...
Lincoln
Sent to our internal DBA support group:
-----Original Message-----
From: me
Subject: crippling problem with signals and Oracle 9i client libriaries
Hi all,
I have found what I believe to be a problem with the Oracle 9.2.0 (9i)
client libraries and signal handling which I think is crippling. It will
PREVENT us from fully upgrading to Oracle 9i until it can be fixed.
We all know that Oracle client hangs for a VERY long time (something like 10
minutes), if sqlnet can not reach the box it is told a database is on (i.e.
the box is down). We have worked around this "bug" with the use of SIGALRM
as illustrated in the test program which follows:
#!/usr/bin/env perl
use strict;
use warnings;
use DBI;
use Time::HiRes qw( ualarm );
my ( $timeout ) = @ARGV;
$timeout = 3 if not defined $timeout;
print "using timeout of $timeout seconds\n" ;
eval
{
local $SIG{ALRM} = sub
{
die "connect timeout after $timeout seconds" ;
};
ualarm($timeout * 1_000_000);
my $dbh = DBI->connect( "dbi:Oracle:dbfail" ,"foo" ,"bar"
,{ AutoCommit=>0 ,RaiseError=>1 ,PrintError=>1 } );
};
ualarm(0);
if ( $@ )
{
die $@;
}
With the Oracle 8.x libraries this code hangs until the timer expires
(default=3 seconds), and then prints:
connect timeout after 3 seconds at ./test.connect.dbfail.pl line 16.
This is the desired behavior, because it returns control to the program,
which can then handle the error.
I have run this code against:
perl 5.6.1 and Oracle 8.1.7 (sun and HP) (succeeds)
perl 5.6.1 and Oracle 8.1.5 (linux) (succeeds)
perl 5.8.0 and Oracle 8.1.5 (linux) (succeeds)
perl 5.8.0 and Oracle 9.2.0 (sun and HP) (FAILS)
With Oracle 9.x libraries this code hangs and never returns. Further, the
only signal the process will accept is KILL (9), which it has to accept.
This tells me that the 9.x libraries are disabling all or most signals,
making it impossible for us to use the Oracle 9i libraries in servers.
DBAs: Please research this on OTN, and if necesssary open a bug report with
Oracle.
THIS IS A BLOCKING ISSUE for completing the 9.2 Oracle upgrade, and being
able to remove the 8.1.7 client.
If we can not get a resolution of this issue from Oracle, I will have to
rebuild the perl 5.8.0 tree with Oracle 8.1.7 libraries (instead of the
9.2.0 libraries it is currently linked with).
I hope Oracle already has a patch for this problem.
Lincoln
PS, although I think it not relevant, this is DBI version 1.37 and
DBD::Oracle version 1.14. I believe I would be able to replicate this with
earlier versions of DBI and DBD:Oracle.
