java MySql 超时 - 我应该在 Spring 应用程序中设置 autoReconnect=true 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2406517/
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
MySql timeouts - Should I set autoReconnect=true in Spring application?
提问by George
After periods of inactivity on my website (Using Spring 2.5 and MySql), I get the following error:
在我的网站（使用 Spring 2.5 和 MySql）上一段时间不活动后，我收到以下错误：
org.springframework.dao.RecoverableDataAccessException: The last packet sent successfully to the server was 52,847,830 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
org.springframework.dao.RecoverableDataAccessException: The last packet sent successfully to the server was 52,847,830 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
According to this question, and the linked bug, I shouldn't just set autoReconnect=true. Does this mean I have to catch this exception on any queries I do and then retry the transaction? Should that logic be in the data access layer, or the model layer? Is there an easy way to handle this instead of wrapping every single query to catch this?
根据这个问题和链接的错误，我不应该只设置 autoReconnect=true。这是否意味着我必须在执行的任何查询中捕获此异常，然后重试事务？该逻辑应该在数据访问层还是模型层？有没有一种简单的方法来处理这个而不是包装每个查询来捕捉这个？
回答by ewernli
I would suggest that you use a connection poolinstead. They improve performance and can take care of the low-level details such as reconnect after timeout, etc.
我建议您改用连接池。它们可以提高性能并可以处理低级细节，例如超时后重新连接等。
A good one is c3p0, but there are others. Spring has support for that, though I don't know all the details. Hereis the configuration of a DataSourcefor Spring.
一个好的是c3p0，但还有其他的。Spring 对此提供支持，尽管我不知道所有细节。这是DataSourceSpring的一个配置。
回答by BalusC
This exception can have 2 causes:
此异常可能有两个原因：
You aren't closing the JDBC resources properly. All of the
Connection,StatementandResultSetmustbe closed in reversed order in thefinallyblock of thetryblock where they're been acquired. This is regardless of the fact whether you're using a connection pool or not.You are closing the JDBC resources properly, but using a connection pool with poor settings. You need to make sure that the connection pool don't hold connections open longer than the DB configrured timeout. Decrease the one or increase the other in the configuration.
您没有正确关闭 JDBC 资源。所有
Connection,Statement和ResultSet必须在获取它们的finally块的try块中以相反的顺序关闭。这与您是否使用连接池无关。您正在正确关闭 JDBC 资源，但使用的连接池设置不佳。您需要确保连接池的连接打开时间不会超过数据库配置的超时时间。在配置中减少一个或增加另一个。
回答by Brian
We needed to set the following properties in order to avoid the timeout even with connection pooling; we use the Apache Commons DBCP.
我们需要设置以下属性以避免即使使用连接池也超时；我们使用 Apache Commons DBCP。
<property name="validationQuery"> <value>SELECT 1</value> </property>
<property name="testOnBorrow"> <value>true</value> </property>
