com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:连接关闭后不允许操作 [英] com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed

问题描述

我构建了一个应用程序并在本地部署......它运行良好.我将它部署在远程服务器上并开始收到主题行中提到的异常.这不是因为任何防火墙问题.

我更改了我的 hibernate.xml 以通过我的 IP 地址而不是本地主机进行连接，现在我在本地部署的应用程序上获得了相同的超时.当我让应用程序运行超过一天时，我收到此错误.

我自己在提交事务或关闭会话后没有执行任何操作.我在 hibernate.cfg.xml

中使用以下属性

<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property><property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property><property name="hibernate.connection.url">jdbc:mysql://myremotehost:3306/akp</property><property name="hibernate.connection.username">root</property><property name="hibernate.connection.password">root</property><property name="hibernate.show_sql">false</property><property name="hibernate.current_session_context_class">thread</property><property name="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</property>

<块引用>

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: 连接关闭后不允许操作.连接被驱动隐式关闭.

详细:

 引起:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:连接关闭后不允许操作.连接被驱动隐式关闭.在 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)在 sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)在 sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)在 java.lang.reflect.Constructor.newInstance(Constructor.java:513)在 com.mysql.jdbc.Util.handleNewInstance(Util.java:409)在 com.mysql.jdbc.Util.getInstance(Util.java:384)在 com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)在 com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)在 com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)在 com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)在 com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1193)在 com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1180)在 com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4137)在 com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4103)在 org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)在 org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)在 org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)在 org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)在 org.hibernate.loader.Loader.doQuery(Loader.java:673)在 org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)在 org.hibernate.loader.Loader.doList(Loader.java:2220)... 36 更多引起:com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:从服务器成功接收的最后一个数据包是 34,247,052 毫秒前.最后一个成功发送到服务器的数据包是在 34,247,052 毫秒之前.比服务器配置的wait_timeout"值长.您应该考虑在应用程序中使用之前使连接有效性过期和/或测试连接有效性，增加客户端超时的服务器配置值，或使用连接器/J 连接属性autoReconnect=true"来避免此问题.在 sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)在 sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)在 sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)在 java.lang.reflect.Constructor.newInstance(Constructor.java:513)在 com.mysql.jdbc.Util.handleNewInstance(Util.java:409)在 com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)在 com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3321)在 com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1940)在 com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)在 com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)在 com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)在 com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)在 org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)在 org.hibernate.loader.Loader.getResultSet(Loader.java:1787)在 org.hibernate.loader.Loader.doQuery(Loader.java:674)在 org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)在 org.hibernate.loader.Loader.doList(Loader.java:2220)在 org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)在 org.hibernate.loader.Loader.list(Loader.java:2099)在 org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)在 org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)在 org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)在 com.xyz.abc.DAO.GenericHibernateDAO.findByField(GenericHibernateDAO.java:119)在 com.xyz.abc.DAO.JobDAO.getJobsByLdap(JobDAO.java:115)在 com.xyz.abc.business.Jcr.getMyruns(Jcr.java:272)在 com.xyz.abc.business.abcService.getMyruns(abcService.java:54)在 sun.reflect.GeneratedMethodAccessor139.invoke(未知来源)在 sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)在 java.lang.reflect.Method.invoke(Method.java:597)在 org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java:194)在 org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:102)在 org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40)在 org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:114)在 org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:173)在 org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:173)在 org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:142)在 javax.servlet.http.HttpServlet.service(HttpServlet.java:641)在 javax.servlet.http.HttpServlet.service(HttpServlet.java:722)在 org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)在 org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)在 org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)在 org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:203)在 org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)在 org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108)在 org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)在 org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:379)在 org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:242)在 org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:259)在 org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:237)... 4个引起:java.net.SocketException:软件导致连接中止:套接字写入错误

有没有人知道可能导致这种行为的原因?

现在在我的 hibernate.cfg.xml 文件中使用以下内容.是否正确?

