Java 与 MySql 的连接正在自动中止。如何正确配置Connector/J？

Connection with MySql is being aborted automatically. How to configure Connector/J properly?

提问by Roman

I read this advice from error message:

我从错误消息中阅读了此建议：

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.

您应该考虑在应用程序中使用之前使连接有效性过期和/或测试连接有效性，增加客户端超时的服务器配置值，或使用连接器/J 连接属性“autoReconnect=true”来避免此问题。

I'm using Spring and JPA. Where should I configure Connector/J? (in persistence.xml, or in entityManagerFactoryspring configuration, or in dateSourcespring configuration, or somewhere else?)

我正在使用 Spring 和 JPA。我应该在哪里配置连接器/J？（在persistence.xml，或entityManagerFactory弹簧配置中，或dateSource弹簧配置中，或其他地方？）

采纳答案by ewernli

The text describes three solutions to prevent connection aborts:

文中介绍了三种防止连接中止的解决方案：

1. Configure the connection string with autoReconnect=true. This is a property of the URL connection string, which works at the driver level. You need to change the connection string in the data source configuration.

   url="jdbc:mysql://localhost:3306/confluence?autoReconnect=true"
   

2. Increase the timeout. This is normally a property of the database. You can increase this value to see if you get less connection abort.

3. Configure the connection pool to test the connection validatiy. This is done at the pool, not a the driver level. This will depend on the data source implementation that you use. But it should be configurable in the property of the data source, if you use a pooled one, e.g. c3p0.

   • Connection pooling options with JDBC: DBCP vs C3P0
   • http://snipplr.com/view/14725/c3p0-datasource-config-connectionpool/
   • http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

1. 配置连接字符串autoReconnect=true。这是 URL 连接字符串的一个属性，它在驱动程序级别工作。您需要更改数据源配置中的连接字符串。

   url="jdbc:mysql://localhost:3306/confluence?autoReconnect=true"
   

2. 增加超时时间。这通常是数据库的属性。您可以增加此值以查看是否减少了连接中止。

3. 配置连接池以测试连接有效性。这是在池中完成的，而不是驱动程序级别。这将取决于您使用的数据源实现。但是它应该可以在数据源的属性中配置，如果您使用池化的，例如c3p0。

   • JDBC 连接池选项：DBCP 与 C3P0
   • http://snipplr.com/view/14725/c3p0-datasource-config-connectionpool/
   • http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

Additionnal comments:

补充意见：

• The datasource/pool can also have a timeout, which corresponds to the time an idle connection remains in the pool. To not confused with the db timeout.
• There are several way to test the validity of a connection. One common way is to have dummy test table. The pool will issue a select on the dummy test table to see if the connection is still OK.

• 数据源/池也可以有一个超时，它对应于空闲连接在池中保持的时间。不要与数据库超时混淆。
• 有几种方法可以测试连接的有效性。一种常见的方法是使用虚拟测试表。池将在虚拟测试表上发出选择以查看连接是否仍然正常。

回答by Gaurav Agarwal

AutoReconnectis not recommended. From MySQL here

AutoReconnect不推荐。从这里的MySQL

Should the driver try to re-establish stale and/or dead connections? If enabled the driver will throw an exception for a queries issued on a stale or dead connection, which belong to the current transaction, but will attempt reconnect before the next query issued on the connection in a new transaction. The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly. Alternatively, as a last option, investigate setting the MySQL server variable "wait_timeout" to a high value, rather than the default of 8 hours.

驱动程序是否应该尝试重新建立陈旧和/或死连接？如果启用，驱动程序将对在旧或死连接上发出的查询抛出异常，这些查询属于当前事务，但将在新事务中的连接上发出下一个查询之前尝试重新连接。不推荐使用此功能，因为当应用程序不能正确处理 SQLExceptions 时，它会产生与会话状态和数据一致性相关的副作用，并且仅在您无法配置应用程序以处理由以下原因导致的 SQLExceptions 时使用死和陈旧的连接正确。或者，作为最后一个选项，调查将 MySQL 服务器变量“wait_timeout”设置为高值，而不是默认值 8 小时。

回答by CodeMed

This is for people like me who find this old posting through the search engines.

这适用于像我这样通过搜索引擎找到这个旧帖子的人。

