为什么 Hibernate/JDBC/MySQL 在一天左右后断开连接？

Why does Hibernate/JDBC/MySQL drop connections after a day or so?

提问by Uri

I have several server processes that once in a while respond to messages from the clients and perform read-only transactions.

我有几个服务器进程，它们偶尔会响应来自客户端的消息并执行只读事务。

After about a few days that the servers are running, they stop working correctly and when I check it turns out that there's a whole bunch of messages about the connection being closed.

服务器运行大约几天后，它们停止正常工作，当我检查时发现有一大堆关于连接被关闭的消息。

When I checked it out, it turned out that hibernate by default works in some sort of development mode where connections are dropped after a few hours, and I started using c3po for connection pooling.

当我检查它时，事实证明 hibernate 默认在某种开发模式下工作，其中连接在几个小时后断开，我开始使用 c3po 进行连接池。

However, even with c3po, I get that problem about 24 hours or so after the servers are started.

但是，即使使用 c3po，我也会在服务器启动后大约 24 小时左右遇到该问题。

Has anyone encountered that problem and knows how to address it? I'm not familiar enough with the intricacies of configuring hibernate.

有没有人遇到过这个问题并且知道如何解决它？我对配置休眠的复杂性还不够熟悉。

回答by Bill Karwin

The MySQL JDBC driver times out after 8 hours of inactivity and drops the connection.

MySQL JDBC 驱动程序在 8 小时不活动后超时并断开连接。

You can set autoReconnect=truein your JDBC URL, and this causes the driver to reconnect if you try to query after it has disconnected. But this has side effects; for instance session state and transactions cannot be maintained over a new connection.

您可以autoReconnect=true在 JDBC URL 中进行设置，如果您在断开连接后尝试查询，这会导致驱动程序重新连接。但这有副作用；例如会话状态和事务不能通过新连接维护。

If you use autoReconnect, the JDBC connection is reestablished, but it doesn't automatically re-execute your query that got the exception. So you do need to catch SQLExceptionin your application and retry queries.

如果使用autoReconnect，将重新建立 JDBC 连接，但它不会自动重新执行出现异常的查询。因此，您确实需要捕获SQLException应用程序并重试查询。

Read http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.htmlfor more details.

阅读http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html了解更多详情。

回答by Uri

MySql basically timeouts by default in 8 hours.

MySql 基本上默认在8 小时内超时。

I got the same exception & resolved the issue after 3 hectic days.Check if you are using I hibernate3. In this version it is required to explicitly mention the connection class name. Also check if the jar is in classpath. Check steps & comments in below link

我遇到了同样的异常并在忙碌的 3 天后解决了这个问题。检查你是否使用了我的 hibernate3。在此版本中，需要明确提及连接类名称。还要检查 jar 是否在类路径中。检查以下链接中的步骤和评论

http://hibernatedb.blogspot.com/2009/05/automatic-reconnect-from-hibernate-to.html

http://hibernatedb.blogspot.com/2009/05/automatic-reconnect-from-hibernate-to.html

Remove autoReconnect=true

消除 autoReconnect=true

回答by Bourkadi

I changed my hibernate configuration file by adding thoses lines and it works for now:

我通过添加这些行更改了我的休眠配置文件，现在它可以工作：

    <property name="connection.autoReconnect">true</property>
    <property name="connection.autoReconnectForPools">true</property>
    <property name="connection.is-connection-validation-required">true</property>

I think that using c3p0 pool is better and recomanded but this solution is working for now and don't present ant problem.
I let the Tomcat On for 24hours and the connection wasn't lost .
Please try it .

我认为使用 c3p0 池更好并推荐使用，但此解决方案目前有效，不会出现蚂蚁问题。
我让 Tomcat On 24 小时并且连接没有丢失。
请尝试一下。

回答by paxdiablo

I would suggest that, in almost any client/server set-up, it's a bad idea to leave connections open when they're not needed.

我建议，在几乎所有客户端/服务器设置中，在不需要连接时让连接保持打开状态是一个坏主意。

I'm thinking specifically about DB2/z connections but it applies equally to all servers (database and otherwise). These connections consume resources at the server that could be best utilized elsewhere.

我正在专门考虑 DB2/z 连接，但它同样适用于所有服务器（数据库和其他）。这些连接消耗服务器上的资源，而这些资源可以在其他地方得到最佳利用。

If you were to hold connections open in a corporate environment where tens of thousand of clients connect to the database, you would probably even bring a mainframe to its knees.

如果您要在数以万计的客户端连接到数据库的企业环境中保持连接打开，您甚至可能会让大型机屈服。

I'm all for the idea of connection pooling but not so much for the idea of trying to hold individual sessions open for ever.

我完全赞成连接池的想法，但不太赞成尝试永远打开各个会话的想法。

My advice would be as follows:

我的建议如下：

1/ Have three sorts of connections in your connection pool:

1/ 在你的连接池中有三种连接：

• closed (so not actually inyour pool).
• ready, meaning open but not in use by a client.
• active, meaning in use by a client.

• （所以不是真正关闭在你的池）。
• 就绪，意味着打开但未被客户端使用。
• active，表示正在被客户端使用。

2/ Have your connection pooling maintain a small number of ready connections, minimum of N and maximum of M. N can be adjusted depending on the peak speed at which your clients request connections. If the number of ready connections ever drops to zero, you need a bigger N.

2/ 让您的连接池保持少量的就绪连接，最小 N 和最大 M。N 可以根据客户端请求连接的峰值速度进行调整。如果就绪连接数下降到零，则需要更大的 N。

3/ When a client wants a connection, give them one of the ready ones (making it active), then immediately open a new one if there's now less than N ready (but don't make the client wait for this to complete, or you'll lose the advantage of pooling). This ensures there will always be at least N ready connections. If none are ready when the client wants one, they will have to wait around while you create a new one.

3/ 当客户端想要一个连接时，给他们一个准备好的连接（使其处于活动状态），然后如果现在准备好的少于 N 个，立即打开一个新的连接（但不要让客户端等待连接完成，或者你将失去池化的优势）。这确保将始终至少有 N 个就绪连接。如果客户想要一个时没有准备好，他们将不得不等待您创建一个新的。

4/ When the client finishes with an active connection, return it to the ready state if there's less than M ready connections. Otherwise close it. This prevents you from having more than M ready connections.

4/ 当客户端完成一个活动连接时，如果准备连接少于 M 个，则将其返回到就绪状态。否则关闭它。这可以防止您拥有超过 M 个就绪的连接。

5/ Periodically recycle the ready connections to prevent stale connections. If there's more than N ready connections, just close the oldest connection. Otherwise close it and re-open another.

5/ 定期回收准备好的连接以防止过时的连接。如果有超过 N 个准备好的连接，只需关闭最旧的连接。否则关闭它并重新打开另一个。

This has the advantage of having enough ready ANDyouthful connections available in your connection pool without overloading the server.

这样做的好处是在您的连接池中有足够的就绪和年轻的连接可用，而不会使服务器过载。