3 Replies Latest reply on Jan 6, 2017 7:12 AM by Anup Dey

    java.sql.SQLException: found null connection context

    Poonam Bhatia Newbie

      Hi All,

       

      We are working on a project where we have migrated our application from websphere 8.5 to JBoss EAP 6.4. We are running a BVT test and we running into an issue mentioned above java.sql.SQLException: found null connection context. The observation is JBoss is not releasing/purging the connections.

       

      Below is my datasource snippet in standalone-full.xml

       

      <xa-datasource jndi-name="java:/jdbc/Pool" pool-name="ec" enabled="true">

      <xa-datasource-property name="URL">

      XXX

      </xa-datasource-property>

      <xa-datasource-property name="User"> XXX </xa-datasource-property>

      <xa-datasource-property name="Password"> XXX </xa-datasource-property>

      <driver>oracle</driver>

      <xa-pool>

      <min-pool-size>1</min-pool-size>

      <max-pool-size>10</max-pool-size>

      <is-same-rm-override>false</is-same-rm-override>

      <no-tx-separate-pools>true</no-tx-separate-pools>

      <flush-strategy>IdleConnections</flush-strategy>

        </xa-pool>

        <timeout>

        <idle-timeout-minutes>1</idle-timeout-minutes>

        </timeout>

      </xa-datasource>

       

      May I request someone to advise on this.

        • 1. Re: java.sql.SQLException: found null connection context
          Anup Dey Apprentice

          Hi,

           

          ~~~

          java.sql.SQLException: found null connection context

          ~~~

          Cause: The connection context variable used in an executable SQL statement was null.

           

          Action: Verify that the connection context variable used in the offending executable SQL statement has been properly initialized. If the offending SQL statement was using an implicit connection context, verify that the default connection context was properly created an installed using the setDefaultContextmethod.

           

          1.The datasource connection pool has not been tuned correctly for the maximum load of the application. Either the pool size needs to be increased, or the blocking timeout extended, or both.

          2.The application is leaking connections because it is not closing them and returning them to the pool.

          3.Threads with connections to the database are hanging and holding on to the connections.

          4. Datasource connection Pool exhaustion.

           

          Below are some suggestion you have to implement:

          1# It is highly recommended that to use below datasource connection validation mechanisms:

          ~~~

          <validation>

                <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>

                <validate-on-match>true</validate-on-match>

                <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>

              </validation>

          ~~~

           

          2# use-fast-fail = true (immediately get a new physical connection)

          use-fast-fail determines whether or not to continue to attempt to acquire a connection from the pool even if the nth attempt has failed. If false, JBoss will move onto the next connection in the pool after "allocation-retry" has been reached. If true, JBoss will assume that all connections in the pool are invalid after the first connection exceeds the "allocation-retry".

           

          and

          ~~~

          <xa-pool>

                ...

               <is-same-rm-override>false</is-same-rm-override>

                <no-tx-separate-pools />

          </xa-pool>

          ~~~

           

          3#

          One part of the configuration that may be important in this regard is verifying that the database server side timeout value is *larger* than the timeout period in JBoss (allowing JBoss to gracefully timeout connections rather than setting up the circumstance where connections in the pool may be externally invalidated).

           

           

          The validate-on-match configuration is recommended for production systems. However, I would also (in addition to validation) recommend making sure the database timeout period is larger than the JBoss timeout as detailed in. This will reduce the cost of validation (when connections have been timed out) which will improve performance of connection requests.

           

          You have configured idle-timeout-minutes below:

           

          ~~~

          <idle-timeout-minutes>1</idle-timeout-minutes>

          ~~~

           

          This is idle connection exist time, JBoss default is **30 minutes** . You need to increase it as per requirement.  So, I don't have a specific recommendation as this is very much a customer/system specific preference. I have seen use 5 minutes, 30 minutes or even 1 minute. It varies widely with customer/system. You need to increase it.

           

          4# <max-pool-size>10</max-pool-size>

           

          If it is possible that you may have more than 10 users concurrently working in the system, you may need to increase the max-pool-size to allow for a greater number of users.

           

          If the maximum number of concurrent users is expected to be less than 10, it may be that there is some connection leak.

           

          Try these above configuration changes and lets know the outcomes

          • 2. Re: java.sql.SQLException: found null connection context
            Poonam Bhatia Newbie

            Thank you Anup for your valuable answer. We tried the configuration changes that were suggested but still no success.

             

            Our application is closing connections correctly and it works fine on websphere.

             

            Below are the changes implemented

            <xa-datasource jndi-name="java:/jdbc/Pool" pool-name="ec" enabled="true">

                                <xa-datasource-property name="URL">

                                    XXX

                                </xa-datasource-property>

                                <xa-datasource-property name="User">

                                    XXX

                                </xa-datasource-property>

                                <xa-datasource-property name="Password">

                                    XXX

                                </xa-datasource-property>

                                <driver>oracle</driver>

                                <xa-pool>

                                <min-pool-size>1</min-pool-size>

                                <max-pool-size>100</max-pool-size>

                                    <is-same-rm-override>false</is-same-rm-override>

                                    <no-tx-separate-pools>true</no-tx-separate-pools>

                                      <flush-strategy>IdleConnections</flush-strategy>

                                </xa-pool>

                                 <timeout>

                                <idle-timeout-minutes>15</idle-timeout-minutes>                   

                                </timeout>

                                <validation>

                                <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>

                               <validate-on-match>true</validate-on-match>

                              <background-validation>false</background-validation>

                              <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>

                              <use-fast-fail>true</use-fast-fail>

                          </validation>

                </xa-datasource>

             

            Please help!

             

            Also on monitoring the connections we get below statistics. DestroyedCount is 0.

             

            {"ActiveCount" : "10", "AvailableCount" : "0", "AverageBlockingTime" : "0", "AverageCreationTime" : "363", "CreatedCount" : "10", "DestroyedCount" : "0", "InUseCount" : "10", "MaxCreationTime" : "829", "MaxUsedCount" : "10", "MaxWaitCount" : "3", "MaxWaitTime" : "0", "TimedOut" : "0", "TotalBlockingTime" : "0", "TotalCreationTime" : "3634", "statistics-enabled" : true}

            • 3. Re: java.sql.SQLException: found null connection context
              Anup Dey Apprentice

              Hi,

               

              ActiveCount:

              The number of connections that are currently active. This includes connections that are InUse (see below) and connections that are available to be provided in the event of a new getConnection() request.

               

              AvailableCount:

              The number of connections that can currently be handed out.

               

              CreatedCount:

              The number of physical connections created.

               

              DestroyedCount:

              The number of physical connections destroyed.

               

              InUseCount

              The number of physical connections that are reserved by application components which have executed a getConnection() request but not yet returned the connection to the pool by calling close(). These may or may not be actively accessing the database but they are not available to be returned by new getConnection() requests. This count may include connections that have been leaked by application code which fails to close (i.e. return to the pool) connections which are no longer needed.

               

              Please make the below configuration changes:

               

              ~~~

              <xa-pool>

                    ...

                    <no-tx-separate-pools />

              </xa-pool>

              ~~~

               

              I would also recommend you to check if there are any connection leak in your application. You can verify that by enabling CCM for datasource. CCM is used to identify any connection leak in connection pool and is used to debug connections. You can enable CCM by using below configuration in DS:

               

               

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

              <subsystem xmlns="urn:jboss:domain:datasources:1.1">

                     <datasources>

                        <xa-datasource ... enabled="true" use-ccm="true">

                           ...

                        </datasource>

                     </datasources>

                  </subsystem>

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

               

              and

               

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

              <subsystem xmlns="urn:jboss:domain:jca:1.1">

                        ...

                        <cached-connection-manager debug="true" error="true"/>

                        ...

                     </subsystem>

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

               

              - The logs will show any connections leaks, but if you need help analyzing them, you can attach the resulting server.log file.

               

              - It is strongly recommended that, *application code* must handle exceptions that arise while a connection is held (InUse) by the application component.

                 

              - Upon completion of connection usage (even if the connection has failed), the application code must explicitly call "Connection.close()" to return the connection to the pool in order to prevent a connection leak.