search

manirathnamgowd / soi-toolkit

Automatically exported from code.google.com/p/soi-toolkit
0 stars 0 forks source link

Add support for pooled XA JDBC Datasource #257

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago 
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-concurren
cy

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
- In getConfigResources() replace 
"soitoolkit-mule-jdbc-xa-datasource-hsql-embedded.xml" with 
"soitoolkit-mule-jdbc-xa-datasource.xml".
- In doSetUpDb() avoid executing the setup scripts (Derby donut like ";", 
should be fixed!!!), e.g. add:
   if (dataSource != null) return;      

8. 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-sprin
g-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-sprin
g-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.ja
r!/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.ja
r!/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-S
A-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-S
A-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-S
A-mule321/target/test-classes/Oneway-Tests-SA-mule321-integrationtests-common.xm
l]
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-S
A-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-S
A-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    - 
**********************************************************************
* Mule ESB and Integration Platform                                  *
* Version: 3.2.1 Build: 23396                                        *
* MuleSoft, Inc.                                                     *
* For more information go to http://www.mulesoft.org                 *
*                                                                    *
* Server started: 2012-05-18 09:32                                   *
* Server ID: 8c32bff8-a0bb-11e1-ac1b-7585c1591bc0                    *
* JDK: 1.6.0_31 (mixed mode)                                         *
* OS encoding: MacRoman, Mule encoding: UTF-8                        *
* OS: Mac OS X (10.7.4, x86_64)                                      *
* Host: MagnusMac-2.local (10.0.1.14)                                *
*                                                                    *
* Agents Running:                                                    *
*   JMX Agent                                                        *
**********************************************************************
2012-05-18 09:32:01,573 INFO  
org.soitoolkit.commons.mule.test.AbstractJmsTestUtil - Clearing messages on 
queues: ONEWAYTESTSSAMULE321.JMSTOJDBC.IN.QUEUE 
DLQ.ONEWAYTESTSSAMULE321.JMSTOJDBC.IN.QUEUE 
2012-05-18 09:32:01,804 INFO  
org.soitoolkit.commons.mule.test.AbstractJmsTestUtil - Clearing messages on 
queues: SOITOOLKIT.LOG.ERROR 
2012-05-18 09:32:02,527 WARN  org.apache.tomcat.jdbc.pool.ConnectionPool - 
minIdle is larger than maxActive, setting minIdle to: 2
2012-05-18 09:32:02,529 WARN  org.apache.tomcat.jdbc.pool.ConnectionPool - 
maxIdle is larger than maxActive, setting maxIdle to: 2
2012-05-18 09:32:02,544 WARN  com.arjuna.ats.arjuna.coordinator.TxControl - 
ARJUNA12132: Supplied name of node contains reserved character -. Using 
Arjuna:53156
2012-05-18 09:32:02,699 INFO  
org.soitoolkit.commons.mule.log.DefaultEventLogger - soi-toolkit.log
** logEvent-info.start 
***********************************************************
IntegrationScenarioId=
ContractId=
LogMessage=msg-in
ServiceImpl=jmsToJdbc-service
Host=MagnusMac-2.local (10.0.1.14)
ComponentId=8c32bff8-a0bb-11e1-ac1b-7585c1591bc0
Endpoint=jms://ONEWAYTESTSSAMULE321.JMSTOJDBC.IN.QUEUE
MessageId=8f9e4bbf-a0bb-11e1-ac1b-7585c1591bc0
BusinessCorrelationId=
BusinessContextId=
ExtraInfo=
Payload=
** logEvent-info.end 
*************************************************************
2012-05-18 09:32:02,761 INFO  
org.soitoolkit.commons.mule.log.DefaultEventLogger - soi-toolkit.log
** logEvent-info.start 
***********************************************************
IntegrationScenarioId=
ContractId=
LogMessage=msg-out
ServiceImpl=jmsToJdbc-service
Host=MagnusMac-2.local (10.0.1.14)
ComponentId=8c32bff8-a0bb-11e1-ac1b-7585c1591bc0
Endpoint=jdbc://jmstojdbc-import-query
MessageId=8f9e4bbf-a0bb-11e1-ac1b-7585c1591bc0
BusinessCorrelationId=
BusinessContextId=
ExtraInfo=
Payload=
** logEvent-info.end 
*************************************************************
2012-05-18 09:32:02,921 ERROR org.mule.exception.AbstractExceptionStrategy - 
Caught exception in Exception Strategy: No current connection. Query: SELECT 
ID, VALUE FROM JMSTOJDBC_IMPORT_TB ORDER BY ID Parameters: []
java.sql.SQLException: No current connection. Query: SELECT ID, VALUE FROM 
JMSTOJDBC_IMPORT_TB ORDER BY ID Parameters: []
    at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:540)
    at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:397)
    at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:369)
    at org.mule.transport.jdbc.JdbcMessageReceiver.executeReadStatement(JdbcMessageReceiver.java:296)
    at org.mule.transport.jdbc.JdbcMessageReceiver.getMessages(JdbcMessageReceiver.java:261)
    at org.mule.transport.TransactedPollingMessageReceiver.poll(TransactedPollingMessageReceiver.java:143)
    at org.mule.transport.AbstractPollingMessageReceiver.performPoll(AbstractPollingMessageReceiver.java:219)
    at org.mule.transport.PollingReceiverWorker.poll(PollingReceiverWorker.java:85)
    at org.mule.transport.PollingReceiverWorker.run(PollingReceiverWorker.java:53)
    at org.mule.work.WorkerContext.run(WorkerContext.java:310)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:680)
