首页 文章

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:连接关闭后不允许任何操作

提问于
浏览
37

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

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

我自己提交交易或结束 Session 后没有执行任何操作 . 我在 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>

引起:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:连接关闭后不允许任何操作 . 驱动程序隐式关闭了连接 .

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

有没有人有任何想法可能会导致这种行为?

编辑:现在我在我的hibernate.cfg.xml文件中使用了folloing . 这是正确的吗?

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

5 回答

  • 17

    MySQL隐式关闭数据库连接,因为连接已经 inactive for too long (34,247,052毫秒≈9.5小时) . 如果您的程序然后从连接池中获取导致 MySQLNonTransientConnectionException: No operations allowed after connection closed 的错误连接 .

    MySQL建议:

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

  • 20

    正如@swanliu所指出的那样是由于连接不良造成的 .
    However before adjusting the server timing and client timeout , I would first try and use a better connection pooling strategy.

    连接池

    Hibernate本身承认其连接池策略很少

    然而,Hibernate自己的连接池算法非常简陋 . 它旨在帮助您入门,不打算在 生产环境 系统中使用,甚至不用于性能测试 . 您应该使用第三方池以获得最佳性能和稳定性 . 只需用连接池特定设置替换hibernate.connection.pool_size属性即可 . 这将关闭Hibernate的内部池 . 例如,您可能想使用c3p0 . 如参考文献中所述:http://docs.jboss.org/hibernate/core/3.3/reference/en/html/session-configuration.html

    我个人使用 C3P0 . 但是还有其他选择,包括 DBCP .
    查看

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

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

    默认情况下,池永远不会到期连接 . 如果您希望Connections随着时间的推移而过期以保持“新鲜度”,请设置maxIdleTime和/或maxConnectionAge . maxIdleTime定义在从池中剔除之前应允许Connection未使用的秒数 . maxConnectionAge强制池剔除从数据库中获取的任何连接超过过去设置的秒数 . 如参考文献中所述:http://www.mchange.com/projects/c3p0/index.html#managing_pool_size

    Edit:
    我更新了配置文件(Reference),因为我刚刚复制粘贴了我之前的项目 . 超时应该理想地解决问题,如果这对你不起作用,我认为你可以看一下 expensive solution

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

    # c3p0.properties
    c3p0.testConnectionOnCheckout=true
    

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

  • 0

    如果您不想使用连接池(您确定,您的应用只有一个连接),您可以这样做 - 如果连接失败,您必须 Build 新的 - 呼叫方法 .openSession() 而不是 .getCurrentSession()

    例如:

    SessionFactory sf = null;
    // get session factory
    // ...
    //
    Session session = null;
    try {
            session = sessionFactory.getCurrentSession();
    } catch (HibernateException ex) {
            session = sessionFactory.openSession();
    }
    

    如果您使用Mysql,则可以设置 autoReconnect 属性:

    <property name="hibernate.connection.url">jdbc:mysql://127.0.0.1/database?autoReconnect=true</property>
    

    我希望这有帮助 .

  • 0
    • 首先替换下面给出的MySQL依赖项
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.44</version>
    </dependency>
    
    • 将出现显示“身份验证插件'caching_sha2_password'”的错误 . 运行此命令:
    mysql -u root -p
    ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
    
  • 1

    请确保您使用的是mysql的最新jdbc连接器 . 我遇到了这个问题,当我用最新的jdbc连接器替换旧的jdbc连接器时,问题就解决了 .

    你可以从https://dev.mysql.com/downloads/connector/j/下载最新的jdbc驱动程序

    选择操作系统作为平台无关 . 它会告诉你两个选择 . 一个作为焦油,一个作为拉链 . 下载zip并解压缩以获取jar文件并将其替换为旧连接器 .

    这不仅适用于hibernate框架,它可以与任何需要jdbc连接器的平台一起使用 .

相关问题