Problems at Startup with MySQL 5 and XAConnection

[bviveiros]

Has anyone setup JBoss Messaging on MySQL 5 with an XAConnection? I am using the persistance service from the examples but am getting the following when starting the server:

2006-06-05 16:03:59,376 DEBUG (Thread: main) [server.plugin.JDBCChannelMapper] Failed to create users table: CREATE TABLE JMS_USER (USERID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, CLIENTID VARCHAR(128), PRIMARY KEY(USERID))
java.sql.SQLException: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2822)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1536)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1626)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:3025)
 at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1147)
 at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1070)
 at com.mysql.jdbc.jdbc2.optional.StatementWrapper.executeUpdate(StatementWrapper.java:801)
 at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:161)
 at org.jboss.jms.server.plugin.JDBCChannelMapper.createSchema(JDBCChannelMapper.java:1091)
 at org.jboss.jms.server.plugin.JDBCChannelMapper.startService(JDBCChannelMapper.java:205)
 at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:274)
 at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:181)
...

[timfox]

Can you post your datasource configuration?

Also what version of JBoss AS and JBoss Messaging are you using?

Thanks.

[bviveiros]

I'm running
- JBoss AS 4.0.3SP1
- JBoss Messaging 1.0.1.CR2
- MySQL 5.0.20a for Win32
- MySQL 5.0.0-beta (for XAConnection)

I did notice that MySQL has bug #17343 against the XAConnection, but not sure if it's the same problem.

Below is my config, let me know if you need anything else.

Messaging datasource:

<datasources>
 <xa-datasource>

 <!-- The jndi name of the DataSource, it is prefixed with java:/ -->
 <!-- Datasources are not available outside the virtual machine -->
 <jndi-name>IMPJMSDatasource</jndi-name>

 <track-connection-by-tx/>

 <isSameRM-override-value>false</isSameRM-override-value>

 <no-tx-separate-pools />

 <!-- The driver class -->
 <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
 <xa-datasource-property name="URL">jdbc:mysql://127.0.0.1/imp_jms</xa-datasource-property>

 <!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
 <min-pool-size>5</min-pool-size>

 <!-- The maximum connections in a pool/sub-pool -->
 <max-pool-size>100</max-pool-size>

 <!-- The time before an unused connection is destroyed -->
 <!-- NOTE: This is the check period. It will be destroyed somewhere between 1x and 2x this timeout after last use -->
 <!-- TEMPORARY FIX! - Disable idle connection removal, HSQLDB has a problem with not reaping threads on closed connections -->
 <idle-timeout-minutes>0</idle-timeout-minutes>

 <!-- sql to call on an existing pooled connection when it is obtained from pool -->
 <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

 <!-- Whether to check all statements are closed when the connection is returned to the pool,
 this is a debugging feature that should be turned off in production -->
 <!-- track-statements/ -->

 <!-- Use the security domain defined in conf/login-config.xml -->
 <security-domain>IMPMQDbRealm</security-domain>

 <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
 <metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>

 </xa-datasource>

</datasources>

Application Datasource:

<datasources>
 <xa-datasource>

 <!-- The jndi name of the DataSource, it is prefixed with java:/ -->
 <!-- Datasources are not available outside the virtual machine -->
 <jndi-name>IMPDatasource</jndi-name>

 <track-connection-by-tx />

 <isSameRM-override-value>false</isSameRM-override-value>

 <no-tx-separate-pools />

 <!-- The driver class -->
 <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
 <xa-datasource-property name="URL">jdbc:mysql://127.0.0.1/imp</xa-datasource-property>

 <!-- The minimum connections in a pool/sub-pool. Pools are lazily constructed on first use -->
 <min-pool-size>5</min-pool-size>

 <!-- The maximum connections in a pool/sub-pool -->
 <max-pool-size>100</max-pool-size>

 <!-- The time before an unused connection is destroyed -->
 <!-- NOTE: This is the check period. It will be destroyed somewhere between 1x and 2x this timeout after last use -->
 <!-- TEMPORARY FIX! - Disable idle connection removal, HSQLDB has a problem with not reaping threads on closed connections -->
 <idle-timeout-minutes>0</idle-timeout-minutes>

 <!-- sql to call on an existing pooled connection when it is obtained from pool -->
 <check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

 <!-- Whether to check all statements are closed when the connection is returned to the pool,
 this is a debugging feature that should be turned off in production -->
 <!-- track-statements/ -->

 <!-- Use the security domain defined in conf/login-config.xml -->
 <security-domain>IMPDbRealm</security-domain>

 <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
 <metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>

 </xa-datasource>

