Avoid Stale Connections to Database

0 Comments
In Apache Tomcat or WSO2 Application Server  you can configure a data source to connect to a database. When you consider WSO2 Middleware, any server is connected to a database using a datasource.

Sometimes the database instances has to be shut down for maintenance. Consider a situation where in a large deployment of WSO2 servers, database server is restarted. When database server is back, when servers connect back to the server you might see following error log

Caused by: java.sql.SQLRecoverableException: Closed Connection
        at oracle.jdbc.driver.PhysicalConnection.setAutoCommit(PhysicalConnection.java:3763)
        at sun.reflect.GeneratedMethodAccessor31.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
        at org.wso2.carbon.ndatasource.rdbms.ConnectionRollbackOnReturnInterceptor.invoke(ConnectionRollbackOnReturnInterceptor.java:51)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
        at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:71)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
        at org.apache.tomcat.jdbc.pool.interceptor.ConnectionState.invoke(ConnectionState.java:153)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
        at org.apache.tomcat.jdbc.pool.TrapException.invoke(TrapException.java:41)
        at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
        at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
        at com.sun.proxy.$Proxy14.setAutoCommit(Unknown Source)
        at org.wso2.carbon.identity.core.persistence.JDBCPersistenceManager.getDBConnection(JDBCPersistenceManager.java:137)

        ... 51 more


The main reason for this is not configuring the datasource correctly. At [WSO2_HOME]/repository/conf/datasources/masterdatasource.xml file following properties should be added to datasource for this to work. 

  1. Test On Borrow - (boolean) Used to indicate if objects will be validated before borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. In order to have a more efficient validation, see validationInterval. Default value is false.
  2. Validation Query - (String) The SQL query used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can't throw a SQLException. The default value is null. Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server).
  3. Validation Interval - (long) avoid excess validation, only run validation at most at this frequency - time in milliseconds. If a connection is due for validation, but has been validated previously within this interval, it will not be validated again. The default value is 30000 (30 seconds).

Following is an example configuration for Oracle: 

<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1 FROM DUAL</validationQuery>
<validationInterval>30000</validationInterval>





Hasitha Hiranya

No comments:

Post a Comment

Instagram