After a long idle time, the connection pool fails to hand out connections

Posted on

We are running ReaHat EAP 6.4 and we have a number of JDBC connections to a MS SQL Server 2012 DBMS that work fine after startup and work fine while using the server all day long. It will continue to work fine the next day if someone was doing something on it the day before. Every morning we have a timer process that wakes up, accesses the database and sends out an email to the team about the health of the server. This is the process that fails if no one has been on the server the day before. It fails with the following exception:

05:30:09,609 ERROR [stderr] (Thread-135) java.sql.SQLException: Unable to obtain a connection from Database Access "java:comp/env/jdbc/NapersoftRepository".Initial Context lookup failed for java:comp/env/jdbc/NapersoftRepository.
05:30:09,609 ERROR [stderr] (Thread-135) at com.napersoft.databases.R80.NIDatabaseAccessManager.initialize(Unknown Source)
05:30:09,609 ERROR [stderr] (Thread-135) at com.napersoft.databases.R80.NIDatabaseAccessManager.getConnectionToCurrentInternalRepository(Unknown Source)
05:30:09,609 ERROR [stderr] (Thread-135) at com.napersoft.dataformat.NIDataLookUpTable.initialize(Unknown Source)
05:30:09,609 ERROR [stderr] (Thread-135) at com.napersoft.dataformat.framework.impl.NIDevLookUpTable.load(Unknown Source)
05:30:09,609 ERROR [stderr] (Thread-135) at com.napersoft.demo.filesend.NapersoftCCMFileSendPlugin.checkJobClassMonitorName(Unknown Source)
05:30:09,609 ERROR [stderr] (Thread-135) at com.napersoft.demo.filesend.NapersoftCCMFileSendPlugin.processRequest(Unknown Source)
05:30:09,609 ERROR [stderr] (Thread-135) at com.napersoft.demo.filesend.NapersoftCCMFileSendPlugin.handleJCMBegin(Unknown Source)
05:30:09,609 ERROR [stderr] (Thread-135) at com.napersoft.distribution.NIDistributionScheduledJCM.run(Unknown Source)
05:30:09,609 ERROR [stderr] (Thread-135) at java.lang.Thread.run(Thread.java:745)

I added some code to re-try getting the connection with a small wait in between each try hoping it was some stale connection pool issue, but every attempt results in the same error. We have to restart the Server in order to get things back to normal.

Here is the database entry in the standalone-full.xml:

            <xa-datasource jndi-name="java:/NapersoftRepository" pool-name="NapersoftRepository" enabled="true" use-ccm="true">
                <xa-datasource-property name="ServerName">
                    qa-w64-sql2012
                </xa-datasource-property>
                <xa-datasource-property name="DatabaseName">
                    NapersoftRepository_CCM8
                </xa-datasource-property>
                <xa-datasource-property name="SelectMethod">
                    cursor
                </xa-datasource-property>
                <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>
                <driver>mssql</driver>
                <new-connection-sql>select 1</new-connection-sql>
                <transaction-isolation>TRANSACTION_SERIALIZABLE</transaction-isolation>
                <xa-pool>
                    <min-pool-size>5</min-pool-size>
                    <max-pool-size>50</max-pool-size>
                    <is-same-rm-override>false</is-same-rm-override>
                    <interleaving>false</interleaving>
                    <pad-xid>false</pad-xid>
                    <wrap-xa-resource>false</wrap-xa-resource>
                </xa-pool>
                <security>
                    <security-domain>NapersoftCCMEncryptedPassword</security-domain>
                </security>
                <validation>
                    <check-valid-connection-sql>select 1</check-valid-connection-sql>
                    <validate-on-match>false</validate-on-match>
                    <background-validation>false</background-validation>
                </validation>
                <timeout>
                    <set-tx-query-timeout>false</set-tx-query-timeout>
                    <blocking-timeout-millis>5000</blocking-timeout-millis>
                    <idle-timeout-minutes>15</idle-timeout-minutes>
                    <query-timeout>0</query-timeout>
                    <use-try-lock>0</use-try-lock>
                    <allocation-retry>5</allocation-retry>
                    <allocation-retry-wait-millis>5000</allocation-retry-wait-millis>
                    <xa-resource-timeout>0</xa-resource-timeout>
                </timeout>
                <statement>
                    <share-prepared-statements>false</share-prepared-statements>
                </statement>
            </xa-datasource>

At this point we have no idea why the connection pool seems to go bad after something like 12 - 16 hours of not being used.

TG

Responses