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