01 May How to fix closed connections between WSO2 ESB Analytics and a Postgresql cluster
When connecting WSO2 ESB Analytics with a Postgresql cluster that performed connection pooling, we encountered the following exceptions in the wso2carbon.log file:
TID: [-1] [] [2017-02-10 18:31:46,289] ERROR {org.wso2.carbon.analytics.dataservice.core.indexing.IndexNodeCoordinator} – Error in processing staging index data: Error in tableExists: This connection has been closed. {org.wso2.carbon.analytics.dataservice.core.indexing.IndexNodeCoordinator}
org.wso2.carbon.analytics.datasource.commons.exception.AnalyticsException: Error in tableExists: This connection has been closed.
at org.wso2.carbon.analytics.datasource.rdbms.RDBMSAnalyticsRecordStore.tableExists(RDBMSAnalyticsRecordStore.java:311)
at org.wso2.carbon.analytics.datasource.rdbms.RDBMSAnalyticsRecordStore.get(RDBMSAnalyticsRecordStore.java:340)
at org.wso2.carbon.analytics.dataservice.core.indexing.StagingIndexDataStore.loadEntries(StagingIndexDataStore.java:113)
at org.wso2.carbon.analytics.dataservice.core.indexing.IndexNodeCoordinator$StagingDataIndexWorker.run(IndexNodeCoordinator.java:994)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
at org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:803)
at org.postgresql.jdbc.PgConnection.setAutoCommit(PgConnection.java:750)
at sun.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
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.$Proxy17.setAutoCommit(Unknown Source)
at org.wso2.carbon.analytics.datasource.rdbms.RDBMSAnalyticsRecordStore.getConnection(RDBMSAnalyticsRecordStore.java:130)
at org.wso2.carbon.analytics.datasource.rdbms.RDBMSAnalyticsRecordStore.getConnection(RDBMSAnalyticsRecordStore.java:125)
at org.wso2.carbon.analytics.datasource.rdbms.RDBMSAnalyticsRecordStore.tableExists(RDBMSAnalyticsRecordStore.java:308)
… 6 more
This issue was related to the Postgresql database connection pooling mechanism, that invalidated connections in the WSO2 ESB Analitics DB pool. To fix this problem, we reconfigured the WSO2 ESB Analytics pool as follows in analytics-datasources.xml:
<datasource>
<name>WSO2_ANALYTICS_EVENT_STORE_DB</name>
<description>The datasource used for analytics record store</description>
<definition type="RDBMS">
<configuration>
<url>jdbc:postgresql://<remote server name>:5432/WSO2_ESB_ANALYTICS_EVENT_STORE_DB</url>
<username>xxxx</username>
<password>xxxx</password>
<driverClassName>org.postgresql.Driver</driverClassName>
<maxActive>50</maxActive>
<maxWait>60000</maxWait>
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1</validationQuery>
<validationInterval>0</validationInterval>
<defaultAutoCommit>false</defaultAutoCommit>
<initialSize>0</initialSize>
<minEvictableIdleTimeMillis>4000</minEvictableIdleTimeMillis>
<defaultTransactionIsolation>READ_COMMITTED</defaultTransactionIsolation>
</configuration>
</definition>
</datasource>
<datasource>
<name>WSO2_ANALYTICS_PROCESSED_DATA_STORE_DB</name>
<description>The datasource used for analytics record store</description>
<definition type="RDBMS">
<configuration>
<url>jdbc:postgresql://<remote server name>:5432/WSO2_ESB_ANALYTICS_PROCESSED_DATA_STORE_DB</url>
<username>xxxx</username>
<password>xxxx</password>
<driverClassName>org.postgresql.Driver</driverClassName>
<maxActive>50</maxActive>
<maxWait>60000</maxWait>
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1</validationQuery>
<validationInterval>0</validationInterval>
<defaultAutoCommit>false</defaultAutoCommit>
<initialSize>0</initialSize>
<minEvictableIdleTimeMillis>4000</minEvictableIdleTimeMillis>
<defaultTransactionIsolation>READ_COMMITTED</defaultTransactionIsolation>
</configuration>
</definition>
</datasource>
The essence of this solution are these 3 parameters:
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1</validationQuery>
<validationInterval>0</validationInterval>
By adding these 3 parameters, each connection taken from the pool will be validated first, before being used by the WSO2 ESB Analytics engine. Make sure you set the “validationInterval” to 0, otherwise the validation will be scheduled (the default value is 3 seconds) and the above exception can continue to appear. More details on each of these parameters can be found at https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html.
After applying this configuration change, WSO2 ESB Analytics worked fine and the above exception no longer appeared in the wso2carbon.log.
Sorry, the comment form is closed at this time.