MySQL Communications Link Failure

I'm building a MySQL Driven web app using MySQL JPA Hibernate JConnector. I've hit an exception:

HTTP Status 500 - exception
javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: could not load an entity: [net.bmo.model.BmoCalendar#2]

The root cause of this error is:

root cause:java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.

Basically this means that the DB connection times out and when the app. went back to the DB to retrieve some data the connection had been lost.

Solution? Add connection pooling. Something I'd been meaning to get round to but hadn't had a chance. So I added C3P0 Connection pooling. I'm using Hibernate so I added the following dependency to the pom.xml:

  <dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-c3p0</artifactId>
    <version>3.3.2.GA</version>
  </dependency>

This drops in the required jar(s) hibernate-core-3.3.8.GA.jar and c3p0-0.9.1.jar add them to the class path if you don't already have them.

Next I added the c3p0 properties to the persistence.xml file. The amendments are highlighted in blue.

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" version="1.0">
  <persistence-unit name="myPersistence"
      transaction-type="RESOURCE_LOCAL">
   <provider>org.hibernate.ejb.HibernatePersistence
   <properties>
     <property name = "hibernate.connection.driver_class"
          value = "com.mysql.jdbc.Driver"/>
     <property name = "hibernate.connection.url"
          value = "jdbc:mysql://localhost:3306/myDB"/>
     <property name = "hibernate.connection.username" value = "uid"/>
     <property name = "hibernate.connection.password" value = "pwd"/>
     <property name = "hibernate.hbm2ddl.auto" value = "validate"/>
     <property name="hibernate.connection.provider_class"
          value="org.hibernate.connection.C3P0ConnectionProvider" />
     <property name="hibernate.c3p0.max_size" value="100" />
     <property name="hibernate.c3p0.min_size" value="0" />
     <property name="hibernate.c3p0.acquire_increment" value="1" />
     <property name="hibernate.c3p0.idle_test_period" value="300" />
     <property name="hibernate.c3p0.max_statements" value="0" />
     <property name="hibernate.c3p0.timeout" value="100" />
   </properties>
  </persistence-unit>
</persistence>


The effect of the lines above is to force the test of the connections in the pool at regular intervals and refresh the connection if required. This prevented the above error for my deployment. I did this without making any changes in the MySQL Database configuration

Back to the Blog

Wow it's been ages since I updated the Blog. I've been working hard for Microgen on a new Billing portal for Virgin Media (ntl:Telewest Business). It was an awesome project. In Four months we re-wrote the online billing application that they'd inherited through an acquisition.

Now it's leaner and meaner. User admin functionality, self sign-up, still with full integration with Microgen's eBilling architecture only now the load process is more maintainable and leveraging JSF, Spring, JPA & Hibernate, we managed to produce a lovely RIA. Only draw back is the configuration nightmare that was the project inception. So what next?

There's a phase II that I may or may not be involved in because I've got other projects to work on, although I'd love it if I could be part of it but Microgen is back up to strength in that department so maybe consultancy only.

I've been investigating other alternatives in the RIA market place. Currently I'm messing with Google's App Engine which I love, with GWT. I played with flex but there are issues there with page ranking, and forcing customers to use Flash - not a bad thing in itself but some people registered protests about moving away from vanilla web (silverlight? JavaFX?)