The other answers are better long termsolutions. But if you just need the mysqlconnection running again right away, you can shutdownthen restarttomcatand everything will work fine for a while. This enables you to avoid system downtime while you figure out a longer term solution.

其他答案是更好的长期解决方案。但如果你只需要mysql重新运行连接向右走，你可以shutdown接着restarttomcat，一切都将正常工作了一段时间。这使您能够在找出长期解决方案的同时避免系统停机。

Navigate to $CATALINA_HOMEin the terminal, then type shutdown.sh, then type startup.sh. Wait a few moments for the startup sequence to complete, then your apps will work again for a while.

导航到$CATALINA_HOME中terminal，然后键入shutdown.sh，然后键入startup.sh。等待启动序列完成，然后您的应用程序将再次运行一段时间。

回答by Ravindra

I was go through many solutions and my problem was solved but after some time the connection is timeout or disconnected.After 2 3 days I got a solution that solve my problem.

我经历了很多解决方案，我的问题得到了解决，但一段时间后连接超时或断开连接。2 3 天后，我得到了解决我的问题的解决方案。

many solution suggest to use autoReconnect=truebut when I was go through the docs. I saw the following text in the source describing the autoReconnect parameter:

许多解决方案建议使用autoReconnect=true但是当我浏览文档时。我在描述 autoReconnect 参数的源代码中看到了以下文本：

The use of this feature is not recommended, because it has side effects related to session state and data consistency

不推荐使用此功能，因为它有与会话状态和数据一致性相关的副作用

When I looked in the Hibernate code. The basic connection mechanism of Hibernate doesn't support reconnecting, one has to use H3C0 connection pool (which itself didn't always support reconnecting).

当我查看 Hibernate 代码时。Hibernate 的基本连接机制不支持重连，必须使用 H3C0 连接池（它本身并不总是支持重连）。

But once one's used H3C0, the default behavior seems to be that to process a request, if the connection is dead then the user sees and error - but at least it reconnects for the next request. I suppose one error is better than infinite errors, but still not as good as zero errors. It turns out one needs the optiontestConnectionOnCheckout- which the documentation doesn't recommend because testing the connection before a request might lead to lower performance. Surely the software firstly has to work, only secondly does it have to work fast.

但是一旦使用了 H3C0，默认行为似乎是处理请求，如果连接已死，则用户会看到并出错 - 但至少它会为下一个请求重新连接。我认为一个错误比无限错误好，但仍然不如零错误。事实证明，人们需要 optiontestConnectionOnCheckout- 文档不建议这样做，因为在请求之前测试连接可能会导致性能降低。当然，软件首先必须工作，其次才必须快速工作。

So, to summarize, to get a connection to “work” (which I define as including handling dropped connections by reconnecting without error): In “hibernate.cfg.xml”:

因此，总而言之，要获得“工作”的连接（我定义为包括通过无错误重新连接来处理断开的连接）：在“hibernate.cfg.xml”中：

  <!-- hibernate.cfg.xml -->
    <property name="c3p0.min_size">5</property>
    <property name="c3p0.max_size">20</property>
    <property name="c3p0.timeout">1800</property>
    <property name="c3p0.max_statements">50</property>
    <!-- no "connection.pool_size" entry! -->

Then create a file “c3p0.properties” which must be in the root of the classpath (i.e. no way to override it for particular parts of the application):

然后创建一个文件“c3p0.properties”，它必须在类路径的根目录中（即无法为应用程序的特定部分覆盖它）：

c3p0.properties

c3p0.properties

c3p0.testConnectionOnCheckout=true

c3p0.testConnectionOnCheckout=true

If this solution don't work than there are more possible solutions:-

如果此解决方案不起作用，则有更多可能的解决方案：-

1. Add

<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

 Also dont forget to place the c3p0-0.9.1.jar in the classpath. 



    2. Instead of using that c3p0.properties file, couldn't you just use this property in your hibernate.cfg.xml:

<property name="hibernate.c3p0.validate">true</property>

    Also checkout the last post on this page:

    https://forum.hibernate.org/viewtopic.php?p=2399313

    If all these not work than go [more][1] and read in detail


  [1]: http://hibernatedb.blogspot.in/2009/05/automatic-reconnect-from-hibernate-to.html