Friday, April 3, 2009

Switch JBossESB-4.5.GA from HSQL to MSSQL

To switch JBossESB from the default HSQL embedded database to a "real" database like MSSQL, the persistence configuration of JMS, jUDDI, ESB MessageStore and jBPM needs updating.

1. JMS
rm server/default/deploy/jms/hsqldb-jdbc2-service.xml
cp docs/examples/jms/mssql-jdbc2-service.xml server/default/deploy/jms
cp docs/examples/jca/mssql-ds.xml server/default/deploy/jms
mv server/default/deploy/jms/hsql-jdbc-state-service.xml server/default/deploy/jms/mssql-jdbc-state-service.xml


update the mssql-jdbc-state-service.xml to point to the MSSQLDS datasource

<depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=MSSQLDS</depends>

update the "Security domain for JBossMQ" in the server/default/conf/login-config.xml to java:/MSSQLDS

2. jUDDI
cp docs/examples/jca/mssql-ds.xml server/default/deploy/jbossesb.sar/juddi-ds.xml
update the juddi-ds.xml, make sure the <jndi-name>juddiDB</jndi-name>
update the server/default/deploy/jbossesb.sar/esb.juddi.xml to make sure the mssql sql is used to create the jUDDI tables

<entry key="juddi.sqlFiles">juddi-sql/mssql/create_database.sql,juddi-sql/mssql/import.sql</entry>

3. JBossESB MessageStore
rm server/default/deploy/jbossesb.esb/hsql-ds.xml
cp docs/examples/jca/mssql-ds.xml server/default/deploy/jbossesb.esb

update the mssql-ds.xml, make sure the <jndi-name>JBossESBDS</jndi-name>
update the server/default/deploy/jbossesb.esb/jbossesb-service.xml to point to the mssql sql for creating the message table

<attribute name="SqlFiles">
message-store-sql/mssql/create_database.sql
</attribute>

4. jBPM
rm server/default/deploy/jbpm.esb/hsql-ds.xml
cp docs/examples/jca/mssql-ds.xml server/default/deploy/jbpm.esb/

update the mssql-ds.xml, make sure the <jndi-name>JbpmDS</jndi-name>
update the server/default/deploy/jbpm.esb/hibernate.cfg.xml to make sure the SQLServerDialect is used

<!-- hibernate dialect -->
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>

update the server/default/deploy/jbpm.esb/jbpm-service.xml so it points to the mssql sql file

<attribute name="SqlFiles">
jbpm-sql/jbpm.jpdl.mssql.sql,
jbpm-sql/import.sql
</attribute>

Not that the import.sql tries to set the primary key, which is an autoincrement column. MSSQL does not like this. Simply setting this column from this script will get you going.
5. Finishing up
To Finish up, make sure you add the mssql database driver jar to the server/default/lib directory, and make sure you added your credentials to the various mssql-ds.xml files.

<datasources>
<local-tx-datasource>
<jndi-name>JbpmDS</jndi-name>
<connection-url>jdbc:jtds:sqlserver://192.168.1.2:1433/jbossesb</connection-url>
<driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
<user-name>jbossesb_admin</user-name>
<password>admin</password>
<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
<metadata>
<type-mapping>MS SQLSERVER2000</type-mapping>
</metadata>
</local-tx-datasource>

Make sure this user has table create rights, so that when you start up JBossESB it can create the tables.

12 comments:

amurguzur said...

Hi,

Congratulations fro your blog, it´s nice.

I tell you...

I am trying to configure my server with mysql but i have some exception. I follow your instructions but when i try to run application server throws some exceptions. Should I change some files more to configure JBoss ESB with MySQL?

I will apreciate a lot your answer.

I am using JBoss ESB 4.5, JBossAS 4.2.3 and MySQL 5.1.

Should I use JBossAS 4.2.2?

Thanks.

mur.

amurguzur said...

Another thing, how many rows do you have in your MSSQL jbossesb database? When I run the server it creates 62 tables in my database. Is it OK?

Thanks.

ert said...

Hi Amurguzur,

It should be fine to use JBossAS-4.2.3. Note that I wrote the blog for MSSQL, not MySQL, however the apart from the driver and the datasource the instructions should be the same.

In my database I have:
32 JBPM tables
5 JMS tables (using JBossMQ)
31 tables
1 JBossESB table

adding up to a total of 69 tables.

Hope that helps, good luck!

--Kurt

ert said...

Here is the complete list:

