MySQL Communications link failure due to underlying exception

Question

I am facing such annoying problem,
running on Tomcat 6 and MySQL using TomcatJDBC Lib.

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:1934)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2380)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3026)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1137)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1231)

After some time (20sh mins) this occurs, all connections to db seems to expire.

autoReconnect=true" 
validationQuery="Select 1"

Everything is set.
I do handle reconnect to database, and it works fine, but huge log files are bad, i cant either handle this exception to make it silent. Also this type of "fixing" isn't right solution. Is there any way to:
a) Handle this exception silently
b) Fix this issue (i do not have any firewall that i know of) (dont have access to my.cnf for mysql configuration)

Also having this problem on Tomcat 5 with Apache jConnector on totaly different application, without any firewalls that could drop a connection.

(Catching EOFException or CommunicationsException on method that does invoke connection doesn't help of course.)

Thanks in advance.

Answer 1

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