search

soi-toolkit / soi-toolkit-mule

SOI Toolkit and Mule ESB
Apache License 2.0
6 stars 1 forks source link

Add support for pooled XA JDBC Datasource #257

Open hdahl opened 9 years ago

hdahl commented 9 years ago

Original issue 257 created by soi-toolkit on 2012-05-18T08:00:21.000Z:

The current JDBC Connection pool implementation in soi-toolkit, Apache Commons DBCP, does not support XA-connections.

An alternative could be Tomcat's Connection Pool: http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html For details see: http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency

Some initial tests indicates problems with errors like: "No current connection" Use of non pooled XA JDBC Connections works fine.

Use of Tomcat's Connection Pool with XA enabled DataSources outside Mule seems to be ok, e.g. if the attached XA DataSource config is used in the spring-config example pointed out by in the blog above: http://people.apache.org/~fhanik/jdbc-pool/spring-jdbc-pool.zip

To repeat the initial tests do the following:

  1. Add dependencies to pom.xml: (should be placed in soitoolkit-mule-n.n.n-dependencies/pom.xml)

    <dependency> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-jdbc</artifactId> <version>7.0.27</version> <exclusions> <exclusion> <groupId>org.apache.tomcat</groupId> <artifactId>tomcat-juli</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>org.apache.tomcat.extras</groupId> <artifactId>tomcat-extras-juli</artifactId> <version>7.0.27</version> </dependency> <dependency> <groupId>org.apache.tomcat.extras</groupId> <artifactId>tomcat-extras-juli-adapters</artifactId> <version>7.0.27</version> </dependency>

  2. In soitoolkit-mule-jdbc-datasource.xml replace use of DBCP with Tomcat Pool:

    <spring:bean id="soitoolkit-jdbc-datasource" class="org.apache.commons.dbcp.BasicDataSource"> --> <spring:bean id="soitoolkit-jdbc-datasource" class="org.apache.tomcat.jdbc.pool.DataSource">

  3. Remove DBCP dependency in (should be placed in soitoolkit-mule-n.n.n-dependencies/pom.xml) <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> <dependency> <groupId>net.sourceforge.jtds</groupId> <artifactId>jtds</artifactId> <version>1.2.4</version> </dependency>
  4. Add a declaration of a XA enabled DataSource using Tomcal Pool for connection pooling. See attached file soitoolkit-mule-jdbc-xa-datasource.xml.
  5. Generate a jms to jdbc flow
  6. Run tests to ensure that everything works fine
  7. Update integration test, JmsToJdbcIntegrationTest.java
  8. In getConfigResources() replace "soitoolkit-mule-jdbc-xa-datasource-hsql-embedded.xml" with "soitoolkit-mule-jdbc-xa-datasource.xml".
  9. In doSetUpDb() avoid executing the setup scripts (Derby donut like ";", should be fixed!!!), e.g. add: if (dataSource != null) return;
  10. Start Derby and create the tables in the ${IC}-db-create.tables.sql file by hand (due to the ";" problem above...)

9 Rerun the tests and they should now fail with a log like:

= Testing: testJmsToJdbc_ok =

