java 一段时间后失去与 MySQL 的连接，并且没有重新连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5060106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Losing connection to MySQL after a while, and not reconnecting
提问by Bart van Heukelom
I'm developing a standalone server which uses JPA+Hibernate to access a MySQL database.
我正在开发一个独立的服务器，它使用 JPA+Hibernate 来访问 MySQL 数据库。
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.
当我启动服务器时，一切正常。但是，一段时间后（通常是第二天早上，如果我在下午启动它）它将停止工作，因为显然与 MySQL 的连接已关闭（我SocketException在日志中看到很多s）。这可能是空闲引起的，服务器正在开发中，晚上没有人使用它。
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?
我认为 Hibernate、JDBC 或我的应用程序下面的其他一些层会管理连接并在必要时重新打开它，但显然不是。是否有我错过的配置参数？
persistence.xml
persistence.xml
http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd" version="2.0">
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>
EntityManagerFactorycreation
EntityManagerFactory创建
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();
}
回答by guido
As you suggest, it is because mysql closes idle connections after each wait_timeoutpasses; you have some options to work-around your problem:
正如您所建议的，这是因为 mysql 在每次wait_timeout通过后都会关闭空闲连接；您有一些选择来解决您的问题：
- use a connection pool manager, like c3p0or 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_timeoutin 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.
- 使用连接池管理器，如c3p0或apache DBCP。这将负责根据请求重新验证连接，最终您可以指定运行哪个查询来测试连接是否处于活动状态。
wait_timeout在 mysql 中为您的用例设置足够大（默认为 8 小时）。- 设置刷新连接的计划任务（例如使用quartz），“ping”mysql 服务器。
回答by kiran.kumar M
mysql driver supports autoReconnect the jdbc url will look like jdbc:mysql://localhost/bms_company?autoReconnect=true
mysql 驱动程序支持自动重新连接 jdbc url 看起来像 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.
如果您怀疑 mysql 正在断开连接，请尝试在 MySQL 的 my.ini 文件末尾添加以下条目。该文件位于 MySQL 安装的根文件夹中。
#The number of seconds the mysqld server is waiting for a connect packet before responding with 'Bad handshake' connect_timeout=0
#mysqld 服务器在响应'Bad handshake' connect_timeout=0 之前等待连接数据包的秒数
#The number of seconds the server waits for activity on an interactive connection before closing it. interactive_timeout=2000000
#服务器在关闭交互式连接之前等待活动的秒数。Interactive_timeout=2000000
#The number of seconds the server waits for activity on a connection before closing it wait_timeout=2000000
#服务器在关闭连接之前等待连接活动的秒数 wait_timeout=2000000
--Kiran.Kumar
——基兰·库马尔