JBPM_ACTION JBPM_BYTEARRAY JBPM_BYTEBLOCK JBPM_COMMENT JBPM_DECISIONCONDITIONS JBPM_DELEGATION JBPM_EVENT JBPM_EXCEPTIONHANDLER JBPM_ID_GROUP JBPM_ID_MEMBERSHIP JBPM_ID_PERMISSIONS JBPM_ID_USER JBPM_JOB JBPM_LOG JBPM_MODULEDEFINITION JBPM_MODULEINSTANCE JBPM_NODE JBPM_POOLEDACTOR JBPM_PROCESSDEFINITION JBPM_PROCESSINSTANCE JBPM_RUNTIMEACTION JBPM_SWIMLANE JBPM_SWIMLANEINSTANCE JBPM_TASK JBPM_TASKACTORPOOL JBPM_TASKCONTROLLER JBPM_TASKINSTANCE JBPM_TOKEN JBPM_TOKENVARIABLEMAP JBPM_TRANSITION JBPM_VARIABLEACCESS JBPM_VARIABLEINSTANCE JMS_MESSAGES JMS_ROLES JMS_SUBSCRIPTIONS JMS_TRANSACTIONS JMS_USERS JUDDI_ADDRESS JUDDI_ADDRESS_LINE JUDDI_AUTH_TOKEN JUDDI_BINDING_CATEGORY JUDDI_BINDING_DESCR JUDDI_BINDING_TEMPLATE JUDDI_BUSINESS_CATEGORY JUDDI_BUSINESS_DESCR JUDDI_BUSINESS_ENTITY JUDDI_BUSINESS_IDENTIFIER JUDDI_BUSINESS_NAME JUDDI_BUSINESS_SERVICE JUDDI_CONTACT JUDDI_CONTACT_DESCR JUDDI_DISCOVERY_URL JUDDI_EMAIL JUDDI_INSTANCE_DETAILS_DESCR JUDDI_INSTANCE_DETAILS_DOC_DESCR JUDDI_PHONE JUDDI_PUBLISHER JUDDI_PUBLISHER_ASSERTION JUDDI_SERVICE_CATEGORY JUDDI_SERVICE_DESCR JUDDI_SERVICE_NAME JUDDI_TMODEL JUDDI_TMODEL_CATEGORY JUDDI_TMODEL_DESCR JUDDI_TMODEL_DOC_DESCR JUDDI_TMODEL_IDENTIFIER JUDDI_TMODEL_INSTANCE_INFO JUDDI_TMODEL_INSTANCE_INFO_DESCR
message

amurguzur said...

Hi Kurt,

What version of JBossAS are you using? Do you deploy JBoss ESB inside JBossAS or are you using embedded Application Server with ESB? What have you got installed? JBPM, JBossESB and JBossAS?

I am desesperate because I´m not able to point persistence to MySQL.


Thanks a lot.

ert said...

I'm also using JBoss-4.2.3.GA, and JBossESB-4.5. If you have specific issues you should try posting them to the JBossESB User Forum. Chances are someone knows exactly what the solution to your problem is.

Cheers,

-Kurt

amurguzur said...

Finally I got it yesterday. I have 69 tables, like yours, in my mysql database. Instead of erasing files hsql-ds.xml I had to delete a file called hsqldb-ds.xml from the root directory (deploy). For the rest, steps are similar considering that we are configuring a mysql database.

Congratulations again for the blog, I could not succeed without your post. I will keep reading your blog!

Thank you very much for your help.

mur.

amurguzur said...

Hi,

I´m back.

I'm trying to deploy a simple process on the server but it does not let me to upload java classes. I create a package too and upload it from the jbpm-console but nothing. If I only select processdefinition file, unselecting java classes (Action Handlers), deploy is succesfull, but when I active a signal in jbpm-console server throws an exception because it is not able to find this Action Handler class, so the process can not finish.

Any idea? JBossESB has JBPM
embedded no?

ert said...

Hi Amurguzur, I think you should post this question to either the jBPM or JBossESB user forum. I think you can either deploy your java classes in to the jBPM database or to the AppServer (check the jBPM docs). But this really goes beyond the scope of this blog entry.

jarkko said...

Hi,
Has anyone seen similar "hangs";
The connection seems to hang (see the thread dump below) and everythings is progressing very slowly. After a while some timeout comes and a new connection is created which works, for awhile..

Thread: JbpmJobExector:172.16.30.242:1 : priority:5, demon:false, threadId:112, threadState:RUNNABLE

java.net.SocketInputStream.socketRead0(Native Method)
java.net.SocketInputStream.read(SocketInputStream.java:129)
java.io.DataInputStream.readFully(DataInputStream.java:178)
java.io.DataInputStream.readFully(DataInputStream.java:152)
net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:842)
net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java:723)
- locked <0x2e147ea4> (a java.util.ArrayList)
net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java:466)
net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java:103)
net.sourceforge.jtds.jdbc.ResponseStream.peek(ResponseStream.java:88)
net.sourceforge.jtds.jdbc.TdsCore.wait(TdsCore.java:3932)
net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1046)
- locked <0x4386589e> (a net.sourceforge.jtds.jdbc.TdsCore)
net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:465)
net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
- locked <0x29240fb4> (a net.sourceforge.jtds.jdbc.ConnectionJDBC3)
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
org.hibernate.loader.Loader.doQuery(Loader.java:697)
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
org.hibernate.loader.Loader.doList(Loader.java:2228)
org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
org.hibernate.loader.Loader.list(Loader.java:2120)
org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:835)
org.jbpm.db.JobSession.getFirstAcquirableJob(JobSession.java:52)
org.jbpm.job.executor.JobExecutorThread.acquireJobs(JobExecutorThread.java:109)
- locked <0x5332bae5> (a org.jbpm.job.executor.JobExecutor)
org.jbpm.job.executor.JobExecutorThread.run(JobExecutorThread.java:54)

Locked synchronizers :
- locked <0x7e830785> (a java.util.concurrent.locks.ReentrantLock$NonfairSync)

This is with JBoss AS 5.1, JTDS or MS JDBC drivers, MS SQL Server 9, JBoss ESB 4.6, JDK 1.6.0_18.

jarkko said...

For anyone working with MS SQL server please see this: http://confluence.atlassian.com/display/DOC/Known+Issues+For+SQL+Server

Unknown said...

Great work !

Regards,

Slawek