You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by er...@aftenposten.no on 2005/05/12 15:32:42 UTC
Oracle 9.2 connection reset problem
Hi there!
We get a SQLException saying 'connection reset', it seems that the "connection"
is suddenly dead for us, and when we try again running the method ('click' on the
link that access the database again) again it's work just fine. It's happend in a web application
in production. There are about 10 people using the web application, that query
and update the database all the time of the day.
We haven't experience that we have run out of connections, just that this connection reset
problem now and then. Same behaviour working with a Toad tool like called DbVisualizer using
JDBC as the connection, if I don't use the tool for over 15-30 min I guess, same message
from Oracle as we get using SQLMap.
In test environment, we haven't experienced this problem, but that don't mean it's not
a problem there, maybe we just haven't been seen it, since we haven't done full scale
testing there. So it's only in production we have this problem it seems, where our Resin server
with the web application and Oracle db server are two different DMZ, could that have
something to do with it ?
Anyway we have this kind of setup of the datasource. After we introduced the
Pool.Ping configuration, it didn't helped that either..
<settings
cacheModelsEnabled="true"
enhancementEnabled="true"
lazyLoadingEnabled="true"
maxRequests="256"
maxSessions="128"
maxTransactions="32"
useStatementNamespaces="false"/>
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${driver}"/>
<property name="JDBC.ConnectionURL" value="${url}"/>
<property name="JDBC.Username" value="${username}"/>
<property name="JDBC.Password" value="${password}"/>
<property name="Pool.MaximumActiveConnections" value="30"/>
<property name="Pool.MaximumIdleConnections" value="1"/> Should we expand that one ???
<property name="Pool.MaximumWait" value="1000"/>
<property name="Pool.PingQuery" value="select * from dual"/>
<property name="Pool.PingEnabled" value="true"/>
<property name="Pool.PingConnectionsOlderThan" value="1"/>
<property name="Pool.PingConnectionsNotUsedFor" value="1"/>
<property name="Pool.ValidationQuery" value="select 1 from dual"/>
<property name="Pool.LogAbandoned" value="true"/>
<property name="Pool.RemoveAbandoned" value="true"/>
<property name="Pool.RemoveAbandonedTimeout" value="50000"/>
</dataSource>
</transactionManager>
Shouldn't this Pool configuration work ?? = keeping the connections up to date ??
We have tried to tune different values forth and back without any 100% result.
--
When I run some test methods from my IDE, haven't experienced this problem,
and in debug mode I get these kind of feedback that's the Pool stuff seems to work.
DEBUG 13:39:36,186 [main] com.ibatis.common.jdbc.SimpleDataSource - Checked out connection 32550604 from pool.
DEBUG 13:39:36,186 [main] com.ibatis.common.jdbc.SimpleDataSource - Testing connection 32550604...
DEBUG 13:39:36,196 [main] com.ibatis.common.jdbc.SimpleDataSource - Connection 32550604 is GOOD!
.....
DEBUG 13:39:36,266 [main] com.ibatis.common.jdbc.SimpleDataSource - Testing connection 32550604...
DEBUG 13:39:36,266 [main] com.ibatis.common.jdbc.SimpleDataSource - Connection 32550604 is GOOD!
DEBUG 13:39:36,266 [main] com.ibatis.common.jdbc.SimpleDataSource - Returned connection 32550604 to pool.
Any clues anyone ? what we can do about this ?
Thanks for any help!
Best regards,
Erlend Bjørge
Re: Oracle 9.2 connection reset problem
Posted by Fabio Insaccanebbia <fi...@gmail.com>.
Do you have a firewall in the network between the application and
database server?
Maybe this error is due to the closing of the connection by the firewall
(timeout after inactivation).
Bye,
Fabio
erlend.bjorge@aftenposten.no wrote:
> Hi there!
>
> We get a SQLException saying 'connection reset', it seems that the
> "connection"
> is suddenly dead for us, and when we try again running the method
> ('click' on the
> link that access the database again) again it's work just fine. It's
> happend in a web application
> in production. There are about 10 people using the web application,
> that query
> and update the database all the time of the day.
>
> We haven't experience that we have run out of connections, just that
> this connection reset
> problem now and then. Same behaviour working with a Toad tool like
> called DbVisualizer using
> JDBC as the connection, if I don't use the tool for over 15-30 min I
> guess, same message
> from Oracle as we get using SQLMap.
>
> In test environment, we haven't experienced this problem, but that
> don't mean it's not
> a problem there, maybe we just haven't been seen it, since we haven't
> done full scale
> testing there. So it's only in production we have this problem it
> seems, where our Resin server
> with the web application and Oracle db server are two different DMZ,
> could that have
> something to do with it ?
>
> Anyway we have this kind of setup of the datasource. After we
> introduced the
> Pool.Ping configuration, it didn't helped that either..
>
> <settings
> cacheModelsEnabled="true"
> enhancementEnabled="true"
> lazyLoadingEnabled="true"
> maxRequests="256"
> maxSessions="128"
> maxTransactions="32"
> useStatementNamespaces="false"/>
>
> <transactionManager type="JDBC">
> <dataSource type="SIMPLE">
> <property name="JDBC.Driver" value="${driver}"/>
> <property name="JDBC.ConnectionURL" value="${url}"/>
> <property name="JDBC.Username" value="${username}"/>
> <property name="JDBC.Password" value="${password}"/>
> <property name="Pool.MaximumActiveConnections" value="30"/>
> <property name="Pool.MaximumIdleConnections"
> value="1"/> Should we expand that one ???
> <property name="Pool.MaximumWait" value="1000"/>
> <property name="Pool.PingQuery" value="select * from dual"/>
> <property name="Pool.PingEnabled" value="true"/>
> <property name="Pool.PingConnectionsOlderThan" value="1"/>
> <property name="Pool.PingConnectionsNotUsedFor" value="1"/>
> <property name="Pool.ValidationQuery" value="select 1 from
> dual"/>
> <property name="Pool.LogAbandoned" value="true"/>
> <property name="Pool.RemoveAbandoned" value="true"/>
> <property name="Pool.RemoveAbandonedTimeout" value="50000"/>
> </dataSource>
> </transactionManager>
> Shouldn't this Pool configuration work ?? = keeping the connections up
> to date ??
> We have tried to tune different values forth and back without any 100%
> result.
>
> --
>
> When I run some test methods from my IDE, haven't experienced this
> problem,
> and in debug mode I get these kind of feedback that's the Pool stuff
> seems to work.
>
> DEBUG 13:39:36,186 [main] com.ibatis.common.jdbc.SimpleDataSource -
> Checked out connection 32550604 from pool.
> DEBUG 13:39:36,186 [main] com.ibatis.common.jdbc.SimpleDataSource -
> Testing connection 32550604...
> DEBUG 13:39:36,196 [main] com.ibatis.common.jdbc.SimpleDataSource -
> Connection 32550604 is GOOD!
> .....
> DEBUG 13:39:36,266 [main] com.ibatis.common.jdbc.SimpleDataSource -
> Testing connection 32550604...
> DEBUG 13:39:36,266 [main] com.ibatis.common.jdbc.SimpleDataSource -
> Connection 32550604 is GOOD!
> DEBUG 13:39:36,266 [main] com.ibatis.common.jdbc.SimpleDataSource -
> Returned connection 32550604 to pool.
>
> Any clues anyone ? what we can do about this ?
>
>
> Thanks for any help!
>
>
> Best regards,
> Erlend Bjørge
--
Fabio Insaccanebbia
INSAC.COM
e-mail: finsaccanebbia@insac.com - URL: http://www.insac.com
<http://www.insac.com/>
Re: Oracle 9.2 connection reset problem
Posted by Brice Ruth <bd...@gmail.com>.
One thing that may help ... when I set up a connection pool in Tomcat,
I noticed I could set up time-based connection validation, but I could
also set it up such that whenever a connection was requested by the
application, it would validate the connection BEFORE handing it over.
Regardless of the timing. This quickly took care of similar problems I
was having.
Does the data pool you're using have such an option?
Brice
On 5/12/05, erlend.bjorge@aftenposten.no <er...@aftenposten.no> wrote:
>
> Hi there!
>
> We get a SQLException saying 'connection reset', it seems that the
> "connection"
> is suddenly dead for us, and when we try again running the method ('click'
> on the
> link that access the database again) again it's work just fine. It's happend
> in a web application
> in production. There are about 10 people using the web application, that
> query
> and update the database all the time of the day.
>
> We haven't experience that we have run out of connections, just that this
> connection reset
> problem now and then. Same behaviour working with a Toad tool like called
> DbVisualizer using
> JDBC as the connection, if I don't use the tool for over 15-30 min I guess,
> same message
> from Oracle as we get using SQLMap.
>
> In test environment, we haven't experienced this problem, but that don't
> mean it's not
> a problem there, maybe we just haven't been seen it, since we haven't done
> full scale
> testing there. So it's only in production we have this problem it seems,
> where our Resin server
> with the web application and Oracle db server are two different DMZ, could
> that have
> something to do with it ?
>
>
> Anyway we have this kind of setup of the datasource. After we introduced the
> Pool.Ping configuration, it didn't helped that either..
>
> <settings
> cacheModelsEnabled="true"
> enhancementEnabled="true"
> lazyLoadingEnabled="true"
> maxRequests="256"
> maxSessions="128"
> maxTransactions="32"
> useStatementNamespaces="false"/>
>
> <transactionManager type="JDBC">
> <dataSource type="SIMPLE">
> <property name="JDBC.Driver" value="${driver}"/>
> <property name="JDBC.ConnectionURL" value="${url}"/>
> <property name="JDBC.Username" value="${username}"/>
> <property name="JDBC.Password" value="${password}"/>
> <property name="Pool.MaximumActiveConnections" value="30"/>
> <property name="Pool.MaximumIdleConnections" value="1"/>
> Should we expand that one ???
> <property name="Pool.MaximumWait" value="1000"/>
> <property name="Pool.PingQuery" value="select * from dual"/>
> <property name="Pool.PingEnabled" value="true"/>
> <property name="Pool.PingConnectionsOlderThan" value="1"/>
> <property name="Pool.PingConnectionsNotUsedFor" value="1"/>
> <property name="Pool.ValidationQuery" value="select 1 from
> dual"/>
> <property name="Pool.LogAbandoned" value="true"/>
> <property name="Pool.RemoveAbandoned" value="true"/>
> <property name="Pool.RemoveAbandonedTimeout" value="50000"/>
> </dataSource>
> </transactionManager>
>
> Shouldn't this Pool configuration work ?? = keeping the connections up to
> date ??
> We have tried to tune different values forth and back without any 100%
> result.
>
> --
>
> When I run some test methods from my IDE, haven't experienced this problem,
> and in debug mode I get these kind of feedback that's the Pool stuff seems
> to work.
>
> DEBUG 13:39:36,186 [main]
> com.ibatis.common.jdbc.SimpleDataSource - Checked out
> connection 32550604 from pool.
> DEBUG 13:39:36,186 [main]
> com.ibatis.common.jdbc.SimpleDataSource - Testing
> connection 32550604...
> DEBUG 13:39:36,196 [main]
> com.ibatis.common.jdbc.SimpleDataSource - Connection
> 32550604 is GOOD!
> .....
> DEBUG 13:39:36,266 [main]
> com.ibatis.common.jdbc.SimpleDataSource - Testing
> connection 32550604...
> DEBUG 13:39:36,266 [main]
> com.ibatis.common.jdbc.SimpleDataSource - Connection
> 32550604 is GOOD!
> DEBUG 13:39:36,266 [main]
> com.ibatis.common.jdbc.SimpleDataSource - Returned
> connection 32550604 to pool.
>
>
> Any clues anyone ? what we can do about this ?
>
>
> Thanks for any help!
>
>
> Best regards,
> Erlend Bj�rge
--
Brice Ruth
Software Engineer, Madison WI