How to switch JBoss ESB Server 4.7 from HSQLDB to MySQL5.5?
mannuyi Jul 3, 2011 11:51 AMI have to ask someone who can help me.
I spent whole day, but I still failed.
Below is my step by step:
1.Replace deploy\hsqldb-ds.xml with mysql-ds.xml
<datasources>
<local-tx-datasource>
<jndi-name>DefaultDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/localDB</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>
<password></password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<idle-timeout-minutes>0</idle-timeout-minutes>
<track-statements/>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<metadata>
<type-mapping>mySQL</type-mapping>
</metadata>
</local-tx-datasource>
</datasources>
2.deploy\jbossesb.esb\message-store-ds.xml
<datasources>
<local-tx-datasource>
<jndi-name>JBossESBDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/jbossesb</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>mysql</user-name>
<password></password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
</local-tx-datasource>
</datasources>
3.deploy\jbossesb.esb\jbossesb-service.xml
<server>
<mbean code="org.jboss.internal.soa.esb.dependencies.DatabaseInitializer"
name="jboss.esb:service=MessageStoreDatabaseInitializer">
<attribute name="Datasource">java:/JBossESBDS</attribute>
<attribute name="ExistsSql">select count(*) from message</attribute>
<attribute name="SqlFiles">
message-store-sql/mysql/create_database.sql
</attribute>
<depends>jboss.jca:service=DataSourceBinding,name=JBossESBDS</depends>
</mbean>
</server>
4.deploy\jbossesb-registry.sar\juddi-ds.xml
<datasources>
<local-tx-datasource>
<jndi-name>juddiDB</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/juddiDB</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>
<password></password>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<!--<depends>jboss:service=Hypersonic,database=juddiDB</depends>-->
</local-tx-datasource>
</datasources>
5.deploy\jbossesb-registry.sar\META-INF\persistence.xml
......
<properties> | |
<property name="hibernate.archive.autodetection" value="class"/> | |
<property name="hibernate.hbm2ddl.auto" value="update"/> | |
<property name="hibernate.show_sql" value="false"/> | |
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" /> | |
</properties> |
......
6.Rename deploy\jboss-messaging.sar\hsqldb-persistence-service.xml to mysql-persistence-service.xml
7.deploy\jbpm.esb\jbpm-ds.xml
<datasources>
<local-tx-datasource>
<jndi-name>JbpmDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/jbpmDB</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>root</user-name>
<password></password>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<min-pool-size>5</min-pool-size>
<max-pool-size>20</max-pool-size>
<idle-timeout-minutes>0</idle-timeout-minutes>
<prepared-statement-cache-size>32</prepared-statement-cache-size>
</local-tx-datasource>
</datasources>
8.deploy\jbpm.esb\hibernate.cfg.xml
......
<property name="hibernate.dialect">org.hibernate.dialect.mysqlDialect</property> |
<!-- JDBC connection properties (begin) ===--> | |
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property> | |
<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/jbpmDB</property> | |
<property name="hibernate.connection.username">root</property> | |
<property name="hibernate.connection.password"></property> |
......
9.deploy\jbpm.esb\jbpm-quartz.properties
......
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MySQLDelegate
......
10.deploy\jbpm.esb\jbpm-service.xml
......
<attribute name="SqlFiles"> | |
jbpm-sql/jbpm.jpdl.mysql.sql, | |
jbpm-sql/import.sql | |
</attribute> |
......
11.Download mysql.jar, and copy to lib folder.
12.create databases and grant privileges:
mysqladmin -u root create localDB
mysqladmin -u root create jbossesb
mysqladmin -u root create juddiDB
mysqladmin -u root create jbpmDB
grant all privileges on *.* to ''@'localhost' identified by '';
13.start run.bat
14.Exception log
2011-07-03 23:47:13,406 DEBUG [org.jboss.system.ServiceController] starting service jboss.messaging:service=PersistenceManager
2011-07-03 23:47:13,406 DEBUG [org.jboss.messaging.core.jmx.JDBCPersistenceManagerService] Starting jboss.messaging:service=PersistenceManager
2011-07-03 23:47:15,156 DEBUG [org.jboss.messaging.core.impl.JDBCSupport] Failed to execute: CREATE TABLE JBM_MSG (MESSAGE_ID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIMESTAMP BIGINT, PRIORITY TINYINT, TYPE TINYINT, HEADERS LONGVARBINARY, PAYLOAD LONGVARBINARY, PRIMARY KEY (MESSAGE_ID))
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LONGVARBINARY, PAYLOAD LONGVARBINARY, PRIMARY KEY (MESSAGE_ID))' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)