Wednesday, October 8, 2008

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.net.SocketException
MESSAGE: Software caused connection abort: recv failed
STACKTRACE:
java.net.SocketException: Software caused connection abort: recv failed


Environment: Apache Tomcat 6.0.14 with MySQL 5.2

This is the exception which was the night mare for many developers who used MySQL with Java Web application in Tomcat server. Before explaining the cause let me told you that, the exception has responsible for pulling my hair for almost 2 weeks, with little use of Googling and MySQL Forum!

Particularly, the problem was, I lost my connection object when server is idle for 24 to 34 hours, so any request comes after this time interval results in above exception.

Disclaimer: The below explanation is as per my understanding and as I’m not an architecture of MySQL JConnector nor Apache DBCP API, Comments & Suggestions are welcomed, See this link from where I derived my explanation

This exception, in my case, comes when a connection has opened by MySQL on the request of Apache DBCP, but which is no more (valid) connected to actual Data Source!! So that when we close the connection by
connection.close();
its actually closes the resource opened with Apache DBCP source , but not with MySQL instanced connection , so that connection from MySQL to DBCP source remained in memory for the time_out period(If this time elapsed the connection get automatically closed by MySQL, its an environment variable).
So there is two connection opened, one from application to DBCP and another from DBCP to MySQL (TCP/IP link). See for diagram



Now, the solution part,


The problem is underlying structure of Apache DBCP code, which actually opens the connection with MySQL, but we can’t forced or tell the DBCP to close the connection, but we can take an working, valid connection from server, by telling them to validate it before returning new connection from Data Source !

For that, we have to pass some attribute to server!

So your context.xml looks like,



<context>
<Resource auth="Container"
driverClassName="com.mysql.jdbc.Driver"
maxActive="5"
maxIdle="1"
maxWait="10000"
name="jdbc/TestDS"
username="root"
password="password"
type="javax.sql.DataSource"
url="jdbc:mysql://127.0.0.1:3306/TestDS?jdbcCompliantTruncation=false&elideSetAutoCommits=true&useLocalSessionState=true&maintainTimeStats=false""
removeAbandoned="true"
logAbandoned="true"
removeAbandonedTimeout="3600"
validationQuery="SELECT 1"
testOnBorrow="true"
testOnReturn="false"
testWhileIdle="true"
timeBetweenEvictionRunsMillis="1000"
minEvictableIdleTimeMillis="60000"
poolPreparedStatements="true"
maxOpenPreparedStatements="10"/>
</context>



The explanation of above attribute can be viewed here:
http://commons.apache.org/dbcp/configuration.html

This will solve the above exception !!

2 comments:

Oliver said...

You save my day! i pulled my hair for 1 week. =)

SagaR said...

Hi Oliver, its nice to know that it "finally" helps you.. ;)