commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Phil Steitz <phil.ste...@gmail.com>
Subject Re: [dbcp]Closed Connection or Exhausted Resultset error
Date Wed, 22 Apr 2015 13:32:36 GMT
On 4/22/15 5:18 AM, Rohini T Nagaraj wrote:
> Hi All,
>
> The Oracle DB has 37k packages. The issue is seen after running the below 
> query from the program or when using 
> DatabaseMetadata.getProcedureColumns()  in a program . We are seeing these 
> errors  "Exhausted Resultset (SQL Code =99999 and vendor code=17,011) " or 
> "Closed Connection :next (SQL code=08003 , Vendor code=17,008)".

Please post a stack trace showing one or both of these errors.
>
> Something happening to connection or resultset getting closed after 
> running the query/getProcedureColumns call. We have investigated further 
> and see this issue of Exhausted Resultset/Connection getting closed only 
> when we use the datasource connection pool logic.
> When we run a standalone java program with the query/getProcedureColumns 
> call , it took 10mins for the customer to get the output and there were no 
> issues.We want the same behavior with datasource connection pool logic .
>
>  
> We are using Tomcat sevrer and 
> org.apache.commons.pool.impl.GenericObjectPool connection pool.We have 
> these connection pool settings done. 

Are you using DBCP or are you just trying to pool the connections
manually using Commons Pool?  What version of pool and / or DBCP? 
How exactly are you creating the connection pool?
>
> connectionPool.setMaxActive(25);
> connectionPool.setMaxIdle(1);
> connectionPool.setMinEvictableIdleTimeMillis(120000);
> connectionPool.setTimeBetweenEvictionRunsMillis(1000);
> connectionPool.setTestOnReturn(false);
> connectionPool.setTestOnBorrow(true);
> connectionPool.setTestWhileIdle(false); 

Why do you have maxIdle set to 1? That is going to cause connections
to get closed when they are returned whenever there is one idle
connection already in the pool.  This will effectively defeat the
purpose of the pool.  Also, with such rigid control on idle
connections, why do you also have the evictor configured to run? 
Every second?  Unless you have special reasons for these settings, I
would change maxIdle to 25 and drop the minEvictableIdleTimeMillis
and timeBetweenEvictionRunsMillis (default will be to have no
evictor runs).
>
> Hence please let us know if there is any fine tuning to be done to make 
> query or DatabaseMetadata.getProcedureColumns() to work when we use 
> datasource connection pool logic.Please suggest us any other timeout or if 
> there is anything else which can help us in this scenario.Thanks
>  
>
> SQL QUERY :
> SELECT * FROM SYS.ALL_ARGUMENTS WHERE PACKAGE_NAME = 'PKG_TEST' AND 
> OBJECT_NAME = 'PRC_INSERT' AND OWNER = 'APPS' ORDER BY SEQUENCE
>
> ERROR:
> java.sql.SQLRecoverableException: Closed Connection: next
> at 
> oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:233)
>
>
>
> Thanks and Regards,
> Rohini T Nagaraj,
> WebSphere CastIron QA Team,
>  IBM INDIA PRIVATE LIMITED,
> DC1-3A-003,DLF IT PARK,Chennai - 600089
> Extn # : 21820 and Mobile #: 9962020675



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


Mime
View raw message