multiple datasource

[Alessandro Ilardo]

Hi there,
I'm new with Jboss and I'm trying to understand how to configure multiple datasource based on different databases. I've already gone trough the jboss.com docs, but onestly it didn't help me much.
I'm now working with JBoss 4.0.5 and MySQL 5 (but I'd like implement Oracle too).

Following this tutorial this tutorial I've been able to run my entity bean, however I had to replace the DefaultDS hsqldb with MySQL and this is not exactly what I wanted to achieve.
So, I'm probably missing something. Would you have a look on what I did and tell me if I need further configurations?

add the mysql driver

replaced the hsqldb-ds.xml with the new edited mysql-ds.xml

<datasources>
  <local-tx-datasource>
    <jndi-name>DefaultDS</jndi-name>
    <connection-url>jdbc:mysql://127.0.0.2:3306/tmp</connection-url>
    <driver-class>org.gjt.mm.mysql.Driver</driver-class>
    <user-name>user</user-name>
    <password>temp</password>
    <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
    <metadata>
       <type-mapping>mySQL</type-mapping>
    </metadata>
  </local-tx-datasource>
</datasources>

replaced the ../jms/hsqldb-jdbc2-service.xml with ../jms/mysql-jdbc2-service.xml

<server>
  <mbean code="org.jboss.mq.server.jmx.DestinationManager" name="jboss.mq:service=DestinationManager">
    <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
    <depends optional-attribute-name="PersistenceManager">jboss.mq:service=PersistenceManager</depends>
    <depends optional-attribute-name="StateManager">jboss.mq:service=StateManager</depends>
  </mbean>
  <mbean code="org.jboss.mq.server.MessageCache"
 name="jboss.mq:service=MessageCache">
    <attribute name="HighMemoryMark">50</attribute>
    <attribute name="MaxMemoryMark">60</attribute>
    <attribute name="CacheStore">jboss.mq:service=PersistenceManager</attribute>
  </mbean>
  <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
 name="jboss.mq:service=PersistenceManager">
    <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
    <attribute name="SqlProperties">
...
...

modified the ejb-jar.xml in my module

<resource-ref>
                <description>jdbc:mysql://127.0.0.2:3306/tmp</description>
                <res-ref-name>jdbc/DefaultDS</res-ref-name>
                <res-type>javax.sql.DataSource</res-type>
                <res-auth>Container</res-auth>
                <res-sharing-scope>Shareable</res-sharing-scope>
            </resource-ref>

edit the entity bean method

private javax.sql.DataSource getSavingsAccountDB() throws javax.naming.NamingException {
        javax.naming.Context c = new javax.naming.InitialContext();
        return (javax.sql.DataSource) c.lookup("java:comp/env/jdbc/DefaultDS");
    }

So, as I sayd the entity bean correctly works, but on JBoss startup I now get an error

19:12:20,488 ERROR [URLDeploymentScanner] Incomplete Deployment listing:

--- MBeans waiting for other MBeans ---
ObjectName: jboss.mq:service=InvocationLayer,type=HTTP
State: CONFIGURED
I Depend On:
jboss.mq:service=Invoker
jboss.web:service=WebServer

ObjectName: jboss.mq.destination:service=Topic,name=testTopic
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager
jboss.mq:service=SecurityManager

ObjectName: jboss.mq.destination:service=Topic,name=securedTopic
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager
jboss.mq:service=SecurityManager

ObjectName: jboss.mq.destination:service=Topic,name=testDurableTopic
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager
jboss.mq:service=SecurityManager

ObjectName: jboss.mq.destination:service=Queue,name=testQueue
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager
jboss.mq:service=SecurityManager

ObjectName: jboss.mq.destination:service=Queue,name=A
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager

ObjectName: jboss.mq.destination:service=Queue,name=B
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager

ObjectName: jboss.mq.destination:service=Queue,name=C
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager

ObjectName: jboss.mq.destination:service=Queue,name=D
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager

ObjectName: jboss.mq.destination:service=Queue,name=ex
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager

ObjectName: jboss.mq:service=Invoker
State: CONFIGURED
I Depend On:
jboss.mq:service=TracingInterceptor
jboss:service=Naming
Depends On Me:
jboss.mq:service=InvocationLayer,type=HTTP
jboss.mq:service=InvocationLayer,type=JVM
jboss.mq:service=InvocationLayer,type=UIL2

ObjectName: jboss.mq:service=TracingInterceptor
State: CONFIGURED
I Depend On:
jboss.mq:service=SecurityManager
Depends On Me:
jboss.mq:service=Invoker

ObjectName: jboss.mq:service=SecurityManager
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager
Depends On Me:
jboss.mq.destination:service=Topic,name=testTopic
jboss.mq.destination:service=Topic,name=securedTopic
jboss.mq.destination:service=Topic,name=testDurableTopic
jboss.mq.destination:service=Queue,name=testQueue
jboss.mq:service=TracingInterceptor
jboss.mq.destination:service=Queue,name=DLQ

ObjectName: jboss.mq.destination:service=Queue,name=DLQ
State: CONFIGURED
I Depend On:
jboss.mq:service=DestinationManager
jboss.mq:service=SecurityManager

ObjectName: jboss.mq:service=InvocationLayer,type=JVM
State: CONFIGURED
I Depend On:
jboss.mq:service=Invoker

ObjectName: jboss.mq:service=InvocationLayer,type=UIL2
State: CONFIGURED
I Depend On:
jboss.mq:service=Invoker

--- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
ObjectName: jboss.mq:service=DestinationManager
State: NOTYETINSTALLED
Depends On Me:
jboss.mq.destination:service=Topic,name=testTopic
jboss.mq.destination:service=Topic,name=securedTopic
jboss.mq.destination:service=Topic,name=testDurableTopic
jboss.mq.destination:service=Queue,name=testQueue
jboss.mq.destination:service=Queue,name=A
jboss.mq.destination:service=Queue,name=B
jboss.mq.destination:service=Queue,name=C
jboss.mq.destination:service=Queue,name=D
jboss.mq.destination:service=Queue,name=ex
jboss.mq:service=SecurityManager
jboss.mq.destination:service=Queue,name=DLQ

I was not able to find the reason, but it started after I replaced the DefaultDS with MySQL.

Back to my configuration, how can I set up multiple datasource?

Thank you in advance.

[Kaustabh Singha Roy]

I am not sure I am understanding the problem properly. If you want to create a new xml file with the name XYZ-ds.xml and give it contents similar to those in the default ds file. Just make sure that the JNDI name is unique. There is no limit of how many data sources you can create like this.

Hope this is helpful for you.

[Mark Spritzler]

You issue is around the Destination Manager.

Here is what I noticed that is different between the jms' jdbc2-service.xml files. In the Hypersonic HSQLDB file it had two more "dependencies" than the MySQL version. But, since Hypersonic is an in memory database, I can see why they need a ThreadPool and lookup stuff in the JNDI tree, whereas in theory the MySQL database shouldn't need. Try adding them, just to see what happens.

<depends optional-attribute-name="ThreadPool">jboss.mq:service=ThreadPool</depends>
<depends>jboss:service=Naming</depends>

What did you change in your mysql-jdbc2-service.xml besides the DataSource Name to be DefaultDS?