</datasources>

Security Domains:

<application-policy name = "messaging">
 <authentication>
 <login-module code = "org.jboss.security.auth.spi.UsersRolesLoginModule"
 flag = "required" >
 <module-option name = "unauthenticatedIdentity">guest</module-option>
 <module-option name = "usersProperties">messaging-users.properties</module-option>
 <module-option name = "rolesProperties">messaging-roles.properties</module-option>
 </login-module>
 </authentication>
 </application-policy>

 <application-policy name = "IMPDbRealm">
 <authentication>
 <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
 flag = "required">
 <module-option name = "principal">root</module-option>
 <module-option name = "userName">root</module-option>
 <module-option name = "password">password</module-option>
 <module-option name = "managedConnectionFactoryName">jboss.jca:service=XATxCM,name=IMPDatasource</module-option>
 </login-module>
 </authentication>
 </application-policy>

 <application-policy name = "IMPMQDbRealm">
 <authentication>
 <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
 flag = "required">
 <module-option name = "principal">root</module-option>
 <module-option name = "userName">root</module-option>
 <module-option name = "password">password</module-option>
 <module-option name = "managedConnectionFactoryName">jboss.jca:service=XATxCM,name=IMPJMSDatasource</module-option>
 </login-module>
 </authentication>
 </application-policy>

 <application-policy name = "JmsXARealm">
 <authentication>
 <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
 flag = "required">
 <module-option name = "principal">guest</module-option>
 <module-option name = "userName">guest</module-option>
 <module-option name = "password">guest</module-option>
 <module-option name = "managedConnectionFactoryName">jboss.jca:service=TxCM,name=JmsXA</module-option>
 </login-module>
 </authentication>
 </application-policy>

[timfox]

Yes, it might be a MySQL bug, I need to investigate further.

I am travelling this and next week so I'm unlikely to get around to this until the week after next.

Having said all that, I don't understand why you're using an XA datasource for messaging.

There doesn't seem to me to be any good reason for doing this since messaging doesn't require the database to support XA for it's purposes.

So best bet is just to use a normal datasource for messaging.

[bviveiros]

Thanks Tim. Although I'm not sure that I understand the following:

messaging doesn't require the database to support XA for it's purposes.

I have a servlet that peforms some database inserts then posts a message to a queue, therefore I need the entire process to be wrapped in one transaction. Is there a way to do this without using an XA Datasource for messaging?

[ovidiu.feodorov]

Configure Messaging to use a non-XA datasource and then use an XASession to enroll Messaging with your application's transaction.

[timfox]

"bviveiros" wrote:
Thanks Tim. Although I'm not sure that I understand the following:

messaging doesn't require the database to support XA for it's purposes.

I have a servlet that peforms some database inserts then posts a message to a queue, therefore I need the entire process to be wrapped in one transaction. Is there a way to do this without using an XA Datasource for messaging?

The type of datasource that messaging uses is just an implementation detail and shouldn't concern the user.

Messaging itself provides an XAResource as Ovidiu has mentioned that can be used to enlist the messaging session in a global JTA transaction. (See JTA spec for more details).

You can use this directly, or use the JCA adaptor:

http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJMSRA

[felipeal]

Hi Ovidiu,