2012-05-18 09:32:03,013 ERROR org.mule.exception.AbstractExceptionStrategy - 
Caught exception in Exception Strategy: No current connection. Query: SELECT 
ID, VALUE FROM JMSTOJDBC_IMPORT_TB ORDER BY ID Parameters: []
java.sql.SQLException: No current connection. Query: SELECT ID, VALUE FROM 
JMSTOJDBC_IMPORT_TB ORDER BY ID Parameters: []
    at org.apache.commons.dbutils.QueryRunner.rethrow(QueryRunner.java:540)
    at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:397)
    at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:369)
    at org.mule.transport.jdbc.JdbcMessageReceiver.executeReadStatement(JdbcMessageReceiver.java:296)
    at org.mule.transport.jdbc.JdbcMessageReceiver.getMessages(JdbcMessageReceiver.java:261)
    at org.mule.transport.TransactedPollingMessageReceiver.poll(TransactedPollingMessageReceiver.java:143)
    at org.mule.transport.AbstractPollingMessageReceiver.performPoll(AbstractPollingMessageReceiver.java:219)
    at org.mule.transport.PollingReceiverWorker.poll(PollingReceiverWorker.java:85)
    at org.mule.transport.PollingReceiverWorker.run(PollingReceiverWorker.java:53)
    at org.mule.work.WorkerContext.run(WorkerContext.java:310)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:680)
2012-05-18 09:32:17,132 ERROR org.mule.exception.AbstractExceptionStrategy - 
********************************************************************************
Message               : Pool wait interrupted. (java.sql.SQLException)
Code                  : MULE_ERROR--2
--------------------------------------------------------------------------------
Exception stack is:
1. null (java.lang.InterruptedException)
  java.util.concurrent.locks.AbstractQueuedSynchronizer:1024 (null)
2. Pool wait interrupted.(SQL Code: 0, SQL State: + null) 
(java.sql.SQLException)
  org.apache.tomcat.jdbc.pool.ConnectionPool:638 (null)
3. Pool wait interrupted. (java.sql.SQLException) 
(org.mule.transport.ConnectException)
  org.mule.transport.jdbc.JdbcConnector:251 (http://www.mulesoft.org/docs/site/current3/apidocs/org/mule/transport/ConnectException.html)
--------------------------------------------------------------------------------
Root Exception stack trace:
java.lang.InterruptedException
    at java.util.concurrent.locks.AbstractQueuedSynchronizer.doAcquireSharedNanos(AbstractQueuedSynchronizer.java:1024)
    at java.util.concurrent.locks.AbstractQueuedSynchronizer.tryAcquireSharedNanos(AbstractQueuedSynchronizer.java:1303)
    at java.util.concurrent.CountDownLatch.await(CountDownLatch.java:253)
    + 3 more (set debug level logging or '-Dmule.verbose.exceptions=true' for everything)
********************************************************************************

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    - 
**********************************************************************
* Mule Context shut down normally on: 2012-05-18 09:32               *
* Server was up for: 0 days, 0 hours, 0 mins, 21.37 sec              *
**********************************************************************

Original issue reported on code.google.com by magnus.l...@gmail.com on 18 May 2012 at 8:00

Attachments:

GoogleCodeExporter commented 9 years ago 
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.

Original comment by magnus.l...@gmail.com on 18 May 2012 at 12:45

GoogleCodeExporter commented 9 years ago 
Move non-high-priority v0.5.1 issues to v0.6.1

Original comment by magnus.l...@gmail.com on 26 Jul 2012 at 4:27

GoogleCodeExporter commented 9 years ago 
See interesting blog on the subject: 
http://ricston.com/blog/?p=3222&goback=.gde_958797_member_252518932

Original comment by magnus.l...@gmail.com on 25 Jun 2013 at 11:37

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

Original comment by magnus.l...@callistaenterprise.se on 4 Oct 2013 at 11:05

GoogleCodeExporter commented 9 years ago

Original comment by hakan.dahl@gmail.com on 23 Feb 2014 at 3:25