Hi Perrin,
The DRCP settings is configured as the document expects.
I made a simple script outside mod_perl and use DBI to test re-connect to
Oracle. The test shows where the problem is.
#!/usr/local/bin/perl
use DBI;
print "first connect\n";
$dbh = DBI->connect('dbi:Oracle:','...','...');
print "first select\n";
$ok = $dbh->do("select 1 from dual");
print "first ok = $ok\n";
print "sleep 501\n";
sleep(501);
print "2nd select\n";
$ok = $dbh->do("select 1 from dual");
print "2nd ok = $ok\n";
print "first disconnect\n";
$dbh->disconnect;
if(!defined($ok)){
print "2nd connect\n";
$dbh1 = DBI->connect('dbi:Oracle:','...','...');
print "last select\n";
$ok = $dbh1->do("select 1 from dual");
print "last ok = $ok\n";
print "last disconnect\n";
$dbh1->disconnect;
}
After sleeping 501 seconds, the 2nd 'select 1 from dual' failed with
"DBD::Oracle::db ping failed: ORA-03113: end-of-file on communication channel"
error, since DRCP MAX_THINK_TIME is configured for 500. The $ok is undef. In
the case if the test does succeed (like the first select), $ok returns 0E0.
Since I changed DBD::Oracle subroutine ping to use 'select 1 from dual', the
code in Apache::DBI subroutine connect needs to be changed also. I attempted to
change it like the following:
200 if ($Connected{$Idx} and ($needping) ) {
201 my $ok = eval{$Connected{$Idx}->ping};
202
203 if(defined($ok) and $ok == 0) {
204 debug(2, "$prefix already connected to '$Idx'");
205
206 # Force clean up of handle in case previous transaction failed
to
207 # clean up the handle
208 &reset_startup_state($Idx);
209
210 return (bless $Connected{$Idx}, 'Apache::DBI::db');
211 }
212 }
Do you think the above code is good?
If I changed the Apache::DBI source code because I used different ping method
in DBD::Oracle, what's the best way to maintain this new code? Whenever I
upgrade this module, would it overwrite the change I made? This is the same
question for DBD::Oracle.
I have another request. The Apache::DBI cached a dead database handle for
apache version 1.3.42 if startup.pl create a database handle. The apache child
processes inherits this dead handle. It doesn't cause application error but it
does take memory space. If there is many apache processes, that's not good. Can
you please identify and change the code for this problem?
Thanks,
- xinhuan
From: Perrin Harkins <phark...@gmail.com<mailto:phark...@gmail.com>>
Date: Thursday, November 7, 2013 8:16 AM
To: Xinhuan Zheng <xzh...@christianbook.com<mailto:xzh...@christianbook.com>>
Cc: mod_perl list <modperl@perl.apache.org<mailto:modperl@perl.apache.org>>
Subject: Re: Apache::DBI connect
Have you used the DRCP settings in DBD::Oracle?
http://search.cpan.org/~pythian/DBD-Oracle-1.66/lib/DBD/Oracle.pm#Oracle_DRCP
>From the description of DRCP, it sounds like you should never lose the
>connection. You may want to check the configuration.
>From your debugging output, it looks like the line that tries to re-connect is
>failing. I'd add some debugging statements to confirm that, and then try
>writing a simple script outside of mod_perl that tries to re-connect to Oracle
>after you drop the connection from the Oracle side. To figure out more,
>you'll probably need to talk to whoever maintains DBD::Oracle.
- Perrin
On Wed, Nov 6, 2013 at 8:55 PM, Xinhuan Zheng
<xzh...@christianbook.com<mailto:xzh...@christianbook.com>> wrote:
Hi Perrin,
I am using Oracle Resident Connection Pool feature. The application randomly
got "ORA-03114 not connect to oracle database" error. As per this document:
http://search.cpan.org/~phred/Apache-DBI-1.12/lib/Apache/DBI.pm
I have changed the DBD::Oracle to use the following ping instead of ora_ping:
sub ping {
my ($dbh) = @_;
my $ret = 0;
eval {
local $SIG{__DIE__} = sub { return (0); };
local $SIG{__WARN__} = sub { return (0); };
# adapt the select statement to your database:
$ret = $dbh->do('select 1');
};
return ($@) ? 0 : $ret;
}
After this change, the application can detect "end-of-file communication" error
then after the application establishes new connection, the application still
throws "ORA-03114 not connect to oracle database" error.
below is the errors when Apache::DBI debug is set:
3861 Apache::DBI need ping: yes
DBD::Oracle::db ping failed: ORA-03113: end-of-file on communication channel
Process ID: 29671
Session ID: 3219 Serial number: 544 (DBD ERROR: OCIStmtExecute/Describe) at
/usr/local/lib/perl5/site_perl/5.8.3/Apache/DBI.pm line 200.
3861 Apache::DBI new connect to '...'
3861 Apache::DBI need ping: yes
3861 Apache::DBI already connected to '...'
3861 Apache::DBI need ping: yes
3861 Apache::DBI already connected to '...'
3861 Apache::DBI need ping: yes
3861 Apache::DBI already connected to '...'
3861 Apache::DBI need ping: yes
3861 Apache::DBI already connected to '...'
3861 Apache::DBI need ping: yes
3861 Apache::DBI already connected to '...'
3861 Apache::DBI need ping: yes
3861 Apache::DBI already connected to '...'
3861 Apache::DBI need ping: yes
DBD::Oracle::st execute failed: ORA-03114: not connected to ORACLE (DBD ERROR:
OCIStmtExecute)
3861 Apache::DBI need ping: yes
3861 Apache::DBI already connected to ''
3861 Apache::DBI disconnect (overloaded)
[Wed Nov 6 16:49:50 2013] [crit] ORA-03114: not connected to ORACLE (DBD
ERROR: OCIStmtExecute).
Process 3861 establishes a new connection. and it says "already connected to"
database. While the query execution failed with "ORA-03114" and the httpd
process is in CLOSE_WAIT state. How does it thinks it's already connected while
it's actually not?
* xinhuan
------------------------------------------------------------------------------------------------------------------------------------------------------------
From: Perrin Harkins <phark...@gmail.com<mailto:phark...@gmail.com>>
Date: Wednesday, November 6, 2013 5:54 PM
To: Xinhuan Zheng <xzh...@christianbook.com<mailto:xzh...@christianbook.com>>
Cc: mod_perl list <modperl@perl.apache.org<mailto:modperl@perl.apache.org>>
Subject: Re: Apache::DBI connect
On Wed, Nov 6, 2013 at 4:07 PM, Xinhuan Zheng
<xzh...@christianbook.com<mailto:xzh...@christianbook.com>> wrote:
I wonder this line of code should be changed to 'DBI->connect(@args) if ($@)'.
If ping failed, that means the connection is already closed, $drh may be no
longer valid, will $drh->connect always return a valid new connection? What's
the difference between DBI->connect
vs $drh->connect?
The $drh there is a DBI driver, not a database handle. It should return a new
connection every time. You can try putting in DBI->connect() there and see if
it fixes the problem for you though. If not, I'd suggest reporting the problem
as a DBD::Oracle issue.
By the way, which Oracle pooling solution are you using and how is it working
out for you, aside from this problem?
- Perrin
