Spring / MySQL connection problems

28th July 2009

Spring / MySQL connection problems

I wrote about setting up a Spring / MySQL web app here

However, one problem that really plagued my early attempts was the fact that MySQL drops the connection between Toplink and the database after 24 hours of inactivity.

That meant that sites that were working perfectly the day before ended up failing the next day and generating support emails.

Surely that should be no problem? How about this in the connection string in the virtual server ROOT.xml:

<Resource

name="jdbc/mysqlsite"
type="javax.sql.DataSource"
password="<pass>"
driverClassName="com.mysql.jdbc.Driver"
maxIdle="2"
maxWait="5000"
username="<usewr>"
url="jdbc:mysql://localhost:3306/mydatabase?autoReconnect=true"

maxActive="4"/> 

That worked fine on Windows 2003 servers. On Linux (more specifically, Ubuntu) it just didn't work. At one point I was having a run a cron job every hour to make sure that the Toplink was connected.

Finally sorted it out. I upgraded to the last MySQL Jconnector and then put this in my virtual server ROOT.xml

<Resource
auth="Container"
name="jdbc/mysqlsite"
type="javax.sql.DataSource" 

driverClassName="com.mysql.jdbc.Driver"
maxIdle="2"
maxWait="5000"
username="<user>"
password="<pass>"
url="jdbc:mysql://localhost:3306/mydatabase?useUnicode=true&amp;characterEncoding=UTF-8"
maxActive="4"
validationQuery="SELECT 1"
testWhileIdle="true"
timeBetweenEvictionRunsMillis="10000"
minEvictableIdleTimeMillis="60000"

> 

The last 4 lines of this seemed to do the trick.