You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Michael Holly <mh...@talisentech.com> on 2003/05/06 19:07:31 UTC

Broken Pipe error in DBCP when DB has been restarted.

Hi

I am running Tomcat 4.1.18, Oracle 8.1.7 using the thin driver, and Commons
DBCP.

My problem mainly stems from the fact that my company has a policy to reboot
the database server every night. I know, I know, Oracle on Sun should run
forever.

Anyway, I can't get around the policy. Perhaps someone here can help me get
around it.

Since the DB gets restarted, the connections that the pool has open become
invalid. My application gets a SQLException: IoException: Broken Pipe error
when ever I try access the JSP page in the morning. It is configured with
two connections and the page will error out twice before finally rendering.

I'm in the process of adding a validation query to my pool, but I am not
sure that this will help. See link for details
http://www.mail-archive.com/tomcat-user@jakarta.apache.org/msg88755.html

I'm not real happy about this validation query because it is just more
network traffic. I'm interseted in the 3rd option described in the snippet
below.  Can anyone point me how to configure my pool like this?

Thanks

Michael

-----------------------------------------------------------------
> Weblogic's JDBC pool has two options:
>
> 1. Validate on checkout
> 2. Validation on return
>
> You can select any or none of the options.  If you don't select any and
> the pooled connections go bad, then you'll get bad connections when
> checking out. I personally like validating the connection on checkout,
> although your code is then tied to a pool that does this.
>
> -Dave


Right, and DBCP does those two options, plus a third: validation while
idle, the periodically walks through the connections sitting idle in the
pool and evicts the ones that go bad.

Personally, I use the third option almost exclusively, attempting to
strike some balance between the validation overhead and the time it will
take the app to recover (i.e., purge the bad connections and establish new
ones).

It's really the same problem with or without pooling, sometimes your
connection will go bad.  Validation or failure detection can't prevent
this problem, it can only try to minimize it.  (For example, assume you've
got this magical "detect a dropped connection and quietly substitute a
valid one, at any point in the client code" facility.  It's still possible
for a connection to be dropped in the midst of a client transaction, and
there may be little the connection wrapper can do to make that problem
quietly go away.  Record and replay the executed statements?  How do you
know whether the exception-throwing statement made it to the database or
not?)

-----------------------------------------------------------------

RE: Broken Pipe error in DBCP when DB has been restarted.

Posted by Michael Holly <mh...@talisentech.com>.
I found out more info.

I implemented the validation query for the connection pool. This seemed to
work except I was getting huge intermittant delays on some queries.

My DBA went to work and found out that we are running Oracle with MTS. He
also found out that MTS has an error with the dispatcher process causing up
to 60+ second intermittant delays.  The solution was to set up the
connection pool to request a dedicated connection from Oracle.

Here is a copy of the config for the DBCP pool from my server.xml.

[code]
  <Resource name="jdbc/oracle_myco" auth="Container"
         type="javax.sql.DataSource"/>

  <ResourceParams name="jdbc/oracle_myco">
    <parameter>
      <name>factory</name>
      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>
    <parameter>
      <name>driverClassName</name>
      <value>oracle.jdbc.driver.OracleDriver</value>
    </parameter>
    <parameter>
      <name>url</name>
      <!-- old style connect string -->
      <!--value>jdbc:oracle:thin:@abraxxas:1521:myco</value-->
      <!-- new style connect string for dedicated connection -->
      <value>jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP)(HOST = abraxxas.myco.com)(PORT = 1521))) (CONNECT_DATA =
(SERVER = DEDICATED) (SID = MYCO)))</value>
    </parameter>
    <parameter>
      <name>username</name>
      <value>myco</value>
    </parameter>
    <parameter>
      <name>password</name>
      <value>#######</value>
    </parameter>
    <parameter>
      <name>maxActive</name>
      <value>20</value>
    </parameter>
    <parameter>
      <name>maxIdle</name>
      <value>10</value>
    </parameter>
    <parameter>
      <name>maxWait</name>
      <value>-1</value>
    </parameter>
    <parameter>
      <name>validationQuery</name>
      <value>select 'validationQuery' from dual</value>
    </parameter>
    <parameter>
      <name>testOnBorrow</name>
      <value>true</value>
    </parameter>
  </ResourceParams>
[/code]

Notice the odd look for the db url.  A entry on Oracle's metalink said that
to get a dedicated link you must copy your SID entry from the tnsnames.ora
file and add the entry (SERVER = DEDICATED) to get the connection.
Obviously, the oracle jdbc driver can handle this.

The other thing to look for is the addition of the

[code]
    <parameter>
      <name>validationQuery</name>
      <value>select 'validationQuery' from dual</value>
    </parameter>
    <parameter>
      <name>testOnBorrow</name>
      <value>true</value>
    </parameter>
[/code]

parameters for the DBCP pool.


For more info on what parameters are available see
http://www.mail-archive.com/commons-user@jakarta.apache.org/msg02953.html

and

http://www.mail-archive.com/commons-user@jakarta.apache.org/msg02950.html

Thanks for the help!

Michael


-----Original Message-----
From: Michael Holly [mailto:mholly@talisentech.com]
Sent: Tuesday, May 06, 2003 12:08 PM
To: commons-user@jakarta.apache.org
Subject: Broken Pipe error in DBCP when DB has been restarted.


Hi

I am running Tomcat 4.1.18, Oracle 8.1.7 using the thin driver, and Commons
DBCP.

My problem mainly stems from the fact that my company has a policy to reboot
the database server every night. I know, I know, Oracle on Sun should run
forever.

Anyway, I can't get around the policy. Perhaps someone here can help me get
around it.

Since the DB gets restarted, the connections that the pool has open become
invalid. My application gets a SQLException: IoException: Broken Pipe error
when ever I try access the JSP page in the morning. It is configured with
two connections and the page will error out twice before finally rendering.

I'm in the process of adding a validation query to my pool, but I am not
sure that this will help. See link for details
http://www.mail-archive.com/tomcat-user@jakarta.apache.org/msg88755.html

I'm not real happy about this validation query because it is just more
network traffic. I'm interseted in the 3rd option described in the snippet
below.  Can anyone point me how to configure my pool like this?

Thanks

Michael

-----------------------------------------------------------------
> Weblogic's JDBC pool has two options:
>
> 1. Validate on checkout
> 2. Validation on return
>
> You can select any or none of the options.  If you don't select any and
> the pooled connections go bad, then you'll get bad connections when
> checking out. I personally like validating the connection on checkout,
> although your code is then tied to a pool that does this.
>
> -Dave


Right, and DBCP does those two options, plus a third: validation while
idle, the periodically walks through the connections sitting idle in the
pool and evicts the ones that go bad.

Personally, I use the third option almost exclusively, attempting to
strike some balance between the validation overhead and the time it will
take the app to recover (i.e., purge the bad connections and establish new
ones).

It's really the same problem with or without pooling, sometimes your
connection will go bad.  Validation or failure detection can't prevent
this problem, it can only try to minimize it.  (For example, assume you've
got this magical "detect a dropped connection and quietly substitute a
valid one, at any point in the client code" facility.  It's still possible
for a connection to be dropped in the midst of a client transaction, and
there may be little the connection wrapper can do to make that problem
quietly go away.  Record and replay the executed statements?  How do you
know whether the exception-throwing statement made it to the database or
not?)

-----------------------------------------------------------------


---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org