Let me describe my question -
I have a Java application - Hibernate as the DB interfacing layer over MySQL. I get the communications link failure error in my application. The occurence of this error is a very specific case. I get this error , When I leave mysql server unattended for more than approximately 6 hours (i.e. when there are no queries issued to MySQL for more than approximately 6 hours). I am pasting a top 'exception' level description below, and adding a pastebin link for a detailed stacktrace description.
javax.persistence.PersistenceException:
org.hibernate.exception.JDBCConnectionException:
Cannot open connection
- Caused by: org.hibernate.exception.JDBCConnectionException:
Cannot open connection
- Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure
- The last packet successfully received from the server was
1,274,868,181,212 milliseconds ago.
The last packet sent successfully to
the server was 0 milliseconds ago.
- Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure
- The last packet successfully received from the server was
1,274,868,181,212 milliseconds ago.
The last packet sent successfully to
the server was 0 milliseconds ago.
- Caused by: java.net.ConnectException: Connection
refused: connect
the link to the pastebin for further investigation - http://pastebin.com/4KujAmgD
What I understand from these exception statements is that MySQL is refusing to take in any connections after a period of idle/nil activity. I have been reading up a bit about this via google search, and came to know that one of the possible ways to overcome this is to set values for c3p0 properties as c3p0 comes bundled with Hibernate. Specifically, I read from here http://www.mchange.com/projects/c3p0/index.html that setting two properties idleConnectionTestPeriod and preferredTestQuery will solve this for me. But these values dont seem to have had an effect.
Is this the correct approach to fixing this? If not, what is the right way to get over this?
The following are related Communications Link Failure questions at stackoverflow.com, but I've not found a satisfactory answer in their answers.
http://stackoverflow.com/questions/2121829/java-db-communications-link-failure
http://stackoverflow.com/questions/298988/how-to-handle-communication-link-failure
Note 1 - i dont get this error when I am using my application continuosly.
Note 2 - I use JPA with Hibernate and hence my hibernate.dialect,etc hibernate properties reside within the persistence.xml in the META-INF folder (does that prevent the c3p0 properties from working?)
edit -
Here are the c3p0 parameters I tried out -
select 1;
2
