WSO2 product cluster with registry replication in Oracle

I recently notice an obvious mistake users doing when they configure WSO2 product cluster and use WSO2 G-Reg as the central repository for the replication among the cluster nodes. You can find a guide on how to set this up in an WSO2 Oxygen Tank tutorial [1] and there we are using Oracle as the database.

If you go through this you will see we are doing two registry mountings in all the cluster nodes, so every cluster node is having two database configuration, one for the local registry database configuration and one for the mount registry database configuration (configuration of the central registry database).

When it comes to Oracle, normally users are using a same database URL and differentiate among different databases by changing the user name and password. This works without any issue, but when it comes to mounting we are referring the URL of the database configuration and we expect you will put different URL values for two database configuration (local registry and config and governance mounting). So with Oracle users will simply not aware of this and use different username password and they get following error.

java.sql.SQLException: Total number of available connections are less than the total number of committed connections

at org.wso2.carbon.registry.core.jdbc.utils.Transaction$ManagedRegistryConnection.commit(Transaction.java:474)

at org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.commitTransaction(EmbeddedRegistry.java:403)

at org.wso2.carbon.registry.core.jdbc.EmbeddedRegistry.resourceExists(EmbeddedRegistry.java:594)

at org.wso2.carbon.registry.core.session.UserRegistry.resourceExists(UserRegistry.java:545)

at org.wso2.carbon.registry.core.internal.RegistryCoreServiceComponent.setupMounts(RegistryCoreServiceComponent.

java:250)

So this error comes when someone try to mount from it's own database, if you use a configuration like below obviously we are throwing this exception.

<dbConfig name="wso2registry">

<url>jdbc:oracle:thin@qcdvcn1001-vip.dev.rsft.net:15000:DEV541</url>

<userName>wso2_esb</userName>

<password>wso2_esb</password>

<driverName>oracle.jdbc.driver.OracleDriver</driverName>

<maxActive>50</maxActive>

<maxWait>60000</maxWait>

<minIdle>5</minIdle>

</dbConfig>

<dbConfig name="esbMountRegistry">

<url>jdbc:oracle:thin@qcdvcn1001-vip.dev.rsft.net:15000:DEV541</url>

<userName>wso2_greg</userName>

<password>wso2_greg</password>

<driverName>oracle.jdbc.driver.OracleDriver</driverName>

<maxActive>50</maxActive>

<maxWait>60000</maxWait>

<minIdle>5</minIdle>

</dbConfig>

You can see above two URL's are same but username password are different.

So the solution for this issue is differentiate the URL by giving username in the database URL before the "@" sign. So the dbconfig elements of the cluster node registry.xml will looks like this.

<dbConfig name="wso2registry">

<url>jdbc:oracle:thin:wso2_esb@qcdvcn1001-vip.dev.rsft.net:15000:DEV541</url>

<userName>wso2_esb</userName>

<password>wso2_esb</password>

<driverName>oracle.jdbc.driver.OracleDriver</driverName>

<maxActive>50</maxActive>

<maxWait>60000</maxWait>

<minIdle>5</minIdle>

</dbConfig>

<dbConfig name="esbMountRegistry">

<url>jdbc:oracle:thin:wso2_greg@qcdvcn1001-vip.dev.rsft.net:15000:DEV541</url>

<userName>wso2_greg</userName>

<password>wso2_greg</password>

<driverName>oracle.jdbc.driver.OracleDriver</driverName>

<maxActive>50</maxActive>

<maxWait>60000</maxWait>

<minIdle>5</minIdle>

</dbConfig>

[1]http://wso2.org/library/tutorials/2010/11/setup-wso2-esb-cluster-wso2-greg