Configure Messaging to use a non-XA datasource and then use an XASession to enroll Messaging with your application's transaction.

I'm facing the same situation as bviveiros and also thought I would had to usa an xa-datasource (and am getting MySQL errors). Now, if I use your approach, could the datasource used to connect to the database also be a local-tx (and not xa)? I mean, in my case, both my applications's DB and the DB used by JMS are mysql.

Also, is there a quick link on how to do this setup you suggested (using local-tx + XASession)?

TIA,

-- Felipe

[timfox]

As already mentioned, it's completely pointless using an XADatasource for the messaging database connection, since internally we suspend any current tx and create a new tx for the duration of the database operation.

That tx is not available to have any other resource enlisted in it.

So, you should always use an local-tx datasource.

If you want to send (or acknowledge) message(s) in the same global tx as something else (e.g. updating a database) then you can either:

1) Use the XAResource directly (this is a bit fiddly and I suggest you read the JTA spec first to ensure you know what you are doing)

something like this off the top of my head:

transactionManager.begin();

Transaction tx = transactionManager.getTransaction();

XAConnectionFactory cf = (XAConnectionFactory)ic.lookup("/XAConnectionFactory");

XAConnection connection = cf.createConnection();

XASession sess = connection.createXASession();

XAResource res = sess.getXAResource();

tx.enlistResource(res);

MessageProducer producer = sess.createProducer(queue);

producer.send(sess.createMessage());

XAResource res2 = getXAResourceForYourdatabase();

tx.enlistResource(res2);

//do something with your database

tx.commit();

You can find xa examples in the Jboss messaging test suite

org.jboss.test.messaging.jms.XATest

Alternatively, if you are executing in a managed (read, JCA enabled) environment then you can use a UserTransaction, this is a whole lot easier.

Managed environments typically include servlets, and inside ejbs.

To do this, just start a user transaction, send your message and do your database update, commit and that should all happen in the same global tx.

You just need to make sure you are using the jms JCA resource adapter - this is the one normally available at java:/jmsXA, and are using a JCA managed data source for your database access.

See the JCA wiki pages for more information.

[felipeal]

"timfox" wrote:

As already mentioned, it's completely pointless using an XADatasource for the messaging database connection, since internally we suspend any current tx and create a new tx for the duration of the database operation.

Forgive my ignorance (on the JBoss internals - I'm not really thaaat stupid when it comes to Java EE/XA/JTA,etc... :-), but how to achieve 2PC commit then? I mean, suppose I'm using a XADatasource for both my app's DB and my JBossMQ DB, add 100 messages in the queue, try to update the DB, get an exception and set the transaction for rollback. According to the 2PC/DTP land, the JBossMQ transaction should rollback as well. Of course, what must be rolled back is the transaction of the JBossMQ resource, not necessary its underlying database. But if the resource suspended my global-tx and started a new local-tx one, once I finished posting the 100 messages, that new tx would be committed (and ready to be consumed), right? Or am I missing something (I hope so...)?

"timfox" wrote:

1) Use the XAResource directly (this is a bit fiddly and I suggest you read the JTA spec first to ensure you know what you are doing)

That's too hardcore, and lacks all the Java EE fun - that code you showed looked more like the DTP/XA samples for C than Java/Java EE :-(

"timfox" wrote:

Alternatively, if you are executing in a managed (read, JCA enabled) environment then you can use a UserTransaction, this is a whole lot easier.

Yes, that's what I'm doing; I'm even using Spring to start the UserTransaction for me...

"timfox" wrote:

To do this, just start a user transaction, send your message and do your database update, commit and that should all happen in the same global tx.

That's what I did initially, and I thought JBoss would (by default) manage to enroll the 2 local transactions (DB and JMS) in a global/XA transaction. But once I deployed the application, the MDBs started to consume the messages before the global tx committed, so I realized something was not set correctly...

"timfox" wrote:

