Several months ago, I was trying to click on a feature link of my personal web application using my MySQL database after I have left the connection to database from awhile and suddenly the following error was displayed in my screen:
SEVERE: Servlet.service() for servlet default threw exceptionThe issue had to do with the connection time out in MySQL, and the default
java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1666)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1082)
C3P0 setup that Hibernate uses. I have not been able to find the solution gathered together in one place, this why I am talking about it here.
Problem:
Technically speaking, after my Struts2/Hibernate/MySQL solution has been running but inactive for more than 8 hours, I get a broken pipe exception.
Cause:
MySQL automatically times out and closes unused connections after 8 hours (what is defined in the administration configuration). In addition to this, I did not have my Hibernate C3P0 to appropriately test and refresh the connection pool when connections are stale.
Solution:
Here is a snippet from the hibernate.cfg.xml which you add in order to fix the issue:
For more details about the elements above please visit here.
The idle_test_period and timeout properties are very important. You want to make sure that C3P0 is configured to test for closed connections and time out unused connections at some rate beneath the sill you set on your MySQL server.
Now you can test:
To test you should just modify the connection timeout of the MySQL server and restart this latter (in Linux for example, you can put wait_timeout=60 in my.cnf file) and set a value beneath this one in the C3P0 configuration section.
Problem:
Technically speaking, after my Struts2/Hibernate/MySQL solution has been running but inactive for more than 8 hours, I get a broken pipe exception.
Cause:
MySQL automatically times out and closes unused connections after 8 hours (what is defined in the administration configuration). In addition to this, I did not have my Hibernate C3P0 to appropriately test and refresh the connection pool when connections are stale.
Solution:
Here is a snippet from the hibernate.cfg.xml which you add in order to fix the issue:
</html>
<session-configuration>
<!—Start C3P0 Configuration -->
<property name="hibernate.c3p0.acquire_increment">3</property>
<property name="hibernate.c3p0.idle_test_period">14400</property>
<property name="hibernate.c3p0.timeout">25200</property>
<property name="hibernate.c3p0.max_size">15</property>
<property name="hibernate.c3p0.min_size">3</property>
<property name="hibernate.c3p0.max_statements">0</property>
<property name="hibernate.c3p0.preferredTestQuery">select 1;</property>
<!-- End C3P0 Configuration -->
</session-configuration>
For more details about the elements above please visit here.
The idle_test_period and timeout properties are very important. You want to make sure that C3P0 is configured to test for closed connections and time out unused connections at some rate beneath the sill you set on your MySQL server.
Now you can test:
To test you should just modify the connection timeout of the MySQL server and restart this latter (in Linux for example, you can put wait_timeout=60 in my.cnf file) and set a value beneath this one in the C3P0 configuration section.
How to encourage this blog if you like it:
- Promote our sponsors;
- Add any kind of comment or critic;
- Ask me directly by email if you prefer.
I am 
