Connection closed problem after DB restart.

[mcevikce]

It looks like JBoss datasource does not handle DB restarts well. We are getting Connection closed errors after the DB restarts. Is there a way for datasource to handle DB restarts?

[weston.price]

How about reading this

http://www.jboss.com/index.html?module=bb&op=viewtopic&t=59031

and

this

http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossHelp

And then try posting again.

[reza0112]

Hi!

I have a similar problem. I have deployed my app on jboss-4.0.3SP1, it connects via oracle-ds.xml to an oracle9i db. Everything works fine, until i restart the db. At this point i get an SQLException. Looks like the following lines:

java.sql.SQLException: Closed Connection
at oracle.jdbc.driver.DatabaseError.throwSqlException
at oracle.jdbc.driver.DatabaseError.throwSqlException
at oracle.jdbc.driver.DatabaseError.throwSqlException
at oracle.jdbc.driver.PhysicalConnection.getMetaData
etc...

the oracle-ds.xml looks like that


<local-tx-datasource>
<jndi-name>MYDB</jndi-name>
<connection-url>jdbc:oracle:thin:@XX.XX:XXXX:XX</connection-url>

<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>XX</user-name>
XXX
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>

<type-mapping>Oracle9i</type-mapping>

</local-tx-datasource>

may somebody know something about this matter

[vickyk]

Look at this
http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossHelp
And if you look carefully you would be taken to this
http://www.jboss.org/wiki/Wiki.jsp?page=FAQJBossJCA
And if you don't get lazy you can further get the answer here
http://wiki.jboss.org/wiki/Wiki.jsp?page=WhatHappensToJBossIfIRestartTheDatabase

[jackminder]

I have read all FAQ pages regarding a restart of the Database. However I am not 100% sure if I should still see errors like below in the server.log for a length of time (ex. 8hrs)? Before when restarting the DB the J2EE app would just fail but now new entries are created in db and application is fine. My assumption is due to opening Min 50 connections in the pool it takes time for the connection checker to close all dead connections since this is a Dev environment and not all connections are actually used.

In the future could I modify the transaction-service.xml and turn debugging off to eliminate these errors?

Serverlog Errors:
SQLException occurred: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ))

My JCA config
<!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
 <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>

 <!-- Checks the Oracle error codes and messages for fatal errors -->
 <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>

 <!-- sql to call when connection is created -->
 <new-connection-sql>select 1 from dual</new-connection-sql>


 <!-- sql to call on an existing pooled connection when it is obtained from pool - the OracleValidConnectionChecker is prefered -->
 <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

 <user-name>******</user-name>
 <password>*****</password>
 <min-pool-size>50</min-pool-size>
 <max-pool-size>50</max-pool-size>
 <idle-timeout-minutes>5</idle-timeout-minutes>
 <blocking-timeout-millis>30000</blocking-timeout-millis>

[reza0112]

Hi vickyk!

Thank you for your fast reply.

I have read the Documentation and i already had set the
<valid-connection-checker-class-name> tag in my oracle-ds.xml File, i use the org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
Class.

Unfortunately it doesn´t work on one machine.
So I have copied the jboss to a another machine, there the same jboss is able to reconnect after db restart. So maybe it´s a problem outside jboss (And yes, I know this is a JBoss Froum).

May somebody have or had the same issues?

[reza0112]

@jackminder

I just have
<min-pool-size>5</min-pool-size>

And the app really can´t connect to db...

[vickyk]

"jackminder" wrote:

In the future could I modify the transaction-service.xml and turn debugging off to eliminate these errors?

Serverlog Errors:
SQLException occurred: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 30000 [ms] ))

No , you don't need to play with the transaction-service.xml file .
The error what you are seeing is explained here
http://wiki.jboss.org/wiki/Wiki.jsp?page=WhatDoesTheMessageNoManagedConnectionsAvailableMean

Here are the possibilities :
1) Leaked Connections .
2) Long Running genuine SQL's .

You can check the leaked connections from the CachedConnectionManagerMbean's listInUseConnection from the jmx-console .

To fix it you need to take care of the leaked connections which should be done by fixing the application code .

[jackminder]

I never see these errors during normal operation just when the oracle 10g RAC database is restarted. So I would not think this would be a connection leak. I guess I could increate the blocking timeout. Any recommendations for this?

Changing the app code will be a challenge since it is vendor based software.

Is the JMX-Console provided in Jboss 3.2.2?

Thank You

[vickyk]

"jackminder" wrote:
I never see these errors during normal operation just when the oracle 10g RAC database is restarted. So I would not think this would be a connection leak. I guess I could increate the blocking timeout. Any recommendations for this?

Increasing the blocking timeout means that you are sure that the connections are held for longer time in your application code .
That means that you have some genuine long ruinning SQL's which can't be optimized anymore and the server peak load is consuming all the connections .
The first things you should try is to increase the pool size and if this can't be don't then you can go and increase the blocking time .

Is the JMX-Console provided in Jboss 3.2.2?

Yes it is there , don't feel lazy to run http://localhost:8080/jmx-console when Jboss3.2.2 is running .
If you get lazy in starting the Jboss3.2.2 then look for the jmx-console folder in the $JBOSS_HOME/server/default/deploy/jmx-console.war :)

Finally think of doing SQL tunining , I guess you need it .

[jackminder]

Sorry the jmx console was removed from deployment. I can access it now but JBOSS 3.2.2 does not provide "listInUseConnections".

I found this statement when searching the FAQs
From 3.2.6 there is a "listInUseConnections" on the CachedConnectionManager.

I will research other methods in finding if I have leaked connections.

Thanks VickyK for all of your great input thus far!