10 Replies Latest reply on Nov 30, 2007 11:03 AM by Jack Saunders

    Connection closed problem after DB restart.

    Mike Cev Newbie

      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?

        • 2. Re: Connection closed problem after DB restart.
          Reza Anwar Newbie

          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

          • 4. Re: Connection closed problem after DB restart.
            Jack Saunders Newbie

            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>
            



            • 5. Re: Connection closed problem after DB restart.
              Reza Anwar Newbie

              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?


              • 6. Re: Connection closed problem after DB restart.
                Reza Anwar Newbie

                @jackminder

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

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

                • 7. Re: Connection closed problem after DB restart.
                  Vicky Kak Master

                   

                  "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 .



                  • 8. Re: Connection closed problem after DB restart.
                    Jack Saunders Newbie

                    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


                    • 9. Re: Connection closed problem after DB restart.
                      Vicky Kak Master

                       

                      "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 .


                      • 10. Re: Connection closed problem after DB restart.
                        Jack Saunders Newbie

                        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!