You just need to make sure you are using the jms JCA resource adapter - this is the one normally available at java:/jmsXA, and are using a JCA managed data source for your database access.

To be honest, that was my first reaction - I've even found the java:/jmsXA reference somewhere (either on the wiki page on some XML file in the deploy dir), but after looking on some other places, I thought I would need to set a <xa-datasource> instead of <local-tx-datasource>...

"timfox" wrote:

See the JCA wiki pages for more information.

Hmm, I think I got it now (http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJMSRA): I just need to change the JNDI name when getting the factory (somewhere in the Spring XML wasteland :-). I will try it tomorrow....

Thanks again for you help (and patience :-)

-- Felipe

[timfox]

"felipeal" wrote:

Forgive my ignorance (on the JBoss internals - I'm not really thaaat stupid when it comes to Java EE/XA/JTA,etc... :-), but how to achieve 2PC commit then? I mean, suppose I'm using a XADatasource for both my app's DB and my JBossMQ DB, add 100 messages in the queue, try to update the DB, get an exception and set the transaction for rollback. According to the 2PC/DTP land, the JBossMQ transaction should rollback as well. Of course, what must be rolled back is the transaction of the JBossMQ resource, not necessary its underlying database. But if the resource suspended my global-tx and started a new local-tx one, once I finished posting the 100 messages, that new tx would be committed (and ready to be consumed), right? Or am I missing something (I hope so...)?

Ok, so in a nutshell this is what happens when use a JBoss Messaging (you meant JBoss Messaging not JBoss MQ, right?) XAConnection in a global tx.

Let's say you are in an ejb method and either using a UserTransaction directly or letting the ejb container demarcate transaction boundaries for that method.

You are using the JBoss JCA resource adaptor (http://wiki.jboss.org/wiki/Wiki.jsp?page=JBossJMSRA) to send JMS messages, and you are using an JCA XA datasource (this one *does* need to do be XA since you are using the XA capabilities of your database - make sure your db does have XA capabilities! Some don't) for your direct data access, and you are using a local tx datasource for the datasource used by JBM for its internal data access.

Either way, the application server (the JCA layer) will have managed to get two XAResources: XAResource1 obtained from XASession.getXAResource() which is an XAResource for your JMS session, and XAResource2 which is an XAResource obtained from the database you're doing direct access with.

A global tx will be started for the UserTransaction and the app server will enlist the two XAResources in the global tx. (Assuming you're using UT)

You then send a message (using XAResource1) and insert a row in your db (using XAResource2)

You then commit the UT. (Or let the app server do it for you)

(I'm going to simplify the following a little, but the basics are correct - I don't want to go through the logging the transaction manager will do (if you are using JBoss TS) - since it's not relevant to the question.)

The transaction manager calls "prepare" on XAResource1. This tells the XAResource that it must log the work done in the tx, so it can be recovered in case of failure after the prepare.

Currently JBM uses the database to log at the prepare stage - so we write the message into the database with state "prepared" - this is done with a single JDBC transaction (if there were more than one messages sent in the same tx - they would all be written in the same JDBC tx). XAResource1 returns "OK"

(Eventually we will log using JBoss TS - but that is a different story).

The app server then calls prepare on XAResource2 - what happens now depends on the implementation of your database. But assuming it supports XA properly, then *somehow* it will write and sync *something* to disc that enables it to redo the operation at a later state. XAResource2 returns "OK"

Since both resources have returned OK, the app server will go to the commit phase.

First it calls "commit" on XAResource1. JBM already has the data for the message send in the database, so we just update it's status to "committed". This happens in a single JDBC transaction - note we do not need an XA capable database to do this - it's a straightforward non XA database update.

Next, "commit" is called onn XAResource2. The database doesn't something internal to its implementation that results in the data ending up in the committed state.

So as you can see there is no need for JBM to need an XA capable database in order to provide a fully functioning XAResource.

This is by design:

a) We can't assume the user has an XA capable database
b) Eventually we can't assume they have a database at all (think file based persistent stores)
c) We don't want to rely on the (often ropy) xa implementation of another product over which we have no control to provide such a critical feature.

"felipeal" wrote:

That's what I did initially, and I thought JBoss would (by default) manage to enroll the 2 local transactions (DB and JMS) in a global/XA transaction. But once I deployed the application, the MDBs started to consume the messages before the global tx committed, so I realized something was not set correctly...

It definitely should work. If it doesn't please file a bug report.

"felipeal" wrote:

Thanks again for you help (and patience :-)

No problem

[felipeal]

Hi again,

First of all, thanks for your quick reply and sorry for the delay on mine's, but the link I was refreshing didn't show your reply, neither did I get an email notifying me about it...

Anyway, your explanation was very good - even though I already knew most of the 2PC/XA stuff (and was more interested to know how JBoss Messaging could participate on a global transaction using a non-XA datasource), it was a very explanation to get logged in the forum :-)