<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property><property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property><property name="hibernate.connection.url">jdbc:mysql://localhost:3306/xyz</property><property name="hibernate.connection.username">root</property><property name="hibernate.connection.password">root</property><property name="hibernate.show_sql">false</property><property name="hibernate.current_session_context_class">thread</property><property name="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</property><property name="hibernate.c3p0.min_size">5</property><property name="hibernate.c3p0.max_size">20</property><!-- <property name="hibernate.c3p0.max_size">1800</property>--><property name="hibernate.c3p0.max_statements">50</property><property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property><property name="c3p0.max_statements">0</property><property name="c3p0.maxIdleTimeExcessConnections">3600</property><property name="c3p0.idleConnectionTestPeriod">3600</property><property name="c3p0.maxIdleTime">3600</property>

解决方案

正如@swanliu 指出的那样，这是由于连接不良造成的.
但是在调整服务器时间和客户端超时之前，我会先尝试使用更好的连接池策略.

连接池

Hibernate 本身承认其连接池策略是最小的

<块引用>

Hibernate 自己的连接池算法，然而，相当初级.它旨在帮助您入门，而不是旨在用于生产系统，甚至用于性能测试.您应该使用第三方池以获得最佳性能和稳定.只需替换 hibernate.connection.pool_size 属性具有连接池特定设置.这将关闭 Hibernate 的内部池.例如，您可能喜欢使用 c3p0.
如参考资料中所述:http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html

我个人使用 C3P0.但是，还有其他替代方案可用，包括 DBCP.
退房

• http://www.mchange.com/projects/c3p0/index.html
• http://commons.apache.org/dbcp/

以下是我的应用程序中使用的 C3P0 的最小配置:

org.hibernate.connection.C3P0ConnectionProvider<property name="c3p0.acquire_increment">1</property><property name="c3p0.idle_test_period">100</property><!-- 秒--><property name="c3p0.max_size">100</property><property name="c3p0.max_statements">0</property><property name="c3p0.min_size">10</property><property name="c3p0.timeout">1800</property><!-- 秒-->

<块引用>

默认情况下，池永远不会过期连接.如果你希望连接会随着时间的推移而过期，以保持新鲜度"，设置 maxIdleTime 和/或 maxConnectionAge.maxIdleTime 定义了多少几秒钟内应允许连接在被使用之前不使用从游泳池中剔除.maxConnectionAge 强制池剔除任何从数据库获取的连接数超过集合过去的秒数.
如参考资料所述: http://www.mchange.com/projects/c3p0/index.html#managing_pool_size

我更新了配置文件(Reference)，因为我刚刚为我的项目复制粘贴了一个.理想情况下超时应该可以解决问题，如果这对您不起作用，则有一个昂贵的解决方案，我认为您可以看看:

创建一个文件c3p0.properties"，它必须位于类路径的根目录中(即无法为应用程序的特定部分覆盖它).(参考)

# c3p0.propertiesc3p0.testConnectionOnCheckout=true

使用此配置，每个连接在使用前都经过测试.但是，它可能会影响网站的性能.

I built an application and deployed locally ... and it was working perfectly. I deployed it on a remote server and started getting the exception mentioned in the subject line. It's not because of any firewall issues.

I changed my hibernate.xml to connect via my IP address rather then localhost and now I get the same timeouts on my locally deployed application. I get this error when I keep the application running for more than one day.

I am not performing any operations after committing transactions or closing sessions myself. I am using the following properties in hibernate.cfg.xml

<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://myremotehost:3306/akp</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">root</property>
        <property name="hibernate.show_sql">false</property>
        <property name="hibernate.current_session_context_class">thread</property>
        <property name="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</property>

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.

