Why does JBMS loose connection to the database after a long period of time

versions: 1.0M4

Explanation

The datasource configurations deployed with JBMS do not specify a maximum, minimum pool size, connection life time or implement connection checking.  While this is proabably okay for small servers and some setups, you will probably want to constrain this and add connection checking for larger production systems.

PostgreSQL example

Add this

    <new-connection-sql>select 1</new-connection-sql>
    <check-valid-connection-sql>select 1</check-valid-connection-sql>
    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>10</idle-timeout-minutes>

below the password.

Note that there is no connection ping command in the postgresql jdbc driver or backend protocol handling.  In MySQL and Oracle this constitues a NOOP command and ACKnoweldgement from the backend that the command was received which is lighter weight than issuing any sql query no matter how light.  This is to say that connection checking in PostgreSQL is heavierweight than in the other databases.

MySQL example

Add this

    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>10</idle-timeout-minutes>
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>     
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>

below the password.

Oracle example

Add this:

    <min-pool-size>5</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <idle-timeout-minutes>10</idle-timeout-minutes>
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>     
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>

below the password.