perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Xinhuan Zheng <xzh...@christianbook.com>
Subject Re: Apache::DBI connect
Date Thu, 07 Nov 2013 14:46:40 GMT
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 <pharkins@gmail.com<mailto:pharkins@gmail.com>>
Date: Thursday, November 7, 2013 8:16 AM
To: Xinhuan Zheng <xzheng@christianbook.com<mailto:xzheng@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 <xzheng@christianbook.com<mailto:xzheng@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 <pharkins@gmail.com<mailto:pharkins@gmail.com>>
Date:  Wednesday, November 6, 2013 5:54 PM
To:  Xinhuan Zheng <xzheng@christianbook.com<mailto:xzheng@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
<xzheng@christianbook.com<mailto:xzheng@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




Mime
View raw message