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

Post a Comment

Popular posts from this blog

WSO2 Stratos - Introducing WSO2 middleware Platform as a Service (PaaS)

Image
Yesterday we released our newest integrated cloud middleware platform, WSO2 Stratos . It is now online and ready for use! For the past few years, WSO2 has been doing many releases based on revolutionary Carbon platform , which helped to consistently improve the platform with introducing latest state-of-art technologies. The initial releases of Carbon product platform intended to improve the features of the individual products. For example, a lot of core features were introduced to WSO2 ESB, WSAS, Governance Registry etc.. Then we focused on ease of integration and more component based model. We introduced Equinox based provisioning model to build your SOA platform by picking and choosing the components as and when you need. Then, the core product platform has been enhanced to support multi-tenancy. With the multi-tenant supported architecture at hand, our brilliant development team was able to introduce the first ever comprehensive middleware PaaS. Why is this so important? Now, WSO2 C...
Read more

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

In the next few weeks, you could expect a series of blog posts from me which explain the remedies to avoid some common mistakes which we do when working with WSO2 ESB . This is the first of many. During the initial rounds of testing your integration solution or even in production systems, you may have come across the following error. org.apache.axis2.AxisFault: The system cannot infer the transport information from the URL .... Some of you may have struggled hours figuring out a solution to address this issue. Let me explain this error in detail and possible causes of this. So, next time when you see this error in your WSO2 ESB setup, you will not have to spend time unnecessarily googling everywhere. There can be many different causes of this error. I have observed  four main reasons. Cause 1 This is the simplest cause. Suppose, you have a proxy service similar to the following. <proxy xmlns="http://ws.apache.org/ns/synapse" name="MistakesTest...
Read more

Working with HTTP multipart requests in soapUI

Image
You can use HTTP request test step in soapUI to submit messages with various Content-Types.  In this post, we will have a quick look into the multipart/form-data requests in soapUI. multipart/form-data requests usually come into action when you do HTML form submissions with file attachments. For example, have a look at the following HTML form post. <form action="http://localhost:8090/CKFileUploadApp/UploadServlet" method="post" enctype="multipart/form-data"> <input type="file" name="file"> <input type="text" name="paramName"> <input type="submit" name="Submit" value="Upload File"> </form> In the forms like above, we have file upload option as a form input where we can upload a file along with the form submission. In that case, the request's HTTP content type will be multipart/form-data. How can we simulate such a request using soapUI...
Read more