MySql - ( Tomcat, Spring, C3P0) - Communication Link Failure Error & Explanation

This blog explains the famous mysql “communication Link Failure” error, and provide the working example of the code that works finally. The blog post is just a result of the all ideas & findings that I went through while looking the cause for this error on blogosphere and documentation. I have applied many techniques that are being mentioned, however none of the approach works finally and some R&D has resulted in the successfully behavior, as required.

Environment & Softwares:
Windows XP, Apache Tomcat 6.0.32, Spring 3.0.3, Hibernate 3.5, C3p0-0.9.11, MySql 5
Error:
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

Last packet sent to the server was 0 ms ago.

Reason: The error comes because the connections in our connection pool goes stale and mysql removes the connection also from his own internal pool through a thread which get invokes after WAIT_TIMEOUT as being configured in the MySQL Server.

Example: Suppose our web based application is being configured to take a fixed number of connections from the database at the start of the application, however as we progress through time and reached a limit where a connection in the pool has not been used more than WAIT TIMEOUT configured in the mysql server. If we hit any SQL/HQL statement through that pool, we are going to get the above error with a message “Last packet was sent to the server was x ms ago”.

Properties involed: Solution of the above problem will involve only three properties one on mysql side and two on the coding side (configured through application-dao.xml).

WAIT_TIMOUT (on mysql side, this can easily be configured through the mysql administrator and should be around 10 minutes, by default the value is 28800 which is very high, because the stale connection can remain in mysql pool for 8 hours). For Testing I reduced it to 30 seconds.
IDLECONNECTIONTESTPERIOD: This property of C3p0 specifies the after how much time the library should check for the validity of the connection in connection pool. This should be set less then the wait_timeout period configured above. For testing I set it to 20 seconds
MAXIDLETIME: This C3p0 property signifies how long an idle connection can remain in the pool. For testing I set it to 25 seconds.

NOTE: we always have to remember that the value of idleTestConnectionTestPeriod should be less then that of maxIdleTime and the value of both of these properties should be less than that of wait_timeout as being configured on the DB server.

However, while configuring the datasource  (C3P0 datasource) through the spring property files, we have to make sure, that we are setting these properties in C3p0 datasource and not in the session factory. The reason is plain simple unless you are using hibernate-c3p0 (check out for correct version of C3p0 for a specific Hibernate version) Provider, it is not going to work. For this you need to set the following property in the session factory “properties” attribute.
hibernate.connection.provider_class = org.hibernate.connection.C3P0ConnectionProvider
I tried to set this provider, however hibernate is not picking this up and still the internal Spring/Hibernate connection provider is being used instead of C3p0 provider. So have to drop this.

here is the final datasource configuration:

        
            com.mysql.jdbc.Driver
        
        
            jdbc:mysql://192.168.1.1:3306/testDB?autoReconnect=true
        
        
            
                root</beans:prop>
                <beans:prop key="password">mysqlpassword
            
        
        
            20
        
        
            25
        
    

In session factory you can set a number of attributes and verify whether these are being set or not by just going through the JConsole ( actually this solves my error, since I can check whether the properties are actually being set or not, in my case previously I am setting them in sessionFactory and assuming they will be used, however jConsole tell me that the ComboPooldataSource properties are not the same one as I specified in the xml file, that prompt me to set them in the ComboPooldataSource instead of specifying in the sessionFactory)


Here is the Configuration for hibernate SessionFactory:

        
            
 

            
 org.hibernate.dialect.MySQL5InnoDBDialect
true
                 
                10
                20
                20
                25
                10
                2
                true   
               select 1;
  
 

Test results: so finally when I set the above properties (remember properties defined in sessionFactory does not work for me because I don’t have the extra jar hibernate-c3p0.jar of 6KB and using it does not worked also).
Wait_timeout: 30
idleConnectionTestPeriod: 20
maxIdleTime: 25

If I hit the database now after a period of 30 seconds I never got the error “Communication Failure” because internally C3p0 verifies the validity of every connection in 20 seconds & if a connection lies unused or unchecked ie. Remain idle for 25 seconds, it will die out automatically. Thus I got my problem solved finally.

If you have to use the set of attributes in session factory instead of defining like this in ComboPoolDataSource please see the documentation of C3P0 and the associate hibernate property. Make sure you are associating every c3p0 property with “connection” or “hibernate” keywords as per the documentation, else see the JConsole to check whether you are able to set the property or not.

Here is a list of blogs that proved useful while searching for the solution of the problem and different things worked for different geeks.

http://hibernatedb.blogspot.com/2009/05/c3p0properties.html

http://amemon.wordpress.com/2007/07/15/monitoring-c3p0-using-jmxjconsole/

http://stackoverflow.com/questions/4104125/how-to-monitor-c3p0-connections

http://www.mchange.com/projects/c3p0/index.html#timeout

http://www.mchange.com/projects/c3p0/index.html#idleConnectionTestPeriod

http://www.codefin.net/2007/05/hibernate-and-mysql-connection-timeouts.html

http://www.mchange.com/projects/c3p0/#hibernate-specific