mysql一段时间过后 无法连接,一段时间后失去与MySQL的连接，而不是重新连接

I'm developing a standalone server which uses JPA+Hibernate to access a MySQL database.

When I start the server, everything is working fine. However, after a while (usually the next morning, if I start it in the afternoon) it will stop working because apparently the connection to MySQL was closed (I see lots of SocketExceptions in the logs). This is probably caused by idling, the server is in development and nobody uses it at night.

I thought Hibernate, JDBC or some other layer below my app would manage the connection and reopen it if neccessary, but apparently not. Is there a configuration parameter I missed?

persistence.xml

http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"

version="2.0">

example.data.entities.User

example.data.entities.Player

EntityManagerFactory creation

log.info("Connecting to database @ " + dbUrl + " using " + dbUser + "/" + dbPass);

emf = Persistence.createEntityManagerFactory("manager", Maps.create(

"javax.persistence.jdbc.user", dbUser,

"javax.persistence.jdbc.password", dbPass,

"javax.persistence.jdbc.url", dbUrl

));

A query

try

{

TypedQuery q = em.createQuery("SELECT u FROM User u WHERE u.email = :mail", User.class);

q.setParameter("mail", email);

try {

u = q.getSingleResult();

log.info("Authenticating: " + u);

} catch (NoResultException e) {

return false;

}

} finally {

em.close();

}

解决方案

As you suggest, it is because mysql closes idle connections after each wait_timeout passes; you have some options to work-around your problem:

use a connection pool manager, like c3p0 or apache DBCP. This will take care of revalidation of connections on request, eventually you can specify which query to run to test if connection is alive.

set wait_timeout in mysql large enough for your use case (default is 8 hrs).

setup a scheduled task (for instance using quartz) that refreshes connections, "pinging" the mysql server.