Sunday, June 27, 2010

apache.commons.dbcp.BasicDataSource Losts Connection with MySql after Long Time Inactive

I deployed my webapp into could server last week. Of course, I'm the only user of this webapp and just use it once a day currently. But when I use it, it reports the same exception every day:
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: org.hibernate.TransactionException: JDBC begin failed

After I scroll down the Exception, the root cause is:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was49672 seconds ago.The last packet sent successfully to the server was 49672 seconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

To solve this problem, the best way is to add property "testOnBorrow" in the dataSource configuration in you applicationContext.xml :

true


By adding testOnBorrow dbcp parameter, it will force a sanity check on every connection before it's used.

0 comments: