[jboss-user] [Installation, Configuration & DEPLOYMENT] - Multiple data sources when using MySQL

[cpslo1999]

I got this piece of advice in response to a question I asked in another thread: http://www.jboss.org/index.html?module=bb&op=viewtopic&t=154018 It seemed to warrant its own discussion...

"PeterJ" wrote : Well, one best practice is to definitely not use DefaultDS for your apps. Leave the JBoss AS services with DefaultDS (though you should use a database other than Hypersonic for it), and each of your apps should use their own data sources, unless the apps are accessing the same data in which case they would want to use the same datasource. Actually, if the two apps are accessing the same data you probably really want one set of EJBs accessing the database with two web apps using that one set of EJBs.

I spent the afternoon working on this, and apparently it is not common with MySQL as the database as there is very little information to be found on how to configure JBoss.

I started by creating a second schema (database) in MySQL to house the JBoss infrastructure tables (both are in the same server instance localhost:3306).

The newbie approach (which is where I started) was to use the mysql-ds.xml file found in the documents/examples/jca directory and replicate the datasource XML, rename the copy, update the URL, etc. to get my second data source. This approach appeared to work until I loaded my app and tried to register a timer which failed with a very nice stack trace. After some searching, I found out that it failed because there are two local-tx data sources, which doesn't work since JBoss is attempting to use a 2PC. The timer was using one 1PC source, while my beans were using a different 1PC source.

The next approach was to try to configure both as XA data sources. I couldn't find any examples in the JBoss 5.0.1.GA installation for MySQL, but after some searching on the net I found some XML that seemed passable. I configured both to be XA data sources and deployed my app. When I ran my regression tests, I found that my application was failing because I had a DROP TABLE statement as part of a SQL batch. Apparently DROP (as well as a hand full of other SQL commands) don't play well in MySQL using XA data sources, since they create implied transaction boundaries and that is bad when you are within a XA transaction.

So, I end the day with a few big questions:
1) How important is it to have JBoss infrastructure use the DefaultDS while applications use something else? (Stated in another way, what advantage does this configuration provide?)
2) Is this a supported (as in tried and tested, not as in paid support) configuration using MySQL and JBoss 5.0?
3) Is there really a need to use XA data sources when one source is for JBoss infrastructure and the other is for my application?
4) Is there another solution that I have overlooked or may be otherwise unaware of?

Thanks,

Josh

View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4225518#4225518

Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=4225518