Now, here are a couple more comments...

"timfox" wrote:

Ok, so in a nutshell this is what happens when use a JBoss Messaging (you meant JBoss Messaging not JBoss MQ, right?)

I thought it was still called JBossMQ and that Jboss Messaging was the name of the next generation's...

"timfox" wrote:

and you are using an JCA XA datasource (this one *does* need to do be XA since you

Hmm, that brings the problem back to its roots: how to get a XADatasource from MySQL 5.0 - I will talk more about this issue later on.

"timfox" wrote:

are using the XA capabilities of your database - make sure your db does have XA capabilities! Some don't)

The MySQL 5 + Connector/J 5.1 combo is supposed to XA compliant; at least I can run the following commands in the mysql console:

xa start 'a';
// issues command
xa end 'a';
xa prepare 'a';
xa commit 'a'; // or xa rollback 'a'

"timfox" wrote:

through the logging the transaction manager will do (if you are using JBoss TS) - since it's not relevant to the question.)

Agreed...

"timfox" wrote:

(Eventually we will log using JBoss TS - but that is a different story).

By JBoss TS do you meand the current stuff or Arjuna's ?

"timfox" wrote:

The app server then calls prepare on XAResource2 - what happens now depends on the implementation of your database. But assuming it supports XA properly,

Funny, it worked on MySQL, even though I didn't use a XAResource. I mean, my DB connection is configured as <local-tx> instead of <xa-datasource> (in fact, it's the same local-tx datasource used by the JMS RA).

"timfox" wrote:

in the database, so we just update it's status to "committed". This happens in a single JDBC transaction

My guess is that the only drawback happens when this simple transaction fails (for instance, due to a lock or error in the database), i.e., from the point of the view of the global transaction, the JMS transaction branch is committed (as the prepare phase returned ok), so somehow JBM or JBTS will have to guarantee the messages are written in the database).

"timfox" wrote:

Next, "commit" is called onn XAResource2. The database doesn't something internal to its implementation that results in the data ending up in the committed state.

The database could also have an issue doint that internal stuff, so the above comment is not that relevant...

"timfox" wrote:

So as you can see there is no need for JBM to need an XA capable database in order to provide a fully functioning XAResource.
This is by design:

That was a clever - and simple - design. Even considering the drawback I mentioned above , as it would be a problem even if JBM relied on a XA-capable datasource - the only difference is that JBM would be 'delegating away' the issue to the database...

"timfox" wrote:

It definitely should work. If it doesn't please file a bug report.

Yes it worked, even more than it should (as the connection to my DB is not XA). The only issue I had was how to get an reference to the javax:/JmsXA from Spring, but that's another problem (basically, I was getting a "not bound exception' when the BeanFactory tried to get the object from JNDI, at the webapp startup; but if I did a lookup on the servlet method, it worked. My guess is this is either a Spring bug or JBoss does not make that object visible to my webapp on startup).

