java 在mysql上关闭连接后不允许进行任何操作

No operations allowed after connection closed on mysql

提问by MDP

I changed host company for my webiste, in which I use a mySQL db. From time to time I get this error:

我为我的网站更改了主机公司，在其中我使用了 mySQL 数据库。我不时收到此错误：

No operations allowed after connection closed.

连接关闭后不允许进行任何操作。

I remember I had the same problem the first time I run my site on the old server, and I fixed the problem with a "configuration fix" and not with a "code fix", but I don't remember what I did :(

我记得我第一次在旧服务器上运行我的网站时遇到了同样的问题，我用“配置修复”而不是“代码修复”解决了这个问题，但我不记得我做了什么:(

I read mant posts here, but every talks about code fix and I don't think it's my case

我在这里阅读了很多帖子，但每个人都在谈论代码修复，我认为这不是我的情况

Can someone help me?

有人能帮我吗？

Thank you

谢谢

EDIT

编辑

I found this on my old context.xml file; probably this is the way I fixed the problem

我在旧的 context.xml 文件中找到了这个；可能这就是我解决问题的方式

  validationQuery="SELECT 1"
  testOnBorrow="true"

testOnBorrow:(boolean) The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string. Default value is false

testOnBorrow:(boolean) 对象在从池中借用之前是否被验证的指示。如果对象无法验证，它将从池中删除，我们将尝试借用另一个。注意 - 要使真值产生任何效果，validationQuery 参数必须设置为非空字符串。默认值为 false

validationQuery:(String) The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query does not have to return any data, it just can't throw a SQLException. The default value is null. Example values are SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server)

validationQuery:(String) 将用于在将连接返回给调用者之前验证来自该池的连接的 SQL 查询。如果指定，此查询不必返回任何数据，它只是不能抛出 SQLException。默认值为空。示例值为 SELECT 1(mysql), select 1 from dual(oracle), SELECT 1(MS Sql Server)

Can someone confirm?

有人可以确认吗？

回答by javatutorial

You can use an autoReconnect=true parameter in your JDBC connection URL: (for example: "jdbc:mysql://localhost:3306/my-db?autoReconnect=true").

您可以在 JDBC 连接 URL 中使用 autoReconnect=true 参数：（例如：“jdbc:mysql://localhost:3306/my-db?autoReconnect=true”）。

Note, though, that this can lead to problems if your application doesn't handle SQLExceptions properly.

但是请注意，如果您的应用程序没有正确处理 SQLExceptions，这可能会导致问题。

From MySql documentation:

来自 MySql 文档：

autoReconnect

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 Aleksa Milosevic

In your configuration properties you might have used c3p0 property maxIdleTime, but c3p0 properties are configured using c3p0. prefix or hibernate.c3p0.. Note that enabling any of c3p0 properties automatically enables the corresponding connection provider by hibernate heuristics. In the log isn't seen that you are using c3p0. If you are not configured data sources then Hibernate will use hibernate.connection.provider_class org.hibernate.connection.DriverManagerConnectionProvider. This connection provider has in-built rudimentary connection pool for which you can set a hibernate.connection.pool_size, but it is used only for development purposes. Never use it in the production environment.

在您的配置属性中，您可能使用了 c3p0 属性 maxIdleTime，但 c3p0 属性是使用 c3p0 配置的。prefix 或 hibernate.c3p0.. 请注意，启用任何 c3p0 属性都会通过休眠启发式自动启用相应的连接提供程序。在日志中没有看到您正在使用 c3p0。如果您没有配置数据源，那么 Hibernate 将使用 hibernate.connection.provider_class org.hibernate.connection.DriverManagerConnectionProvider。此连接提供程序具有内置的基本连接池，您可以为其设置 hibernate.connection.pool_size，但它仅用于开发目的。切勿在生产环境中使用它。

You might have endless discussion on topic How to fix java.net.SocketException: Broken pipe. After some time you will realize that you have remained open connections in the pool, which are suddenly closed on the other side by the following reasons:

您可能对如何修复 java.net.SocketException: Broken pipe 主题进行了无休止的讨论。一段时间后，您会发现池中的连接一直处于打开状态，但由于以下原因，这些连接在另一侧突然关闭：

• Firewalls or routers may clamp down on idle connections (the MySQL client/server protocol does not ping).

• The MySQL Server may be closing idle connections that exceed the wait_timeout or interactive_timeout threshold.

  To help troubleshoot these issues, the following tips can be used:

• A recent (5.1.13+) version of JDBC driver is used.

• Ensure that wait_timeout and interactive_timeout are set sufficiently high. Check if interactiveClient is used. ?Ensure that tcpKeepalive is enabled.
• Ensure that any configurable firewall or router timeout settings allow for the maximum expected connection idle time.
• Ensure connections are valid when used from the connection pool. Use a query that starts with /* ping */ to execute a lightweight ping instead of full query. Note, the syntax of the ping needs to be exactly as specified here.
• Explicitly validate the connection before using it if the connection has been left idle for an extended period of time.
• Minimize the duration a connection object is left idle while other application logic is executed.

• 防火墙或路由器可能会限制空闲连接（MySQL 客户端/服务器协议不会 ping）。

• MySQL 服务器可能正在关闭超过 wait_timeout 或 interactive_timeout 阈值的空闲连接。

  为了帮助解决这些问题，可以使用以下提示：

• 使用了最新 (5.1.13+) 版本的 JDBC 驱动程序。

• 确保将wait_timeout 和interactive_timeout 设置得足够高。检查是否使用了 InteractiveClient。?确保启用了 tcpKeepalive。
• 确保任何可配置的防火墙或路由器超时设置允许最大预期连接空闲时间。
• 从连接池中使用时，确保连接有效。使用以 /* ping */ 开头的查询来执行轻量级 ping 而不是完整查询。请注意，ping 的语法需要与此处指定的完全相同。
• 如果连接闲置了很长时间，请在使用前显式验证连接。
• 最小化连接对象在执行其他应用程序逻辑时空闲的持续时间。

To comply to some of this options you'd better use a connection pool that you can use with Hibernate. Hibernate has support for commons-dbcp, c3p0, and proxool. Also you can configure JNDI data source on web server to use with hibernate, it has a connection pool.

为了遵守这些选项中的一些，您最好使用可以与 Hibernate 一起使用的连接池。Hibernate 支持 commons-dbcp、c3p0 和 proxool。您也可以在 Web 服务器上配置 JNDI 数据源以与 hibernate 一起使用，它有一个连接池。