1 Reply Latest reply on Dec 8, 2003 6:36 AM by Sujoy Mazumdar

    configure mySQL 4.0.15 with JBoss 3.2.2

    sherveen Newbie

      Hi, I would be grateful to some one who can help me to configure and use MySQL with JBoss 3.2.2. Please include detailed steps since I am a novice user of JBoss and am not currently well versant in it. Thank you very much in advance. :)

        • 1. Re: configure mySQL 4.0.15 with JBoss 3.2.2
          Sujoy Mazumdar Newbie

          Hi...I have been trying to get it right and finally I figured it out completely...so here it is...

          1.Connect to mysql as the root and then create a user and grant him all rights. Then create a database of ur choice or u could use the default db "test" for the time being.

          2.Create two tables as shown below:

          create table jms_transactions(txid integer);

          create table jms_messages(CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB BLOB, PRIMARY KEY (MESSAGEID, DESTINATION));

          3.Copy the files mysql-ds.xml from <JBOSS_HOME>\docs\examples\jca and copy it into <JBOSS_HOME>\server\default\deploy

          4.Rename the file hsqldb-ds.xml to hsqldb-ds.bak.

          5.Open the mysql-ds.xml file in your favourite editor and make the changes so that it looks like the following:


          <local-tx-datasource>
          <jndi-name>MySqlDS</jndi-name>
          <connection-url>jdbc:mysql://localhost:3306/test</connection-url>
          <driver-class>com.mysql.jdbc.Driver</driver-class>
          <user-name>jboss</user-name>
          jboss
          <security-domain>MySqlDBRealm</security-domain>
          </local-tx-datasource>



          6.Copy the file mysql-jdbc2-service.xml from the folder <JBOSS_HOME>\docs\examples\jms to <JBOSS_HOME>\server\default\deploy\jms

          7.Again edit this file and make sure it looks like the following:



          <!-- ==================================================================== -->
          <!-- Persistence and caching using MySQL -->
          <!-- IMPORTANT: Remove hsqldb-jdbc2-service.xml -->
          <!-- ==================================================================== -->

          <!--
          | The destination manager is the core service within JBossMQ
          -->

          <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache
          <depends optional-attribute-name="PersistenceManager">jboss.mq:service=PersistenceManager
          <depends optional-attribute-name="StateManager">jboss.mq:service=StateManager


          <!--
          | The MessageCache decides where to put JBossMQ message that
          | are sitting around waiting to be consumed by a client.
          |
          | The memory marks are in Megabytes. Once the JVM memory usage hits
          | the high memory mark, the old messages in the cache will start getting
          | stored in the DataDirectory. As memory usage gets closer to the
          | Max memory mark, the amount of message kept in the memory cache aproaches 0.
          -->

          50
          60
          jboss.mq:service=PersistenceManager


          <!-- The PersistenceManager is used to store messages to disk. -->
          <!--
          | The jdbc2 PersistenceManager is the new improved JDBC implementation.
          | This implementation allows you to control how messages are stored in
          | the database.
          |
          | This jdbc2 PM configuration has was supplied by Stephane Nicoll in the forums as an example for MySQL
          -->

          <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=MySqlDS

          BLOB_TYPE=BLOB
          INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
          INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
          SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
          SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
          SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
          SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
          MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
          UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
          UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
          UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
          UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
          UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
          UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
          DELETE_MARKED_MESSAGES_WITH_TX = DELETE JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_TRANSACTIONS.TXID AND JMS_MESSAGES.TXOP=?
          DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
          DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
          DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
          CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB BLOB, PRIMARY KEY (MESSAGEID, DESTINATION))
          CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS (TXID INTEGER)
          CREATE_TABLES_ON_STARTUP = TRUE





          8.Rename the file hsqldb-jdbc2-service.xml in <JBOSS_HOME>\server\default\deploy\jms to hsqldb-jdbc2-service.bak.

          9.Open the file standardjbosscmp.xml in the folder <JBOSS_HOME>\server\default\conf and make the changes


          java:/MySqlDS
          <datasource-mapping>mySQL</datasource-mapping>

          <create-table>true</create-table>
          <remove-table>false</remove-table>
          <read-only>false</read-only>
          <read-time-out>300000</read-time-out>
          <row-locking>false</row-locking>
          <pk-constraint>true</pk-constraint>
          <fk-constraint>ture</fk-constraint>
          <preferred-relation-mapping>foreign-key</preferred-relation-mapping>
          <read-ahead>
          on-load
          <page-size>1000</page-size>
          <eager-load-group>*</eager-load-group>
          </read-ahead>
          <list-cache-max>1000</list-cache-max>

          ***** ==== Rest of the file as it is ==== *****

          10.Open the file standardjaws.xml from the folder <JBOSS_HOME>\server\default\conf and make the changes


          java:/MySqlDS
          <type-mapping>mySQL</type-mapping>
          true

          <default-entity>
          <create-table>true</create-table>
          <remove-table>false</remove-table>
          <tuned-updates>true</tuned-updates>
          <read-only>false</read-only>
          <time-out>3000</time-out>
          <row-locking>false</row-locking>
          <read-ahead>false</read-ahead>
          </default-entity>

          ***** ==== Rest is as it is ==== *****

          11.Open the file login-conf.xml in the folder <JBOSS_HOME>\server\default\conf and add the following piece of code given below just before the line
          <application-policy name = "HsqlDbRealm">

          Code:
          <application-policy name = "MySqlDBRealm">

          <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
          flag = "required">
          <module-option name = "principal">jboss</module-option>
          <module-option name = "userName">jboss</module-option>
          <module-option name = "password">jboss</module-option>
          <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=MySqlDS</module-option>
          </login-module>

          </application-policy>

          12.Open the file schedule-manager-service.xml in the folder <JBOSS_HOME>\server\default\deploy and make the changes

          jboss:service=mySQL instead of jboss:service=Hypersonic

          and

          java:MySqlDS instead of java:DefaultDS

          Now that we have made all the necessary changes, now start the server from the console by typing
          run > <LOG_FOLDER>\jboss.log

          Next open the log file from the location u have saved it in and just check to see that no exceptions of any kinds have been thrown.

          If none are there...then you have successfully deployed JBOSS and MySql.

          NOTE: Backup all the files before you delete them or change them.

          Happy coding.... Let me know if you have any problems...

          adios amigo...

          Sujay