Losing connection to MySQL after a while, and not reconnecting

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">

<persistence-unit name="manager">

<class>example.data.entities.User</class>
<class>example.data.entities.Player</class>

<properties>
    <property name="hibernate.dialect" value="example.data.HibernateDialect" />
    <property name="hibernate.max_fetch_depth" value="3" />
    <property name="hibernate.hbm2ddl.auto" value="update" />

    <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />

</properties>

</persistence-unit>

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<User> 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.

mysql driver supports autoReconnect the jdbc url will look like jdbc:mysql://localhost/bms_company?autoReconnect=true

if u suspect mysql is dropping connections Try adding the following entries at the end of MySQL's my.ini file. This file is located in the MySQL installation's root folder.

#The number of seconds the mysqld server is waiting for a connect packet before responding with 'Bad handshake' connect_timeout=0

#The number of seconds the server waits for activity on an interactive connection before closing it. interactive_timeout=2000000

#The number of seconds the server waits for activity on a connection before closing it wait_timeout=2000000

--Kiran.Kumar