Thursday, July 28, 2011

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.




Tuesday, July 5, 2011

NetBeans SpringMVC (REST) Project Junit testing: Errors & Resolutions

This blog is a consolidated view of the problems that are being faced while executing JUNIT based test cases for a  Spring MVC based web project in Netbeans.
The project environment is:
1. NetBeans 6.9
2. JDK 6u13
3. Tomcat 6.0.32
4. Windows XP



Problems faced while executing Junit test cases:
1. Memory problems while executing test cases
2. A number of properties are being exposed with the help of catalina.properties like the proxy username and password need not to be hardcoded in the applicationcontext files & a number of different properties files (like cache.properties, dbdetails.properties)



The test package "Test Packages" contains all the Junit test cases. 




An Example of a single test case is
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(
{
        "classpath*:/web/WEB-INF/applicationContext.xml",
        "classpath*:/web/WEB-INF/dispatcher-servlet.xml"
}) 
public class DaoTest {  
// JUNIT Specific Code
}
Since our project requires a number of properties file that are being configured through catalina.properties like logback configuration file path, dbdetails properties file path etc. (since we are loading all the properties defined in all of these properties file through Spring PropertyPlaceHolderConfigurer) which is provided automatically at runtime while running the application under TOMCAT, here the challenge is to provide all those properties through an external medium to the spring junit runner, so that the Spring application context is being initialized successfully.


For This Go to NetBeans menu: 
a. Tools -> Options  -> ANT Tab
b. Look for the properties at the bottom of the window.



For each of the properties that you have externalize by making use of catalina.properties we need to enter a line here in the PROPERTIES box.
E.g. Suppose we have a propery named “https.proxyPort” defined in the catalina.properties then we need to define a property here also, however we need to keep in mind that each property needs to be prepended by “test-sys-prop.”



      So the final property is test-sys-prop.https.proxyPort=1971 as shown in above diagram.
Similarly if we have externalize the Log4j property file it will be
test-sys-prop.logback.configurationFile=file\:///c\:/logback.xml
NOTE:
The “\” entered above is automatically added by netbeans we just need to add the following.
test-sys-prop.logback.configurationFile=file:///c:/logback.xml



Now Click on “OK” and netbeans will start building the indices for internal purposes.
Start Running your Junit Test Cases and Spring will initialize properly as in the TOMCAT.  And we can get access to all our beans by using @Autowired annotation in the test Cases.



Memory Problem:
Now if we are doing some memory intensive work, we need to provide netbeans with the –Xms and –Xmx runtime arguments. Otherwise we will keep on getting OutofMemoryError : heap space. To remove this:
Right Click on the project -> Properties -> Run


Place the –Xms & -Xmx parameter as per your requirements.

Hope it helps you to do the UNIT Testing in NetBeans. For this blog, I have taken help of a number of blogs,the main one is :http://forums.netbeans.org/post-294.html