Application Hung Caused by Corrupted Connection in DBCP Connection Pool

There was an incident where an unplanned network refresh causing the active connection object in the DBCP Connection Pool to lose the 'connection' to the corresponding database session and this resulted the application to hang upon getting connection from the pool.

This case was unique and was not reproducible due to no privilege to perform a network refresh intentionally. However I managed to reproduce this by intentionally killing the database sessions (Sql Server) while having SoapUI to fire the application continuously.

Solution
- via DBCP configuration. There are 2 solutions:

1. For Sql Server, it's fixed by setting "maxIdle" of the Apache DBCP as 0, the application always establishes a new connection upon new request.
2. For Oracle, what I observed from setting the "maxIdle=0" solution at local connecting to remote Oracle database was that the application was experiencing slowness upon getting connection from the DBCP pool. I did not want to compromise the application performance, therefore I tried another method which is to set the "maxWait" property, I set it at 500 milliseconds and at the same time had SOAPUI firing the application continuously. After killing the corresponding Oracle Connection Session, I noticed the application was able to create a new connection very soon, as opposed to previously the application would just be hung.

Tortoise Git SSH Auto-Authorization Issue

I'm using Tortoise Git as the Tortoise client to perform Git actions such as check modifications, clone, add, commit, push etc. via GUI. It's been a pain getting Tortoise to be able to be authorized automatically by the Git Serve.

Background

1. I've already had private key generated via MobaXTerm and stored in the Git machine's authorized_keys.
2. Now I want to enable the auto-authorization from TortoiseGit to the Git server upon performing actions such as Git Push. Therefore I need to let my Tortoise Git know the whereabout of the private key.
3. Here comes the problem, that specific private key has to be converted to Putty format. To do that, we use PuttyGen that is installed together with the Tortoise Git.
1. Conversations - Import Key (look for the specific Private Key)
2. Save private key to a location
4. Next, start Pageant which has also been installed by Tortoise Git installer. An icon will appear at the taskbar. Right click - > Add Key. Select the Private Key that you just created in previous step.

Apache DBCP Connection Pool Issue

I'm using this Apache DBCP for Connection Pool purpose. One thing I notice is that, every time there's a network failure in between my application and the database (Sql Server), yes it will generate Socket Write Failure/Error exception, and subsequent few calls would somehow cause the  getConnection() of BasicDataSource to freeze. 

I troubleshooted this by monitoring the Processes in the Activity Monitor in Sql Server. I saw there's always a Connection created each time I fired a request. That Connection would just stay, I think my application was using this particular Connection for multiple firing of requests. If I killed this particular Connection then fire a few more times, then I managed to reproduce the issue where the getConnection() was giving no response. 

After a lot of trial and error, finally I found the resolution - maxIdle of the BasicDataSource. The default maxIdle for BasicDataSource is 8, therefore I just need to set maxIdle as zero. This time, although I fired many many times , I did not see any Connection stays in the Process of Activity Monitor anymore! No more Connection Pool freeze! 

Camel-Context(DSL) only, No Coding! How to Log? How to Handle Error? How to Configure Retry? How to work out the Property Placeholder?

I'm excited to have found new way in Apache Camel to build specific feature without writing any single code, which has always been my way due to time constraint and flexibility (to make everything configurable and every exception handled properly). It was a long winding process with many many rounds of trial and error.

What I'm trying to achieve here is very simple, just read file and SFTP to the other side and Backup the original Source File! If this would have been done in a Java/Groovy and registered as a Spring Bean, then it shall be very fast. However I chose to do it the Camel-way!

1. In your Camel-Context.xml (Spring DSL), declare a Route.  
2. The <From> should be the File EIP. Remember to set a "Delay" for the file polling interval. 
3. Next, use the SFTP EIP for the <To>

Done. So simple! However...

I want to LOG the entire process, such as:  

1. What Are the Files Being Picked Up? 
2. Whether the SFTP is Successful or Failed?  
3. Was the SFTPed file moved to a Backup folder? 

For my own knowledge, I was using 2 ways to log:

1. Camel's Log EIP - To log Route process + Error to the Application-specific log. 
2. Camel's Log - To log Error to the General Error log. 

**For Error, will be written to both logs. 

Camel's Log EIP 

<camel:log message="[MyTimer2] SFTP Begins ${file:name}" loggingLevel="DEBUG" logName="myTimer2Logger" />

**${file:name} here is a kind of Reserved Keyword inside Camel's. It's called the File Expression language and this specific one will output the name of the file picked up in the log.  

Camel's Log

<camel:to uri="log:error-rollingFileAppender?level=ERROR&amp;showCaughtException=true&amp;showStackTrace=true" />

** First Parameter (in this case - error-rollingFileAppender) refers to the Appender setup in my Application's LogBack.xml

Question, where should I place my LOG Error Block? I should only LOG Error upon Exception right? 

First, I tried the <DoTry> - <DoCatch> way, I put my LOG Error function within <DoCatch>. However, there is a flaw here! Upon SFTP Exception, the File:// will still regard the SFTP as successful and continued to move file to the Backup folder. 

Finally I found the right way, use <OnException>! So just put the Log Error block inside your <OnException>, which is inside the <Route>! 

<camel:exception>java.lang.Exception</camel:exception>
<camel:to uri="log:error-rollingFileAppender?  level=ERROR&amp;showCaughtException=true&amp;showStackTrace=true" />
<camel:log message="[MyTimer2] File Transmission Error ${exception.stacktrace}" loggingLevel="ERROR" logName="myTimer2Logger" />
</camel:onException> 

Is that all? Nope.

I wanted to Retry for a fixed number of times upon any kind of Exception! 

Easy. Just declare the below:

<camel:redeliveryPolicyProfile id="myRedelivery" retryAttemptedLogLevel="ERROR" maximumRedeliveries="${H2MaxRetry}" redeliveryDelay="${H2RetryDelay}" />

Then add this as the "redeliveryPolicyRef" to your Route.

Last but not least, I wanted to set all the properties, such as the Input Directory, the SFTP host, username, password and etc in a single properties file! 

Here's the trick:

Use <Endpoint> for your File:// and SFTP:// 

Somehow only Endpoint allows Spring Property Placeholder! 

<camel:endpoint id="send_source" uri="file://${app.dir}/filewatcher/anz_output?delay=${myTimer2}&amp;move=${app.dir}/filewatcher/anz_backup/$simple{file:name}.done&amp;moveFailed=${app.dir}/filewatcher/anz_bad"/>

<camel:endpoint id="send_remote" uri="sftp://${H2hostname}${H2Path}?username=${H2user}&amp;password=${H2password}&amp;soTimeout=${H2SocketTimeout}&amp;binary=${H2Binary}"/>

**These properties placeholder are stored in the properties file configured at the PlaceholderConfigurer