I'm going to post this in the JCA / Datasource forum as well, but I think it is possibly more relevant here. I hope this is OK.
I have a very simple cluster with 3 nodes. 1 of the nodes only runs a JCA adapter to access a heavy weight application on that node and does no database access. so really there are currently only 2 nodes which access the database. I was attempting to get by with a local-tx datasource, as the DefaultDS, but eventually ran into a wall that is forcing me to go to a XA datasource.
Since I started out using MySQL I tried to convert my configuration from local-tx to XA. I ran into a class cast error, and since I found very little information on using MySQL as an XA datasource with JBoss on the web, I switched to MSSQL which at least has a example configuration of a XA datasource. I can get MSSQL to work as a local-tx, but I can't get it to work as a XA datasource. Again the chatter on the web seems pretty minimal on this topic.
I'm starting to wonder if anyone has ever made either of these configurations to work with 4.2.1 or if I am just on the wrong track.
I should clarifiy that The error that I am getting is on my XP laptop which I am using for development. I am using the latest version of SQL Server 2005, and the 1.2 jdbc connector from Microsoft. I installed the recommended Hotfix from Microsoft (http://www.theserverside.com/news/thread.tss?thread_id=47218#241439), but I an error which in other places is attributed to trying to create a database that already exits. (SQLJDBC_XA DTC_ERROR http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2124531&SiteID=17&pageid=0)
The XA datasource seems to be working since if I drop the jboss database asnd recreate it it creates most of the tables, but it dies part way through.
The issue I get with an XA datasource and MSSQL:
16:09:00,290 WARN [loggerI18N] [com.arjuna.ats.internal.jta.transaction.arjunac
starterror] TransactionImple.enlistResource - XAResource.start returned: [com.ar
juna.ats.jta.utils.unknownerrorcode] Unknown error code:0 for < 131075, 24, 22,
16:09:00,290 ERROR [STDERR] javax.transaction.xa.XAException: java.sql.SQLExcept
ion: Failed to create the XA control connection. Error: xp_sqljdbc_xa_init failu
re, status:0 msg:*** SQLJDBC_XA DTC_ERROR Context: xa_init, state=1, StatusCode:
0 (0x00000000) ***.^M
16:09:00,290 ERROR [STDERR] at com.microsoft.sqlserver.jdbc.SQLServerXAResourc
16:09:00,290 ERROR [STDERR] at org.jboss.resource.adapter.jdbc.xa.XAManagedCon
16:09:00,290 ERROR [STDERR] at org.jboss.resource.connectionmanager.xa.JcaXARe
16:09:00,290 ERROR [STDERR] at com.arjuna.ats.internal.jta.transaction.arjunac
I'm sorry my topic got truncated, and it is not the easiest to follow. Perhaps I can simplify and clarify.
1.) has anyone been able to get a 4.2.1 cluster to work with an XA default datasource using either SQL Server 2005, or MySQL 5.0
2.) is an XA datasource a prerequisite for a cluster
Well no responses, but I guess that is what is to be expected when I don't gather my thoughts properly before I post. Here is some additional information that I posted on the JCA forum. I'm not sure that that is the right place, so I'll update this topic as well.
I am trying to get the Microsoft 1.2 JDBC driver configured for xa-datasource to function as the DefaultDS for an 4.2.1.GA jboss cluster, and I have run into an issue that I hope you can help with.
On the second and all subsequent attempts at starting the server I get a javax.transaction.RollbackException. I have done no other processing, I just shut it down and start it back up. If I drop all of the tables that JBoss creates, and start the server there are no errors reported.
I have followed the directions to turn on trace for jdbc and ta, but I see no difference in the output.
First let me document the setup which I have pieced together from multiple sources. I think this is getting close. It may contain steps that are not required, but I know this is close to working.
Host is: Microsoft Windows Server 2003
Service Pack 2
Intel Xeon 2.80GHz
1.00 GB of Ram
1.) I installed SQL Server 2005 evaluation
2.) Downloaded and expanded the 1.2 driver package released 10/15/2007 http://www.microsoft.com/downloads/details.aspx?FamilyId=C47053EB-3B64-4794-950D-81E1EC91C1BA&displaylang=en
3.) Copied the appropriate dll to the Binn directory in the SQL Server installation as documented in the comments of the sqljdbc_1.2\enu\xa\xa_install.sql file from the directory where I expanded the JDBC package.
4.) Ran the T-SQL script in the xa directory of the JDBC driver.
5.) Turned on the MS DTC service http://www-1.ibm.com/support/docview.wss?uid=swg21221370
6.) Created a boss user -- Make sure to turn enable SQL login, and uncheck the box that says enforce the log in policy.
7.) Created a jboss database, and made my jboss user the owner of it.
8.) Modified the jboss user to have access to the master and granted the SqlJDBCXAUser, then made the jboss database the default database.
9.) Restarted SQL Server.
10.) Copied the jdbc driver to server/all/lib
11.) Deleted the hsqldb-ds.xml file
12.) Copied the docs/examples/jca/mssql-da-ds.xml file to the deploy directory. It required some major surgery to get right. I eventually wound up with this:
<datasources> <xa-datasource> <jndi-name>DefaultDS</jndi-name> <track-connection-by-tx/> <isSameRM-override-value>false</isSameRM-override-value> <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class> <xa-datasource-property name="ServerName">localhost</xa-datasource-property> <xa-datasource-property name="DatabaseName">jboss</xa-datasource-property> <xa-datasource-property name="SelectMethod">cursor</xa-datasource-property> <xa-datasource-property name="User">jboss</xa-datasource-property> <xa-datasource-property name="Password">jboss</xa-datasource-property> <xa-datasource-property name="URL">jdbc:sqlserver://localhost:1433</xa-datasource-property> <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml --> <metadata> <type-mapping>MS SQLSERVER2000</type-mapping> </metadata> </xa-datasource> </datasources>
13.) In the deploy-hasingleton directory I deleted the hsqldb-jdbc-state-service.xml file
14.) Copied the docs\examples\jms\mssql-jdbc2-service.xml
15.) Changed the datasource in mssql-jdbc2-service.xml to DefaultDS
This being my first attempt ( Yea right :) ) it of course came up with no errors.
The problem is that on all subsequent times that it comes up I get the following exception being thrown.
19:50:39,919 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=DefaultDS' to JNDI name 'java:DefaultDS' 19:50:41,950 WARN [JDBCStateManager] Error initialising state manager db org.jboss.mq.SpyJMSException: Could not commit/rollback a transaction with the transaction manager.; - nested throwable: (javax.transaction.RollbackException: [com.arjuna.ats.internal.jta.transaction.arjunacore.commitwhenaborted] [com.arjuna.ats.internal.jta.transaction.arjunacore.commitwhenaborted] Can't commit because the transaction is in aborted state) at org.jboss.mq.sm.jdbc.JDBCStateManager$JDBCSession.close(JDBCStateManager.java:638) at org.jboss.mq.sm.jdbc.JDBCStateManager.initDB(JDBCStateManager.java:485) at org.jboss.mq.sm.jdbc.JDBCStateManager.startService(JDBCStateManager.java:393) at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289) at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245) at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155) at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94) at org.jboss.mx.server.Invocation.invoke(Invocation.java:86) at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264) at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659) at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978) at $Proxy0.start(Unknown Source) at org.jboss.system.ServiceController.start(ServiceController.java:417) ~~~~~~~~~~~~~~ more stuff you probably don't care about ~~~~~~~~ at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:482) at org.jboss.system.server.ServerImpl.start(ServerImpl.java:362) at org.jboss.Main.boot(Main.java:200) at org.jboss.Main$1.run(Main.java:508) at java.lang.Thread.run(Thread.java:595) Caused by: javax.transaction.RollbackException: [com.arjuna.ats.internal.jta.transaction.arjunacore.commitwhenaborted] [com.arjuna.ats.internal.jta.transaction.arjunacore.commitwhenaborted] Can't commit because the transaction is in aborted state at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1394) at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:135) at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.commit(BaseTransactionManagerDelegate.java:87) at org.jboss.mq.sm.jdbc.JDBCStateManager$JDBCSession.close(JDBCStateManager.java:633) ... 112 more 19:50:42,294 INFO [A] Bound to JNDI name: queue/A 19:50:42,325 INFO [B] Bound to JNDI name: queue/B
I am stumped and I have not been able to come up with any meaningful clues on the Web.
Thanks in advance for any help.
I am not sure if this is the right answer but I think your problem is when you restart the jboss server, you get the transaction issue.
I think there is some problem with JBoss caching mechanism. May be when you are shutting down the JBoss it is not clearing the cache, or you probably are not shutting down the jboss properly. This usually doesn't cause any problem, however, in this case this exactly may be the root of the problem.
I would suggest to clean stop the jboss and check if it works. If not, you can look for the configuration which would clean the cache of application server.
As a workaround, you could write a script that would clean the tables. Call that script from the run.sh or run.bat whichever installation you use.