Now, back to the original MySQL issue, the problem still remains: how to configure a XA datasource for a MySQL database on JBoss. I know this is not exactly an issue for the JBoss Messaging forum, but using JBM is the easiest way to reproduce the problem: it's just a matter of setting the JMS xml files to use such datasource and waiting to JBoss to fail on startup... I will fill a Jira issue regarding it (and explaining the whole situation), ok?

Thanks again,

-- Felipe

[timfox]

"felipeal" wrote:
Hi again, ...
I thought it was still called JBossMQ and that Jboss Messaging was the name of the next generation's...

JBoss Messaging is the new generation JMS provider (http://www.jboss.com/products/messaging) - it's already in 1.0.
JBossMQ is the old JMS provider that is still the default JMS provider for JBoss 4.x.
JBoss Messaging will be the default JMS provider in JBoss 5.x and above.
This forum is about JBoss Messaging, there is another forum that handles JBossMQ queries.

"felipeal" wrote:

By JBoss TS do you meand the current stuff or Arjuna's ?

JBoss Transactions (JBoss TS) is the new name for what used to be called Arjuna transactions (I think) until JBoss bought it.

JBoss TS will be the default transaction manager in JBoss 5, and I think can already be used with JBoss 4.x. I would post a question in the JBoss TS forum to get clarification on this.

"felipeal" wrote:

Funny, it worked on MySQL, even though I didn't use a XAResource. I mean, my DB connection is configured as <local-tx> instead of <xa-datasource> (in fact, it's the same local-tx datasource used by the JMS RA).

I suspect the JCA adaptor may be doing some kind of last resource optimisation here. Best post in the JCA forum - they should be able to help you since I'm not an expert in the JCA area.

"felipeal" wrote:

My guess is that the only drawback happens when this simple transaction fails (for instance, due to a lock or error in the database), i.e., from the point of the view of the global transaction, the JMS transaction branch is committed (as the prepare phase returned ok), so somehow JBM or JBTS will have to guarantee the messages are written in the database).

The messages will already have been written in the prepare stage, so we can guarantee the messages are there. If commit fails then typically this will be a transitory reason e.g. no network connection, db server is down. the transaction manager will retry periodically until it works. If there is a catastrophic failure e.g. bomb in data centre then even 2pc won't help you.

"felipeal" wrote:

Now, back to the original MySQL issue, the problem still remains: how to configure a XA datasource for a MySQL database on JBoss. I know this is not exactly an issue for the JBoss Messaging forum, but using JBM is the easiest way to reproduce the problem: it's just a matter of setting the JMS xml files to use such datasource and waiting to JBoss to fail on startup... I will fill a Jira issue regarding it (and explaining the whole situation), ok?

Felipe- as to how to set up an XA datasource for Mysql - I would suggest you post a message in the JCA forum - they may have some insight here.

[felipeal]

"timfox" wrote:

JBossMQ is the old JMS provider that is still the default JMS provider for JBoss 4.x.
JBoss Messaging will be the default JMS provider in JBoss 5.x and above.

Ok, I wasn't that wrong then, as I use JBoss 4.0.4 without changing anything (hence it uses JBossMQ).

"timfox" wrote:

I suspect the JCA adaptor may be doing some kind of last resource optimisation here.

Yes, that might be a possible cause. As much as it is working, I'm not that worried (yet :-).

"timfox" wrote:

until it works. If there is a catastrophic failure e.g. bomb in data centre then even 2pc won't help you.

Agreed - that's why I said the problem would still exist even if JBM depended on a XA-capable datasource.

"timfox" wrote:

Felipe- as to how to set up an XA datasource for Mysql - I would suggest you post a message in the JCA forum - they may have some insight here.

Ok, I will - and thanks again for your help/patience...

-- Felipe

[vakuthota]

I created the xa-datasource for my sql, but it does not rollback the transaction in case of failure....

I am using MySql5.0.20a and connection/J 5.0.2.

Please help me ?