Multiple applications connection problem

[mex]

We are attempting to run
-- two (almost identical) ears
-- on a single instance of JBoss [Zion] 4.0.5.GA
-- using two datasources
-- pointing to two different databases on the same database server.

What happens is that the ear deployed first determines the database used.
In other words, the second ear will connect to the same database as the first one.
Therefore entity beans of the second-deployed application are connected to the wrong database.

When run individually, each application connects to the database as defined in the relevant -ds file.

Details
There are no deployment errors

Both datasources are bound correctly to different JNDI-names and show up correctly in the JNDI-view under the java: Namespace.

The applications are separated in Global JNDI namespace.

Inspecting the log indicates that there is no classloading issue.
Static code sections work properly also indicate there is no problem with classloading.
Accessing the datasources directly from each application via InitialContext.lookup by JNDI also works.

We use two -ds files (putting both datasources into one file made no difference):

first-ds.xml
<datasources>

 <local-tx-datasource>
 <jndi-name>MySql_FIRST_DS</jndi-name>
 <connection-url>jdbc:mysql://ourHost:3306/firstSchema</connection-url>
 <driver-class>com.mysql.jdbc.Driver</driver-class>
 <user-name>ourName</user-name>
 <password>ourPassword</password>
 </local-tx-datasource>

</datasources>


second-ds.xml
<datasources>

 <local-tx-datasource>
 <jndi-name>MySqlDS</jndi-name>
 <connection-url>jdbc:mysql://ourHost:3306:3306/secondSchema</connection-url>
 <driver-class>com.mysql.jdbc.Driver</driver-class>
 <user-name>ourName</user-name>
 <password>ourPassword</password>
 </local-tx-datasource>

</datasources>

Any help appreciated. Thank you

[paulalbano]

You have to use "xa-datasource".
Only this type can work with two types of different database.
Try it!!

[mex]

Thanks for your reply.

I am struggling getting the xa-datasource to work.
Current state:



<xa-datasource>
<jndi-name>MySqlDS</jndi-name>
<xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
<xa-datasource-property name="Url">ourUrl/schema</xa-datasource-property>
<xa-datasource-property name="User">User</xa-datasource-property>
<xa-datasource-property name="Password">Password</xa-datasource-property>
</xa-datasource>


is giving me this kind of error:
2007-05-16 14:17:28,078 ERROR [org.jboss.resource.adapter.jdbc.xa.XAManagedConnectionFactory] End transaction failed for XAResource
com.mysql.jdbc.jdbc2.optional.MysqlXAException: XAER_INVAL: Invalid arguments (or unsupported command)
at com.mysql.jdbc.jdbc2.optional.MysqlXAConnection.mapXAExceptionFromSQLException(MysqlXAConnection.java:564)

[mex]

Adding

 <track-connection-by-tx>true</track-connection-by-tx>
 <new-connection-sql>set autocommit=1</new-connection-sql>
 <no-tx-separate-pools>true</no-tx-separate-pools>

made it for the xa-datasource.

But the problem remains.

It DOES appear to be a classloader isolation problem.

More specific there is a problem with static sections of classes with identical packages and ClassNames (but in different ears). I believe instances are correctly referenced via jndi.

I will try classloader separation next.

[mex]

Getting the classloading isolation correct solved the problem.

I defined a distinct loader-repository in a jboss-app.xml in the META-INF of each ear and set

<attribute name="Isolated">true</attribute>

in the ear-deployer.

This results in an application isolation as desired.
It is not necessary to use xa-datasources - but it also works.

The main problems along the way were to find which part of the application prevented the isolation (in my case it was the scheduler-plugin from outside of the scope of the loader-repository of the applications being referenced from a sar within the application). After sorting that out things were easy.


It was a bit of a surprise to me that the tomcat deployer works in a scoped way ensuring web applications are isolated.
This made me believe in the beginning there was no classloading issue.