Detailed:

 Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
      at com.mysql.jdbc.Util.getInstance(Util.java:384)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
      at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
      at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1193)
      at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1180)
      at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4137)
      at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4103)
      at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
      at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
      at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
      at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
      at org.hibernate.loader.Loader.doQuery(Loader.java:673)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
      at org.hibernate.loader.Loader.doList(Loader.java:2220)
      ... 36 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 34,247,052 milliseconds ago.  The last packet sent successfully to the server was 34,247,052 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.
      at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
      at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
      at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
      at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
      at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
      at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
      at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3321)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1940)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
      at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
      at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
      at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
      at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
      at org.hibernate.loader.Loader.doQuery(Loader.java:674)
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
      at org.hibernate.loader.Loader.doList(Loader.java:2220)
      at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
      at org.hibernate.loader.Loader.list(Loader.java:2099)
      at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
      at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
      at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)
      at com.xyz.abc.DAO.GenericHibernateDAO.findByField(GenericHibernateDAO.java:119)
      at com.xyz.abc.DAO.JobDAO.getJobsByLdap(JobDAO.java:115)
      at com.xyz.abc.business.Jcr.getMyruns(Jcr.java:272)
      at com.xyz.abc.business.abcService.getMyruns(abcService.java:54)
      at sun.reflect.GeneratedMethodAccessor139.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:597)
      at org.apache.axis2.rpc.receivers.RPCUtil.invokeServiceClass(RPCUtil.java:194)
      at org.apache.axis2.rpc.receivers.RPCMessageReceiver.invokeBusinessLogic(RPCMessageReceiver.java:102)
      at org.apache.axis2.receivers.AbstractInOutMessageReceiver.invokeBusinessLogic(AbstractInOutMessageReceiver.java:40)
      at org.apache.axis2.receivers.AbstractMessageReceiver.receive(AbstractMessageReceiver.java:114)
      at org.apache.axis2.engine.AxisEngine.receive(AxisEngine.java:173)
      at org.apache.axis2.transport.http.HTTPTransportUtils.processHTTPPostRequest(HTTPTransportUtils.java:173)
      at org.apache.axis2.transport.http.AxisServlet.doPost(AxisServlet.java:142)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:240)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:203)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:164)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:108)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:379)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:242)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:259)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:237)
      ... 4 more
Caused by: java.net.SocketException: Software caused connection abort: socket write error

Does anyone have any ideas what might cause this behavior?

EDIT: Now am using folloing in my hibernate.cfg.xml file.Is it correct?

<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/xyz</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">root</property>
    <property name="hibernate.show_sql">false</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <property name="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</property>
    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
<!-- <property name="hibernate.c3p0.max_size">1800</property>-->
    <property name="hibernate.c3p0.max_statements">50</property>

    <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
    <property name="c3p0.max_statements">0</property>
    <property name="c3p0.maxIdleTimeExcessConnections">3600</property>
    <property name="c3p0.idleConnectionTestPeriod">3600</property> 
    <property name="c3p0.maxIdleTime">3600</property>

解决方案

As @swanliu pointed out it is due to a bad connection.
However before adjusting the server timing and client timeout , I would first try and use a better connection pooling strategy.

Connection Pooling

Hibernate itself admits that its connection pooling strategy is minimal

Hibernate's own connection pooling algorithm is, however, quite rudimentary. It is intended to help you get started and is not intended for use in a production system, or even for performance testing. You should use a third party pool for best performance and stability. Just replace the hibernate.connection.pool_size property with connection pool specific settings. This will turn off Hibernate's internal pool. For example, you might like to use c3p0.
As stated in Reference : http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html

I personally use C3P0. however there are other alternatives available including DBCP.
Check out

• http://www.mchange.com/projects/c3p0/index.html
• http://commons.apache.org/dbcp/

Below is a minimal configuration of C3P0 used in my application:

<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="c3p0.acquire_increment">1</property> 
<property name="c3p0.idle_test_period">100</property> <!-- seconds --> 
<property name="c3p0.max_size">100</property> 
<property name="c3p0.max_statements">0</property> 
<property name="c3p0.min_size">10</property> 
<property name="c3p0.timeout">1800</property> <!-- seconds --> 

By default, pools will never expire Connections. If you wish Connections to be expired over time in order to maintain "freshness", set maxIdleTime and/or maxConnectionAge. maxIdleTime defines how many seconds a Connection should be permitted to go unused before being culled from the pool. maxConnectionAge forces the pool to cull any Connections that were acquired from the database more than the set number of seconds in the past.
As stated in Reference : http://www.mchange.com/projects/c3p0/index.html#managing_pool_size

Edit:
I updated the configuration file (Reference), as I had just copy pasted the one for my project earlier. The timeout should ideally solve the problem, If that doesn't work for you there is an expensive solution which I think you could have a look at:

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). (Reference)

# c3p0.properties
c3p0.testConnectionOnCheckout=true

With this configuration each connection is tested before being used. It however might affect the performance of the site.

这篇关于com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:连接关闭后不允许操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！