Archive | May 2013
May 9, 2013  

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server

06:47:59,966 WARN http-8880-1 spi.SqlExceptionHelper:143 – SQL Error: 0, SQLState: 08003
06:47:59,986 ERROR http-8880-1 spi.SqlExceptionHelper:144 – No operations allowed after connection closed.
SEVERE: >>org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.JDBCConnectionException: No operations allowed after connection closed.; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: No operations allowed after connection closed.
>>javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: No operations allowed after connection closed.
>>org.hibernate.exception.JDBCConnectionException: No operations allowed after connection closed.
>>com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
>>com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 127,349,486 milliseconds ago. The last packet sent successfully to the server was 127,349,487 milliseconds ago. 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.
>>java.net.SocketException: Broken pipe
>> at java.net.SocketOutputStream.socketWrite0(Native Method)
>> at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)

Reason:

DB default connection idle timeout = 8hrs

If Application idle with no DB transaction before the timeout, DB connection will be dropped.

Solution:

1. Create own connection pool, and keep alive checking

2. Append autoconnect option at end of DB connection string,

eg. <property name=”hibernate.connection.url” value=”jdbc:mysql://127.0.0.1/db?autoReconnect=true” /

* not recommended by MYSQL (ref: http://pages.citebite.com/p4x3a0r8pmhm)

3. use c3p0 connection provider_class

–

<property name=”hibernate.connection.provider_class” value=”org.hibernate.connection.C3P0ConnectionProvider”/>
<property name=”hibernate.c3p0.min_size” value=”5″/>
<property name=”hibernate.c3p0.max_size” value=”20″/>
<property name=”hibernate.c3p0.timeout” value=”1800″/>
<property name=”hibernate.c3p0.max_statements” value=”50″/>
<property name=”hibernate.c3p0.testConnectionOnCheckout” value=”true”/>

(ref: https://forum.hibernate.org/viewtopic.php?t=991039

http://blog.csdn.net/nethibernate/article/details/6658855)

* C3P0ConnectionProvider class locate in separate jar

<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-c3p0</artifactId>
			<version>3.6.3.Final</version>
		</dependency>