一段时间后失去与MySQL的连接，而不是重新连接

问题描述

我正在开发一个使用JPA + Hibernate访问MySQL数据库的独立服务器。

当我启动服务器时，一切正常。然而，经过一段时间（通常是第二天早上，如果我在下午开始）它将停止工作，因为显然与MySQL的连接已关闭（我看到很多 SocketException s在日志中）。这可能是由空闲造成的，服务器正在开发中，晚上没有人使用它。

我认为Hibernate，JDBC或我应用程序下面的其他图层会管理连接并重新打开它，如果需要的话，但显然不是。有没有我错过的配置参数？

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> 
 
<属性> 
<属性名称=hibernate.dialectvalue =示例。 data.HibernateDialect/> 
< property name =hibernate.max_fetch_depthvalue =3/> 
< property name =hibernate.hbm2ddl.autovalue =update /> 
 
< property name =javax.persistence.jdbc.drivervalue =com.mysql.jdbc.Driver/> 
 
< /属性> 
 
< /持久性单元> 
  

> EntityManagerFactory 创建

  log.info（Connecti使用+ dbUser +/+ dbPass）来访问数据库@+ dbUrl +; 
 $ b $ emf = Persistence.createEntityManagerFactory（manager，Maps.create（
javax.persistence.jdbc.user，dbUser，
javax.persistence.jdbc.password ，dbPass，
javax.persistence.jdbc.url，dbUrl 
））; 
  

查询

 尝试
 {
 
 TypedQuery< User> q = em.createQuery（SELECT u FROM User u WHERE u.email =：mail，User.class）; 
 q.setParameter（mail，email）; 
尝试{
 u = q.getSingleResult（）; 
 log.info（Authenticating：+ u）; 
} catch（NoResultException e）{
 return false; 
} 
 
} finally {
 em.close（）; 
 
  

解决方案

因为在每个 wait_timeout 传递后，mysql会关闭空闲连接;你有一些选择来解决你的问题：
$ b

• 使用连接池管理器，比如或。这将负责根据请求重新验证连接，最终，您可以指定要运行哪个查询来测试连接是否处于活动状态。 （默认为8小时）。


• 设置一个计划任务（例如使用），刷新连接，pingmysql服务器。

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的连接，而不是重新连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！