WSO2 SOA middleware Deployment Tips - 1 - validationQuery to avoid broken DB connections

I thought to put together some best practices, guidelines on deploying WSO2 SOA middleware platform. This post will remind you a well-known best practice used in most of the product deployments.
When you are maintaining DB connections, it is always recommended to use a validationQuery to check the health of the TCP connection of the connections stay in DB connection pool.
Because the connection opening is an expensive and time consuming operation, after a connection is created, it will be kept open for a specific time in the pool. When re-using these connections from the pool, there can be situations that the TCP connection to the DB is interrupted and the connection consumer gets errors such as communication link failures etc..
In order to avoid that, a validationQuery, an SQL statement specific to DBMS type, can be used which runs before using the connection.

In WSO2 middleware platform, you usually use central governance/configuration registry to store SOA metadata and various governance tasks. Governance registry supports multiple DBs as underlying data stores. You establish the connection to DB in CARBON_HOME/repository/conf/registry.xml as follows.

<dbConfig name="wso2registry">
<url>jdbc:mysql://localhost:3306/config_db</url>
<userName>>regadmin</userName>
<password>regadmin</password>
<driverName>com.mysql.jdbc.Driver</driverName>
<maxActive>50</maxActive>
<maxWait>60000</maxWait>
<minIdle>5</minIdle>
</dbConfig>

This is one of the places where you may experience DB connection issues as I explained above. Therefore, it is always a best practice to use a validationQuery.

If your DBMSs is MySQL or MSSQL, then use the following.

<validationQuery>SELECT 1</validationQuery>

In Oracle;

<validationQuery>SELECT 1 FROM DUAL</validationQuery>

In Postgres,

<validationQuery>SELECT version(); </validationQuery>

Similarly, when you make DB connections in WSO2 Data Services Server, make sure to use validationQuery in data source definition section as follows.

<property name="org.wso2.ws.dataservice.validationquery">SELECT 1</property>

Comments

Popular posts from this blog

Common mistakes to avoid in WSO2 ESB - 1 - "org.apache.axis2.AxisFault: The system cannot infer the transport information from the URL"

Working with HTTP multipart requests in soapUI

How to deploy JSR181 annotated class in Apache Axis2