11 Replies Latest reply on Aug 27, 2002 6:55 PM by mgao

    How can I configure more than one MySQL database?

    lucvu

      Hello everyone!

      I am currently using JBoss 3 + Tomcat 4.03 bundle. I got the MySQL datasource working for one database. How do I setup for more than one MySQL databases?

      Thanks!

      Luc

        • 1. Re: How can I configure more than one MySQL database?
          lucvu

          I have found the solution. If anyone interest in it then I will post it.

          Luc

          • 2. Re: How can I configure more than one MySQL database?
            j8055

            YES PLEASE!!! I have been trying to find the solution for about 3 days now. I'm looking to configure 2 SQL datasources. I'm guessing the same applies ...

            thx in advance
            J8055

            • 3. Re: How can I configure more than one MySQL database?
              lucvu

              Let me tell you what I did.

              I am using JBoss-3.0.0_tomcat-4.0.3. In the JBOSS_TOMCAT_INSTALL_DIR/docs/examples/jca there is a template for setting up mysql call "mysql-service.xml". [Don't use the latest version from SourceForge because it it has one line out of place so it doesn't work.] I made 2 copy of mysql-service.xml and name them as follow: mysqlDS1-service.xml and mysqlDS2-service.xml. You can name it them to whatever you want but they must follow with "-service.xml".

              In mysqlDS1-service.xml I modified the following:
              1) rename all MySqlDS to MySqlDS1
              2) modify jdbc:mysql://dell:3306/jbossdb to jdbc:mysql://localhost:3306/database1
              3) add username and password

              In mysqlDS2-service.xml I modified the following:
              1) rename all MySqlDS to MySqlDS2
              2) modify jdbc:mysql://dell:3306/jbossdb to jdbc:mysql://localhost:3306/database2
              3) add username and password

              Put both files in the default/deploy folder.

              Check to see if your datasource is up, goto http://localhost:8082 and click on service=JNDIView. Scroll down to the bottom and click on the list button. Goto the java:Namespace you should see MySqlDS1 and MySqlDS2.

              Notice that I added the username and password and I didn't use the MySqlDbRealm policy. I will try to use the MySqlDbRealm later.

              I hope this help.

              Luc

              • 4. Re: How can I configure more than one MySQL database?
                j8055

                Add another service ... how obvious is that!! Can't believe I never thought of it :)

                All working fine now. Thank you so much Luc!!

                J8055

                • 5. Re: How can I configure more than one MySQL database?
                  cake

                  I'm using JBoss3.0.1 with Jetty. I've followed the instructions to create 2 DS. However, I got the following error messages. Any idea? Thanks.

                  06:06:25,336 INFO [MainDeployer] Starting deployment of package: file:/C:/JBoss
                  /jboss-3.0.1/server/all/deploy/mysql-service2.xml
                  06:06:25,396 INFO [MainDeployer] deployment waiting for deployer: file:/C:/JBos
                  s/jboss-3.0.1/server/all/deploy/mysql-service2.xml
                  06:06:25,406 INFO [MainDeployer] Deployment of package: file:/C:/JBoss/jboss-3.
                  0.1/server/all/deploy/mysql-service2.xml is waiting for an appropriate deployer.

                  06:06:25,446 ERROR [URLDeploymentScanner] MBeanException: Exception in MBean ope
                  ration 'checkIncompleteDeployments()'
                  Cause: Incomplete Deployment listing:
                  Packages waiting for a deployer:
                  [org.jboss.deployment.DeploymentInfo@545fc348 { url=file:/C:/JBoss/jboss-3.0.1/s
                  erver/all/deploy/mysql-service2.xml }
                  deployer: null
                  status: null
                  state: INIT_WAITING_DEPLOYER
                  watch: file:/C:/JBoss/jboss-3.0.1/server/all/deploy/mysql-service2.xml
                  lastDeployed: 1030107985406
                  lastModified: 1030107985366
                  mbeans:
                  ]Incompletely deployed packages:
                  [org.jboss.deployment.DeploymentInfo@545fc348 { url=file:/C:/JBoss/jboss-3.0.1/s
                  erver/all/deploy/mysql-service2.xml }
                  deployer: null
                  status: null
                  state: INIT_WAITING_DEPLOYER
                  watch: file:/C:/JBoss/jboss-3.0.1/server/all/deploy/mysql-service2.xml
                  lastDeployed: 1030107985406
                  lastModified: 1030107985366
                  mbeans:
                  ]MBeans waiting for classes:

                  MBeans waiting for other MBeans:


                  06:06:25,616 INFO [URLDeploymentScanner] Started
                  06:06:25,626 INFO [MainDeployer] Deployed package: file:/C:/JBoss/jboss-3.0.1/s
                  erver/all/conf/jboss-service.xml
                  06:06:25,646 INFO [Server] JBoss (MX MicroKernel) [3.0.1 Date:200208062340] Sta
                  rted in 0m:52s:34ms

                  This is my 2nd mysql-service2.xml:

                  <?xml version="1.0" encoding="UTF-8"?>

                  <!-- ===================================================================== -->
                  <!-- -->
                  <!-- JBoss Server Configuration -->
                  <!-- -->
                  <!-- ===================================================================== -->



                  <!-- ==================================================================== -->
                  <!-- New ConnectionManager setup for mysql using 2.0.11 driver -->
                  <!-- Build jmx-api (build/build.sh all) and view for config documentation -->
                  <!-- ==================================================================== -->



                  <depends optional-attribute-name="ManagedConnectionFactoryName">
                  <!--embedded mbean-->


                  MySqlPKDS



                  <config-property name="ConnectionURL" type="java.lang.String">jdbc:mysql://localhost/pk</config-property>
                  <config-property name="DriverClass" type="java.lang.String">org.gjt.mm.mysql.Driver</config-property>
                  <!--set these only if you want only default logins, not through JAAS -->
                  <config-property name="UserName" type="java.lang.String">abcde</config-property>
                  <config-property name="Password" type="java.lang.String">12345</config-property>




                  <!--Below here are advanced properties -->
                  <!--hack-->
                  <depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper




                  <depends optional-attribute-name="ManagedConnectionPool">
                  <!--embedded mbean-->


                  0
                  50
                  5000
                  15
                  <!--criteria indicates if Subject (from security domain) or app supplied
                  parameters (such as from getConnection(user, pw)) are used to distinguish
                  connections in the pool. Choices are
                  ByContainerAndApplication (use both),
                  ByContainer (use Subject),
                  ByApplication (use app supplied params only),
                  ByNothing (all connections are equivalent, usually if adapter supports
                  reauthentication)-->
                  ByContainer



                  <depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager

                  <depends optional-attribute-name="JaasSecurityManagerService">jboss.security:service=JaasSecurityManager

                  java:/TransactionManager
                  <!--make the rar deploy! hack till better deployment-->
                  jboss.jca:service=RARDeployer





                  • 6. Re: How can I configure more than one MySQL database?
                    cake

                    I'm using JBoss3.0.1 with Jetty. I've followed the instructions to create 2 DS. However, I got the following error messages. Any idea? Thanks.

                    06:06:25,336 INFO [MainDeployer] Starting deployment of package: file:/C:/JBoss
                    /jboss-3.0.1/server/all/deploy/mysql-service2.xml
                    06:06:25,396 INFO [MainDeployer] deployment waiting for deployer: file:/C:/JBos
                    s/jboss-3.0.1/server/all/deploy/mysql-service2.xml
                    06:06:25,406 INFO [MainDeployer] Deployment of package: file:/C:/JBoss/jboss-3.
                    0.1/server/all/deploy/mysql-service2.xml is waiting for an appropriate deployer.

                    06:06:25,446 ERROR [URLDeploymentScanner] MBeanException: Exception in MBean ope
                    ration 'checkIncompleteDeployments()'
                    Cause: Incomplete Deployment listing:
                    Packages waiting for a deployer:
                    [org.jboss.deployment.DeploymentInfo@545fc348 { url=file:/C:/JBoss/jboss-3.0.1/s
                    erver/all/deploy/mysql-service2.xml }
                    deployer: null
                    status: null
                    state: INIT_WAITING_DEPLOYER
                    watch: file:/C:/JBoss/jboss-3.0.1/server/all/deploy/mysql-service2.xml
                    lastDeployed: 1030107985406
                    lastModified: 1030107985366
                    mbeans:
                    ]Incompletely deployed packages:
                    [org.jboss.deployment.DeploymentInfo@545fc348 { url=file:/C:/JBoss/jboss-3.0.1/s
                    erver/all/deploy/mysql-service2.xml }
                    deployer: null
                    status: null
                    state: INIT_WAITING_DEPLOYER
                    watch: file:/C:/JBoss/jboss-3.0.1/server/all/deploy/mysql-service2.xml
                    lastDeployed: 1030107985406
                    lastModified: 1030107985366
                    mbeans:
                    ]MBeans waiting for classes:

                    MBeans waiting for other MBeans:


                    06:06:25,616 INFO [URLDeploymentScanner] Started
                    06:06:25,626 INFO [MainDeployer] Deployed package: file:/C:/JBoss/jboss-3.0.1/s
                    erver/all/conf/jboss-service.xml
                    06:06:25,646 INFO [Server] JBoss (MX MicroKernel) [3.0.1 Date:200208062340] Sta
                    rted in 0m:52s:34ms

                    This is my 2nd mysql-service2.xml:

                    <?xml version="1.0" encoding="UTF-8"?>

                    <!-- ===================================================================== -->
                    <!-- -->
                    <!-- JBoss Server Configuration -->
                    <!-- -->
                    <!-- ===================================================================== -->



                    <!-- ==================================================================== -->
                    <!-- New ConnectionManager setup for mysql using 2.0.11 driver -->
                    <!-- Build jmx-api (build/build.sh all) and view for config documentation -->
                    <!-- ==================================================================== -->



                    <depends optional-attribute-name="ManagedConnectionFactoryName">
                    <!--embedded mbean-->


                    MySqlPKDS



                    <config-property name="ConnectionURL" type="java.lang.String">jdbc:mysql://localhost/pk</config-property>
                    <config-property name="DriverClass" type="java.lang.String">org.gjt.mm.mysql.Driver</config-property>
                    <!--set these only if you want only default logins, not through JAAS -->
                    <config-property name="UserName" type="java.lang.String">abcde</config-property>
                    <config-property name="Password" type="java.lang.String">12345</config-property>




                    <!--Below here are advanced properties -->
                    <!--hack-->
                    <depends optional-attribute-name="OldRarDeployment">jboss.jca:service=RARDeployment,name=JBoss LocalTransaction JDBC Wrapper




                    <depends optional-attribute-name="ManagedConnectionPool">
                    <!--embedded mbean-->


                    0
                    50
                    5000
                    15
                    <!--criteria indicates if Subject (from security domain) or app supplied
                    parameters (such as from getConnection(user, pw)) are used to distinguish
                    connections in the pool. Choices are
                    ByContainerAndApplication (use both),
                    ByContainer (use Subject),
                    ByApplication (use app supplied params only),
                    ByNothing (all connections are equivalent, usually if adapter supports
                    reauthentication)-->
                    ByContainer



                    <depends optional-attribute-name="CachedConnectionManager">jboss.jca:service=CachedConnectionManager

                    <depends optional-attribute-name="JaasSecurityManagerService">jboss.security:service=JaasSecurityManager

                    java:/TransactionManager
                    <!--make the rar deploy! hack till better deployment-->
                    jboss.jca:service=RARDeployer





                    • 7. Re: How can I configure more than one MySQL database?
                      mgao

                      thanks lucvu :

                      that is really good idea, however, how about 3 dbs?

                      I am trying carete 3 dbs connection by your way.but only two of them work OK.:(

                      I am using the exactly same environment as you, the default connection work fine.Moreover,I create msql2-service.xml and mysql3-service.xml for other two connections.

                      I am useing ejb staff to connect db and one reference another. So, I have to build ejb2 and then ejb3, the problem is the ejb3 always connect ejb2's dababase?

                      do you have idea about this? where should I specify database for ejb?
                      jbosscmp-jdbc.xml?
                      I change it!

                      java:/MySQL3
                      <datasource-mapping>mySQL</datasource-mapping>
                      <create-table>false</create-table>
                      <remove-table>false</remove-table>
                      <read-only>false</read-only>
                      <pk-constraint>true</pk-constraint>
                      <fk-constraint>true</fk-constraint>



                      java:/MySQL2
                      <datasource-mapping>mySQL</datasource-mapping>
                      <create-table>false</create-table>
                      <remove-table>false</remove-table>
                      <read-only>false</read-only>
                      <pk-constraint>true</pk-constraint>
                      <fk-constraint>true</fk-constraint>


                      any ideas?

                      • 8. Re: How can I configure more than one MySQL database?
                        davidjencks

                        cake -- your config file needs to be named *-service.xml. *-service2.xml will not be recognized.

                        mgao -- only one set of defaults/file, please, the parser can't read your mind. Use 3 ejb jars or specify the datasource per ejb. Consult the jbosscmp-jdbc.dtd to see how to do this.

                        • 9. Re: How can I configure more than one MySQL database?
                          mgao

                          thanks for your reply davidjencks:
                          I have three ejb apps and jar them saparately in one server. I specify three mysql-service.xml. naming mysql, mysql2 and mysql3, each of them end with -service.xml.

                          In each apps, name the jbosscmp-jdbc.xml default section as
                          //-- for mysql-service.xml

                          java:/MySQL
                          <datasource-mapping>mySQL</datasource-mapping>
                          <create-table>false</create-table>
                          <remove-table>false</remove-table>
                          <read-only>false</read-only>
                          <pk-constraint>true</pk-constraint>
                          <fk-constraint>true</fk-constraint>


                          //--for mysql2-services.xml

                          java:/MySQL2
                          <datasource-mapping>mySQL</datasource-mapping>
                          <create-table>false</create-table>
                          <remove-table>false</remove-table>
                          <read-only>false</read-only>
                          <pk-constraint>true</pk-constraint>
                          <fk-constraint>true</fk-constraint>


                          //--for mysql3-services.xml

                          java:/MySQL3
                          <datasource-mapping>mySQL</datasource-mapping>
                          <create-table>false</create-table>
                          <remove-table>false</remove-table>
                          <read-only>false</read-only>
                          <pk-constraint>true</pk-constraint>
                          <fk-constraint>true</fk-constraint>

                          is that right?

                          what do you mean "one set default/file?"
                          default element in jbosscmp-jdbc.xml?

                          • 10. Re: How can I configure more than one MySQL database?
                            davidjencks

                            Sorry, I misinterpreted your earlier post. What you show with the 3 files should work AFAIK. If the 3 datasources are getting deployed successfully I would ask in the cmp forum, I'm not such an expert on how this particular xml is interpreted.

                            • 11. Re: How can I configure more than one MySQL database?
                              mgao

                              thanks davidjencks

                              the three databses connection in my computer work fine now, I retry that way and create the 4th connection, it works! it might be my careless for the 3rd one.

                              Anyway, thank you very much indeed!

                              cheers