Common mistakes to avoid in WSO2 Carbon - 2 - "java.sql.SQLException: Total number of available connections are less than the total number of closed connections"

This is the second post of common mistakes blog series which I'm planning to share with you. In this post, we are looking into another common mistake which we do when working with WSO2 Carbon platform.

Registry mounting is a way of federating the registry space across multiple servers in a product cluster. For example, if you have a WSO2 ESB cluster, you can use a single registry space to store all configuration data common to cluster nodes.
There are 3 different registry spaces provided by each WSO2 Carbon product; local, configuration and governance. You can find more details about these spaces in here.

We have to keep in mind a few important concepts when building a shared registry setup. You cannot share the local registry space among multiple cluster nodes. The local registry space is used to store node-specific data hence it should not be shared among other nodes in the cluster. However, we mistakenly do this when configuring shared registry setups and experience many unexpected issues. The following weird startup error is one such occurrence due to incorrect mounting configurations (I removed some part of the complete stack trace for clarity).

ERROR - RegistryCoreServiceComponent Failed to activate Registry Core bundle
org.wso2.carbon.registry.core.exceptions.RegistryException: Failed to close transaction.
    at org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCTransactionManager.endTransaction(JDBCTransactionManager.java:183)
--   
Caused by: java.sql.SQLException: Total number of available connections are less than the total number of closed connections
    at org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCDatabaseTransaction$ManagedRegistryConnection.close(JDBCDatabaseTransaction.java:1349)
    at org.wso2.carbon.registry.core.jdbc.dataaccess.JDBCTransactionManager.endTransaction(JDBCTransactionManager.java:178)

This error does not give any clue about a problem related to mounting. You may have spent many hours unnecessarily to tune up your DBMS since the error says about DB connections! 

Let's explore this error in detail.

Step 1

 

We are going to have a shared registry database (which is used as configuration and governance registry spaces in a ESB cluster). I will use mySQL and create a database first.

mysql> create database sharedreg_db;

Next, create the registry DB schema using mySQL database scripts available in CARBON_HOME/dbscripts directory.

mysql> use sharedreg_db;
mysql> source /home/charitha/products/esb/tmp/wso2esb-4.8.1/dbscripts/mysql.sql;

Step 2

 

We will register this new database in master-datasources.xml which can be found at
CARBON_HOME/repository/conf/datasources directory

<datasource>
            <name>WSO2_SHARED_REGISTRY_DB</name>
            <description>The datasource used for shared registry</description>
            <jndiConfig>
                <name>jdbc/WSO2SharedRegDB</name>
            </jndiConfig>
            <definition type="RDBMS">
                <configuration>
                    <url>jdbc:mysql://localhost:3306/sharedreg_db</url>
                    <username>root</username>
                    <password>root</password>
                    <driverClassName>com.mysql.jdbc.Driver</driverClassName>
                    <maxActive>50</maxActive>
                    <maxWait>60000</maxWait>
                    <testOnBorrow>true</testOnBorrow>
                    <validationQuery>SELECT 1</validationQuery>
                    <validationInterval>30000</validationInterval>
                </configuration>
            </definition>
        </datasource>

Step 3

 

Now, we have a shared registry database. We need to mount the registry collections to this remote database. There are 3 mounting mechanisms; JDBC, Atom and WS. The commonly used pattern is JDBC mounting. Hence I will use the same.
Mounting configuration can be done in CARBON_HOME/repository/conf/registry.xml as shown below.

<remoteInstance url="https://localhost:9443/registry">

        <id>instanceid</id>

        <dbConfig>wso2registry</dbConfig>

        <readOnly>false</readOnly>

        <enableCache>true</enableCache>

        <registryRoot>/</registryRoot>

    </remoteInstance>

    <mount path="/_system/config" overwrite="true">

        <instanceId>instanceid</instanceId>

        <targetPath>/_system/nodes</targetPath>

    </mount>
    <mount path="/_system/governance" overwrite="true">

        <instanceId>instanceid</instanceId>

        <targetPath>/_system/governance</targetPath>

    </mount>

Make a note of the highlighted configuration parameter.

Now, the database configuration referred by the mount is defined at the top of registry.xml as follows.
We simply change the JNDI name of the default db config; jdbc/WSO2CarbonDB to the JNDI name of our shared registry database; jdbc/WSO2SharedRegDB

 <currentDBConfig>wso2registry</currentDBConfig>

    <readOnly>false</readOnly>

    <enableCache>true</enableCache>

    <registryRoot>/</registryRoot>


    <dbConfig name="wso2registry">

        <dataSource>jdbc/WSO2SharedRegDB</dataSource>

    </dbConfig>

OK. Assuming everything is configured correctly, we start Carbon server. Unfortunately, you will get the above meaningless error at the server startup.

What is wrong here?

 

By defining a common data source for mount configuration as well as local registry definition (under currentDBConfig element), we have done a big mistake. This will eventually leads to share local registry space among heterogeneous product cluster nodes which is theoretically incorrect.  

How can we fix this?

 

Simple. You can define a separate, unique database configuration for the shared registry db.

<dbConfig name="sharedregistry">

        <dataSource>jdbc/WSO2SharedRegDB</dataSource>

    </dbConfig>

Then, that will be referenced by the remote mounting configuration.

<remoteInstance url="https://localhost:9443/registry">

        <id>instanceid</id>

        <dbConfig>sharedregistry</dbConfig>

        <readOnly>false</readOnly>

        <enableCache>true</enableCache>

        <registryRoot>/</registryRoot>

    </remoteInstance>

Finally, make sure to change the local registry definition back to its default so that it will use the WSO2 Carbon DB (usually H2).

<dbConfig name="wso2registry">

        <dataSource>jdbc/WSO2CarbonDB</dataSource>

    </dbConfig>

Restart the server. The error will disappear!