|
SEVERE: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.net.SocketException
MESSAGE: Software caused connection abort: socket write error
Communications link failure due to underlying exception:
...
So yesterday I decided to see if I could fix this. It turned out to be a very very long day. Here are the lessons I learned.
Lesson 1: By default, mySQL terminates db connections after 8 hours of inactivity
Here are the default mySQL settings:
#The number of seconds the server waits for activity on a connection before closing itwait_timeout=28800#The number of seconds the server waits for activity on an interactive connection before closing it.interactive_timeout=28800For the remainder of my testing I changed these values to 300 in the my.ini file so that my connections would close after only 5 minutes of activity. A much shorter timeframe for testing! So the initial problem is that once mySql closes down the connection, Hibernate does not natively manage this connection and reopen it. If you connect directly with a jdbc connection you may run into the same issue. The trick is to use some kind of connection pool manager to keep the connections alive... the question remains, how can we achieve this?
Lesson 2: Using App Server JNDI datasources gives you connection pooling
In jrun or jboss, it is possible to create jndi datasources. These will then be managed by the application server. I created a jndi datasource for the crm database that comes with FDS which I recreated a while back in mySQL. Creating this datasource involved adding the following configuration to my jrun-resources.xml in the \fds2\JRun4\servers\default\SERVER-INF folder:
javax.naming.Context context = new javax.naming.InitialContext();return ((DataSource) context.lookup("java:crm")).getConnection();would fail once and then reconnect successfully. It looked like I was on the right track, now it was time to reconfigure Hibernate.
Lesson 3: Hibernate is not recommended for connection pooling
I had to configure Hibernate to take advantage of my jndi datasources. Here's what the hibernate.cfg.xml looks like for both jrun and jboss:
Lesson 4: Make sure to have the latest mysql jdbc drivers installed
It turns out that Jboss jndi datasources can be configured to actively ping db connections and keep them alive. Yet I kept getting errors after configuring my datasource to do this 'pinging', and it turned out I was running older jdbc drivers. I upgraded to the latest ones and the errors went away. Here's the jboss datasource file with the ping features enabled:
Lesson 5: Hibernate must be told that it's connection is now being managed
At this point i was able to retrieve data without error, despite connection time outs. When I went to update a record, however, I got a cool new error:
java.sql.SQLException: You cannot commit during a managed transaction!
So, I did some digging and changed my hibernate.cfg.xml to this:
Summary
I spent alot of time on this yesterday, so this blog might seem a bit scarce and a bit rushed. Basically all the configuration files are here for you to use and customize. If you have questions, please ask. Again, this is blogged here for my benefit as well as yours. I would not want to have to redo this from scratch 6 months from now!