Communications link failure

Communications-link-failure-due-to-underlying-exception

I am getting this exception frequently on server console. I have increase MySql time out period to 8 Hr also but it doesn't work. After restart tomcat it work for some time again this exceptions come on console.
I am using Tomcat 6 with MySQL, and Torque.

Below is log print -

[11/21/10 12:02 AM WARNING] TorqueException while getting the new Connection org.apache.torque.TorqueException: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.io.EOFException
STACKTRACE:
java.io.EOFException
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1963)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2375)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2874)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3243)
at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:5371)
at
org.apache.commons.dbcp.cpdsadapter.ConnectionImpl.setAutoCommit(ConnectionImpl.java:326)
at org.apache.commons.dbcp.datasources.SharedPoolDataSource.setupDefaults(SharedPoolDataSource.java:227)
at org.apache.commons.dbcp.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:692)
at org.apache.commons.dbcp.datasources.InstanceKeyDataSource.getConnection(InstanceKeyDataSource.java:653)

Solution –

When a new transaction open, it try to use old connection which is expired in connection pool in that case this exception occur. There are multiple way be which your connection can killed by firewall, Third party security server etc. If one thread acquire connection and sleep for more than 8 HR (default connection timeout period for MySQL) then this exception occur if that thread woke up after 8 Hr and try to do transaction with database. 

There are two way for solution.

1. Reduce wait time period in connection pool and reclaim old connection in code. (In connection pool)
2. Reduce wait time period in Mysql. So that connection from MySQL will reclaim soon.

I tried below solution and it is working fine. Below are few steps and settings

 STEP-

a) Stop MySQL db server and application server (Tomcat or else).

b) Mysql settings: This should be done in "my.ini" file under mysql folder.

 wait_timeout =1200 (i.e 20 min – Keep same time of your firewall OR any other third party software)

You can set it DB level by above query. set  wait_timeout =1200;

View settings by above query Show variables like 'wait_timeout'  ;

c)  Add two properties in “Torque.properties” file.

#minEvictableIdleTimeMillis  A the time in millisecond a connection can stay idle in the pool 

        torque.dsfactory.csa.pool.minEvictableIdleTimeMillis = 1100000

 #timeBetweenEvictionRunsMillis A the time in millisecond between the run of the  eviction thread; must be greater than 0; default to -1

      torque.dsfactory.csa.pool.timeBetweenEvictionRunsMillis = 2000

        d) Start application and db server