SetUp A Mysql Datasource

Setting up a mySQL datasource

Download the driver

1、First, http://www.mysql.com/products/connector/j/ appropriate for your edition of mySQL.
2、Next, untar/unzip it and extract the jar file.
3、Copy the jar file into $JBOSS_HOME/server/xxx/lib, where xxx is your config name (such as "default") NOTE: For JBoss 4.0.2, use the jar file mysql-connector-java-3.1.8-bin.jar, not mysql-connector-java-3.1.8-bin-g.jar.
4、Copy the $JBOSS_HOME/docs/examples/jca/mysql-ds.xml file to $JBOSS_HOME/server/xxx/deploy

Configure the datasource

1、Edit the mysql-ds.xml file.
2、Replace <jndi-name>MySqlDS</jndi-name> with your datasource name. If you choose to make mySQL your default database (DefaultDS), then call this DefaultDS and be sure to delete the example $JBOSS_HOME/server/all/deploy/hsqldb-ds.xml which is also configured to be DefaultDS.
3、Replace <connection-url>jdbc:mysql://mysql-hostname:3306/jbossdb</connection-url> with your connection string. Generally you just need to replace mysql-hostname with your host. Be sure that your user has permission to connect to that hostname.
4、Set the user-name and hostname elements to your database username and hostname

Advanced options for the MySQL Driver can be set with <connection-property name="property">value</connection-property>. Refer to MySQL Connector/J Manual Chapter 2 for more Information.

Named pipes

Under Windows NT/2000/XP you can connect to the MySQL Server via named pipes if the MySQL server and JBoss are running on the same machine. Following the Connector/J documentation this is 30%-50% faster than TCP/IP access.

Set the opion enable-named-pipe in the my.ini and restart the MySQL Server (the server variable named_pipe must be ON)
Set the Property socketFactory to com.mysql.jdbc.NamedPipeSocketFactory?
Set the JDBC URL to jdbc:mysql://./databasename

Automatic reconnect

WARNING: DO NOT ENABLE AUTO RECONNECT IF YOU ARE USING MANAGED TRANSACTIONS
The auto reconnect does not preserve transaction state in the database.
It is ok if you are ALWAYS using auto-commit=true.

autoReconnect (default = false) Set the driver to reconnect if the MySQL Server fails.
maxReconnects (default = 3) Maximum number of connection attembts.
initialTimeout (default = 2) Delay in seconds between connection atembts

JBossMQ

1、First copy $JBOSS_HOME/docs/examples/jms/mysql-jdbc2-service.xml to $JBOSS_HOME/server/xxx/deploy/jms
2、Delete hsqldb-jdbc2-service.xml from $JBOSS_HOME/server/xxx/deploy/jms
3、NOTE: If you made mysql your DefaultDS above, you need to edit $JBOSS_HOME/server/xx/deploy/jms/mysql-jbbc2-service.xml and set the DataSourceBinding.name by replacing <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=MySqlDS</depends> with <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
4、NOTE: The maximum length for JMS destinations in the example mysql-jdbc2-service.xml file in JBoss 4.0.2 is 150 characters. This is typically too short to contain the full destination name, especially if a message selector is involved. You may need to alter the CREATE_MESSAGE_TABLE line so that the maximum length of the DESTINATION column is 255 characters. Alternately, for even longer names, make it a TEXT column type and specify a maximum length of 255 to use in the primary key. That is:

CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB LONGBLOB, PRIMARY KEY (MESSAGEID, DESTINATION))

Or

CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION TEXT NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB LONGBLOB, PRIMARY KEY (MESSAGEID, DESTINATION(255)))

Troubleshooting

1、If you get connection or password errors, it is most likely an issue of permissions to the hostname supplied. See the http://dev.mysql.com/doc/mysql/en/Adding_users.html?.
2、If you try connecting to "localhost" and keep getting permission errors regarding "localhost.localdomain", you're running redhat Linux. I don't know how to fix it. I'll let someone who does finish it out here.
3、If you get errors regarding creating the tables while deploying an entity bean, then perhaps your user doesn't have permission to create tables in that database. You can grant the user *.*. See http://dev.mysql.com/doc/mysql/en/Adding_users.html? for more information.

Examples

MySQL server on localhost with TCP/IP connection on port 3306 and autoReconnect enabled

This is a bad idea, it is ok for no-tx-datasource.

<datasources>
  <local-tx-datasource>
 
    <jndi-name>MySqlDS</jndi-name>
 
    <connection-url>jdbc:mysql://localhost:3306/database</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
 
    <user-name>username</user-name>
    <password>secret</password>
 
    <connection-property name="autoReconnect">true</connection-property>
 
    <!-- Typemapping for JBoss 4.0 -->
    <metadata>
      <type-mapping>mySQL</type-mapping>
    </metadata>
 
  </local-tx-datasource>
</datasources>

MySQL server on localhost with connection over Named Pipe

<datasources>
  <local-tx-datasource>
 
    <jndi-name>MySQLDS</jndi-name>
    <connection-url>jdbc:mysql://./database</connection-url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
 
    <user-name>username</user-name>
    <password>secret</password>
 
    <connection-property name="socketFactory">com.mysql.jdbc.NamedPipeSocketFactory</connection-property>

    <!-- Typemapping for JBoss 4.0 -->
    <metadata>
      <type-mapping>mySQL</type-mapping>
    </metadata>
 
  </local-tx-datasource>
</datasources>