关于 No operations allowed after connection closed.错误

本人用struts+jDBC+tomcat+mysql 做了个小型网站，刚开始挂上去时没有问题，等过三天左右就会报No operations allowed after connection closed.错误

报错如下：javax.servlet.ServletException: java.sql.SQLException: No operations allowed after connection closed.

Connection was closed due to the following exception:

** BEGIN NESTED EXCEPTION ** 

java.sql.SQLException
MESSAGE: Communication link failure: java.net.SocketException, underlying cause: Software caused connection abort: recv failed

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Software caused connection abort: recv failed

 

后经网上查找发现原因：

 

出现这种异常的原因是：Mysql服务器默认的“wait_timeout”是8小时，也就是说一个connection空闲超过8个小时，Mysql将自动断开该 connection。这就是问题的所在，在C3P0 pools中的connections如果空闲超过8小时，Mysql将其断开，而C3P0并不知道该connection已经失效，如果这时有 Client请求connection，C3P0将该失效的Connection提供给Client，将会造成上面的异常。

自己测试了一下，在mysql 的配置文件my.ini中在

[mysqld]下添加

wait_timeout=100  服务器关闭交互式连接前等待活动的秒数

  interactive_timeout=100   服务器关闭非交互连接之前等待活动的秒数

 

 

两个参数，即将mysql默认的链接等待时间由8小时改为100秒，登录网站，确实有如上错误出现。

 

解决方案：1、换一下JDBC驱动，JDBC3.1.0-alpha及以前版本会出现此问题，下载新的JDBC驱动

 

2、使用hibernate配置：

 

使用hibernate： 
<property name="connection.autoReconnect">true</property>　　<!--这个是最主要的--> 
<property name="connection.autoReconnectForPools">true</property> 
<property name="connection.is-connection-validation-required">true</property> 
加入以上property，可解决此问题，如果未使用hibernate等持久化管理框架，可在mysql的url中加入autoReconnect=true，这样就可以解决（源自iteye 信仰的力量）

 

 

 

抱歉上述方法可行性有问题，现改变：

 

原因很简单。在对数据库操作结束后关闭连接是正确的做法，没什么大问题。至于出现：No operations allowed after connection closed。这样的问题原因只有一个，你这里和数据库的连接Connection是一个Static的，程序共享这一个Connection。所以第一次对数据库操作没问题，当把Connection关闭后，第二次还想操作数据库时Connection肯定不存在了。（源自CSDN 小荷初现）

 

有没有更好的方法，请各位指点一下

评论

5 楼 whitesock 2011-04-30  

20.3.5.3.13: Why does Connector/J not reconnect to MySQL and re-issue the statement after a communication failure, instead of throwing an Exception, even though I use the autoReconnect connection string option?

There are several reasons for this. The first is transactional integrity. The MySQL Reference Manual states that “there is no safe method of reconnecting to the MySQL server without risking some corruption of the connection state or database state information”. Consider the following series of statements for example:

conn.createStatement().execute(
  "UPDATE checking_account SET balance = balance - 1000.00 WHERE customer='Smith'");
conn.createStatement().execute(
  "UPDATE savings_account SET balance = balance + 1000.00 WHERE customer='Smith'");
conn.commit();


Consider the case where the connection to the server fails after the UPDATE to checking_account. If no exception is thrown, and the application never learns about the problem, it will continue executing. However, the server did not commit the first transaction in this case, so that will get rolled back. But execution continues with the next transaction, and increases the savings_account balance by 1000. The application did not receive an exception, so it continued regardless, eventually commiting the second transaction, as the commit only applies to the changes made in the new connection. Rather than a transfer taking place, a deposit was made in this example.

Note that running with auto-commit enabled does not solve this problem. When Connector/J encounters a communication problem, there is no means to determine whether the server processed the currently executing statement or not. The following theoretical states are equally possible:

    The server never received the statement, and therefore no related processing occurred on the server.

    The server received the statement, executed it in full, but the response was not received by the client.

If you are running with auto-commit enabled, it is not possible to guarantee the state of data on the server when a communication exception is encountered. The statement may have reached the server, or it may not. All you know is that communication failed at some point, before the client received confirmation (or data) from the server. This does not only affect auto-commit statements though. If the communication problem occurred during Connection.commit(), the question arises of whether the transaction was committed on the server before the communication failed, or whether the server received the commit request at all.

The second reason for the generation of exceptions is that transaction-scoped contextual data may be vulnerable, for example:

    Temporary tables

    User-defined variables

    Server-side prepared statements

These items are lost when a connection fails, and if the connection silently reconnects without generating an exception, this could be detrimental to the correct execution of your application.

In summary, communication errors generate conditions that may well be unsafe for Connector/J to simply ignore by silently reconnecting. It is necessary for the application to be notified. It is then for the application developer to decide how to proceed in the event of connection errors and failures.

4 楼 Technoboy 2011-04-30  

whitesock 写道

autoReconnect是个非常危险的配置

可以简单说一下这个配置吗，徐师傅？

3 楼 阳韵圣 2011-04-27  

fujohnwang 写道

治标不治本，还是mysql的参数调整才是正道

我提供的方法是有很大弊端的，不关闭连接会使连接数越来越多，请问2楼，如何配置才能彻底根除。

2 楼 fujohnwang 2011-04-27  

治标不治本，还是mysql的参数调整才是正道

1 楼 whitesock 2011-04-27  

autoReconnect是个非常危险的配置