Bug 1248732

Summary: The Camel SQL component used by SwitchYard's SQL binding does not close connections due to [CAMEL-8715] bug
Product: [JBoss] JBoss Fuse Service Works 6 Reporter: Musharraf Hussain <mhussain>
Component: SwitchYardAssignee: Rob Cernich <rcernich>
Status: NEW --- QA Contact: Matej Melko <mmelko>
Severity: high Docs Contact:
Priority: high    
Version: 6.0.0 GACC: soa-p-jira
Target Milestone: ---   
Target Release: ---   
Hardware: All   
OS: All   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

Description Musharraf Hussain 2015-07-30 17:44:02 UTC 
Description of problem:
- The Camel SQL component used by SwitchYard's SQL binding does not close connections due to [https://issues.apache.org/jira/browse/CAMEL-8715] bug highlighted in Camel community. This causes the following WARN messages to be logged in the underlying EAP 6.1.1 server.log file .
~~~
17:46:00,753 INFO  [stdout] (Camel (camel-3) thread #4 - timer://V1CamelSqlBindingModel/GreetingService@_GreetingService_sql_1#-702531588) Consumed [id: 28] from Rob to Lukasz 
17:46:01,756 WARN  [org.jboss.jca.adapters.jdbc.WrappedConnection] (Camel (camel-3) thread #4 - timer://V1CamelSqlBindingModel/GreetingService@_GreetingService_sql_1#-702531588) Closing a result set you left open! Please close it yourself.: java.lang.Throwable: STACKTRACE
	at org.jboss.jca.adapters.jdbc.WrappedStatement.registerResultSet(WrappedStatement.java:1357)
	at org.jboss.jca.adapters.jdbc.WrappedStatement.getResultSet(WrappedStatement.java:740)
	at org.apache.camel.component.sql.SqlProducer$1.doInPreparedStatement(SqlProducer.java:80) [camel-sql-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.component.sql.SqlProducer$1.doInPreparedStatement(SqlProducer.java:48) [camel-sql-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:614)
	at org.apache.camel.component.sql.SqlProducer.process(SqlProducer.java:48) [camel-sql-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.util.AsyncProcessorConverterHelper$ProcessorToAsyncProcessorBridge.process(AsyncProcessorConverterHelper.java:61) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.SendProcessor$2.doInAsyncProducer(SendProcessor.java:122) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.impl.ProducerCache.doInAsyncProducer(ProducerCache.java:298) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:117) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:99) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:73) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:99) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.interceptor.TraceInterceptor.process(TraceInterceptor.java:91) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:99) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.fabric.FabricTraceProcessor.process(FabricTraceProcessor.java:81) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:99) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.RedeliveryErrorHandler.processErrorHandler(RedeliveryErrorHandler.java:334) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:220) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.RouteContextProcessor.processNext(RouteContextProcessor.java:46) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.interceptor.DefaultChannel.process(DefaultChannel.java:308) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.Pipeline.process(Pipeline.java:117) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.Pipeline.process(Pipeline.java:80) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.RouteContextProcessor.processNext(RouteContextProcessor.java:46) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.UnitOfWorkProcessor.processAsync(UnitOfWorkProcessor.java:150) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.UnitOfWorkProcessor.process(UnitOfWorkProcessor.java:117) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.RouteInflightRepositoryProcessor.processNext(RouteInflightRepositoryProcessor.java:48) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:73) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.processNext(DelegateAsyncProcessor.java:99) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:90) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:73) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.util.AsyncProcessorHelper.process(AsyncProcessorHelper.java:99) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.processor.DelegateAsyncProcessor.process(DelegateAsyncProcessor.java:86) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.component.timer.TimerConsumer.sendTimerExchange(TimerConsumer.java:135) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at org.apache.camel.component.timer.TimerConsumer$1.run(TimerConsumer.java:63) [camel-core-2.10.0.redhat-60024.jar:2.10.0.redhat-60024]
	at java.util.TimerThread.mainLoop(Timer.java:555) [rt.jar:1.7.0_60]
	at java.util.TimerThread.run(Timer.java:505) [rt.jar:1.7.0_60]
~~~

Although this WARN message is just for your information purposes only where the "track-statements" feature of the datasource configuration in EAP 6 is closing the unclosed connections from your database for you as it was not closed after being used. 
~~~
...
                <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
                    <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>
                    <driver>h2</driver>
                    <security>
                        <user-name>sa</user-name>
                        <password>sa</password>
                    </security>
                    <statement>
                        <track-statements>true</track-statements>
                    </statement>
                </datasource>
...
~~~

But yes, it is a valid concern that Camel should attempt to close it properly from itself to avoid it. Perhaps a solution could be to upgrade the existing "jboss-eap-6.1/modules/system/layers/soa/org/apache/camel/sql/main/camel-sql-2.10.0.redhat-60024.jar" jar to any version equal or higher than " 2.14.3, 2.16.0, 2.15.3 " as this Camel community JIRA [https://issues.apache.org/jira/browse/CAMEL-8715] outlines.

Please look into this issue to upgrade the camel-sql component which we ship.


Version-Release number of selected component (if applicable):
- FSW 6.0.0

How reproducible:
- Always

Steps to Reproduce:
1. First enable the "track-statements" property inside the "java:jboss/datasources/ExampleDS" configuration of your FSW 6.0.0 installation.
~~~
...
                <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
                    <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>
                    <driver>h2</driver>
                    <security>
                        <user-name>sa</user-name>
                        <password>sa</password>
                    </security>
                    <statement>
                        <track-statements>true</track-statements>
                    </statement>
                </datasource>
...
~~~

2. Run the "camel-sql-binding" quickstart of FSW 6.0.0 where it replicates the same exception as shown below using "H2" database.
3. Check the server.log file.

Actual results:
- The WARN message "Closing a result set you left open! Please close it yourself" is logged in server.log file.

Expected results:
- Camel SQL component should close the database connections from it's own.

Additional info:
- Nothing