JBoss occasionally couldn't connect to database

Question

JBoss as7.1.1
MS SQL Server 2012

I have the below datasource setup in my JBoss and occasionally it has problem connecting to the db, it keeps on trying but couldn't connect.

<datasource jta="false" jndi-name="java:jboss/WebDataSource" pool-name="WebDataSource" enabled="true" use-ccm="false">
    <connection-url>jdbc:sqlserver://proddbserver:1433;DATABASENAME=proddb;sendStringParametersAsUnicode=false;</connection-url>
    <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>
    <driver>sqljdbc4.jar</driver>
    <new-connection-sql>SELECT 1</new-connection-sql>
    <pool>
        <min-pool-size>100</min-pool-size>
        <max-pool-size>300</max-pool-size>
        <prefill>false</prefill>
        <use-strict-min>false</use-strict-min>
        <flush-strategy>FailingConnectionOnly</flush-strategy>
    </pool>
    <security>
        <user-name>user</user-name>
        <password>pwd</password>
    </security>
    <validation>
        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
        <validate-on-match>true</validate-on-match>
        <background-validation>true</background-validation>
        <background-validation-millis>300000</background-validation-millis>
        <use-fast-fail>true</use-fast-fail>
    </validation>
    <statement>
        <share-prepared-statements>false</share-prepared-statements>
    </statement>
</datasource>

I tried the same setup in my local JBoss pointing to a test db, when there is no database connection, if i try to login to the portal, portal returns back an error message immediately, since it couldn't talk to the database, whereas, in production when I try to login to portal, it keeps on trying.

In my local environment, unplugged the network cable, tried to access a page having db connection, it failed to connect, then plugged back the network cable, accessed the page and could successfully retrieve data. Also tried by stopping the database, start it back and still could successfully access from the portal.

Restart of production JBoss fixes this problem. What could be the problem? Is there any other fix available without restart? I checked many SO posts related to this topic, and noticed there are few differences like using 'valid-connection-checker' instead of 'check-valid-connection-sql' and 'validate-on-match' not being required when 'background-validation' being true.

Another JBoss having same code base and datasource settings in a different windows server, which is load balanced, works perfectly fine.

Sound like some kind of cut-off connections. There is a firewall in production environment? It may cutting the connection but Jboss doesn't realize and keep trying to use it... and never gets a db server response. — Guillermo

SyAu SyAu · Accepted Answer · 2016-09-29T00:31:45

The server having problem connecting to db server are in different networks and this connectivity issue was caused by network bandwidth being clogged by other process. This connectivity issue was happening at a certain time and this gave some hint where the problem was. The program which is consuming most of the bandwidth was postponed to run in after hours and I believe this solved the problem.

At the same time, before doing the above fix, I also updated the 'datasource' settings to 1 minute from 5 minute,

<background-validation-millis>60000</background-validation-millis>

Not sure, if this helped in anyway. But I noticed, before this change, the JBoss db pool was filling up very quickly and JBoss crashed and after this datasource settings update that didn't happen.

Either of the fix could have solved the problem.

JBoss occasionally couldn't connect to database

1 Answers