2012-05-18 09:31:57,215 INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from URL [jar:file:/Users/magnuslarsson/.m2/repository/org/mule/modules/mule-module-spring-config/3.2.1/mule-module-spring-config-3.2.1.jar!/mule-spring-config.xml] 2012-05-18 09:31:57,533 INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from URL [jar:file:/Users/magnuslarsson/.m2/repository/org/mule/modules/mule-module-spring-config/3.2.1/mule-module-spring-config-3.2.1.jar!/default-mule-config.xml] 2012-05-18 09:31:58,150 INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from URL [jar:file:/Users/magnuslarsson/.m2/repository/org/soitoolkit/commons/components/soitoolkit-commons-mule/0.5.1-SNAPSHOT/soitoolkit-commons-mule-0.5.1-SNAPSHOT.jar!/soitoolkit-mule-jms-xa-connector-activemq-embedded.xml] 2012-05-18 09:31:58,548 INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [soitoolkit-mule-jms-connector-commons.xml] 2012-05-18 09:31:58,938 INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from URL [jar:file:/Users/magnuslarsson/.m2/repository/org/soitoolkit/commons/components/soitoolkit-commons-mule/0.5.1-SNAPSHOT/soitoolkit-commons-mule-0.5.1-SNAPSHOT.jar!/soitoolkit-mule-jdbc-xa-datasource.xml] 2012-05-18 09:31:59,017 INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from URL [file:/Users/magnuslarsson/Documents/temp/_plugintests/jUnitTests/Oneway-Tests-SA-mule321/target/classes/Oneway-Tests-SA-mule321-jdbc-connector.xml] 2012-05-18 09:31:59,112 INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from URL [file:/Users/magnuslarsson/Documents/temp/_plugintests/jUnitTests/Oneway-Tests-SA-mule321/target/classes/Oneway-Tests-SA-mule321-common.xml] 2012-05-18 09:31:59,288 INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from URL [file:/Users/magnuslarsson/Documents/temp/_plugintests/jUnitTests/Oneway-Tests-SA-mule321/target/test-classes/Oneway-Tests-SA-mule321-integrationtests-common.xml] 2012-05-18 09:31:59,400 INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from URL [file:/Users/magnuslarsson/Documents/temp/_plugintests/jUnitTests/Oneway-Tests-SA-mule321/target/classes/jmsToJdbc-service.xml] 2012-05-18 09:31:59,568 INFO org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from URL [file:/Users/magnuslarsson/Documents/temp/_plugintests/jUnitTests/Oneway-Tests-SA-mule321/target/test-classes/teststub-services/jmsToJdbc-teststub-service.xml] 2012-05-18 09:31:59,852 WARN org.springframework.core.io.support.PropertiesLoaderSupport - Could not load properties from class path resource [Oneway-Tests-SA-mule321-config-override.properties]: class path resource [Oneway-Tests-SA-mule321-config-override.properties] cannot be opened because it does not exist 2012-05-18 09:32:01,247 INFO org.mule.exception.AbstractExceptionStrategy - Initialising exception listener: org.soitoolkit.commons.mule.error.ServiceExceptionStrategy@173dcf55 2012-05-18 09:32:01,543 INFO org.mule.DefaultMuleContext -

2012-05-18 09:32:22,131 WARN org.mule.work.MuleWorkManager - Pool jdbcConnector.receiver did not terminate in time; No work items were cancelled. 2012-05-18 09:32:22,133 ERROR org.mule.exception.AbstractSystemExceptionStrategy - Already in lifecycle phase 'stop', cannot fire the same phase twice 2012-05-18 09:32:22,363 INFO org.mule.DefaultMuleContext -

hdahl commented 9 years ago

Comment #1 originally posted by soi-toolkit on 2012-05-18T12:45:08.000Z:

Issue 258 solved the problem with execution of sql-commands on a Derby-database so the fixes in step 7 for doSetUpDb() and in step 8 by manually creating tables are no longer required to reproduce the problem describe above.

hdahl commented 9 years ago

Comment #2 originally posted by soi-toolkit on 2012-07-26T16:27:32.000Z:

Move non-high-priority v0.5.1 issues to v0.6.1

hdahl commented 9 years ago

Comment #3 originally posted by soi-toolkit on 2013-06-25T11:37:19.000Z:

See interesting blog on the subject: http://ricston.com/blog/?p=3222&goback=.gde_958797_member_252518932

hdahl commented 9 years ago

Comment #4 originally posted by soi-toolkit on 2013-10-04T11:05:54.000Z:

A new potentially interesting blog regarding this subject: http://java.dzone.com/articles/xa-connection-pooling

hdahl commented 9 years ago

Comment #5 originally posted by soi-toolkit on 2014-02-23T15:25:07.000Z:

<empty>