• mysql重连,连接丢失:The last packet successfully received from the server--转载


    1.1 错误信息:

    Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 20,820,001 milliseconds ago.  The last packet sent successfully to the server was 20,820,002 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.GeneratedConstructorAccessor29.newInstance(Unknown Source) ~[na:na]
    		at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_51]
    		at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_51]
    		at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.29.jar:na]
    		at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1129) ~[mysql-connector-java-5.1.29.jar:na]
    		at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3988) ~[mysql-connector-java-5.1.29.jar:na]
    		at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2598) ~[mysql-connector-java-5.1.29.jar:na]
    		at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778) ~[mysql-connector-java-5.1.29.jar:na]
    		at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2828) ~[mysql-connector-java-5.1.29.jar:na]
    		at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5372) ~[mysql-connector-java-5.1.29.jar:na]
    		at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:881) ~[c3p0-0.9.1.1.jar:0.9.1.1]
    		at org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.setAutoCommit(AttributeRestoringConnectionInvocationHandler.java:98) ~[quartz-2.2.1.jar:na]
    

    1.2 解决方法

    - 如果使用的是JDBC,在JDBC URL上添加?autoReconnect=true,如:

    jdbc:mysql://10.10.10.10:3306/mydb?autoReconnect=true
    

    - 如果是在Spring中使用DBCP连接池,在定义datasource增加属性validationQuerytestOnBorrow,如:

    <bean id="vrsRankDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${jdbc.driverClassName}" />
        <property name="url" value="${countNew.jdbc.url}" />
        <property name="username" value="${countNew.jdbc.user}" />
        <property name="password" value="${countNew.jdbc.pwd}" />
        <property name="validationQuery" value="SELECT 1" />
        <property name="testOnBorrow" value="true"/>
    </bean>
    

    - 如果是在Spring中使用c3p0连接池,则在定义datasource的时候,添加属性testConnectionOnCheckintestConnectionOnCheckout,如:

    <bean name="cacheCloudDB" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${cache.url}"/>
        <property name="user" value="${cache.user}"/>
        <property name="password" value="${cache.password}"/>
        <property name="initialPoolSize" value="10"/>
        <property name="maxPoolSize" value="${cache.maxPoolSize}"/>
        <property name="testConnectionOnCheckin" value="false"/>
        <property name="testConnectionOnCheckout" value="true"/>
        <property name="preferredTestQuery" value="SELECT 1"/>
    </bean>
    

    参考

    附录分析

    When a c3p0-proxied Connection throws an SQLException, c3p0 examines  
    the Exception and the Connection to make a judgement about whether  
    the problem implies that the Connection should no longer be included  
    in the pool. c3p0 tests the Connection, and if the test fails,  the  
    Connection will be excluded from the pool.
    
    What c3p0 is telling you here is that a Connection that previously  
    signalled an error and then failed a Connection test is still in use,  
    and has signalled another error. From c3p0's perspective, this is a  
    non-issue, it just means c3p0 doesn't have to do any kind of checks  
    or notifications, the Connection is already gone as far as the pool  
    is concerned. But c3p0 wonders why you'd still be using such a  
    Connection, and warns you about it.
    
    Usually, if a client continues to use a Connection that c3p0 has  
    correctly identified as broken, all further uses will provoke such an  
    exception, and the fix is to close the Connection and start over when  
    an application's Connection turns out to be dead. But, by the error  
    you're getting, it looks like your Connection is still live and okay  
    -- it's clearly communicating with the database. So, the issue is,  
    why did c3p0 deem the Connection dead if it is not?
    
    If you turn on DEBUG level logging (relevant loggers would be  
    com.mchange.v2.c3p0.impl.NewPooledConnection,  
    com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool, and  
    com.mchange.v2.c3p0.impl.DefaultConnectionTester, unless you've  
    defined your own ConnectionTester), you can trace the testing and  
    invalidation of Connections, and try to understand why Connections  
    that seem okay are testing as broken. That will give you better  
    information about what's going on.
    
    That said, the only cost of this behavior is disconcerting warning  
    messages and somewhat faster churn of Connections through the pool.  
    c3p0 is erring on the side of caution -- it has reason to believe a  
    Connection is bad, so it's been excluded from the pool. It'd be nice  
    to know why apparently good Connections are failing Connection tests,  
    but if it is an infrequent occurrence, it's very little to worry  
    about. (If it's happening a lot, you should track it down.)
    原文地址:http://sourceforge.net/p/c3p0/mailman/message/18310863/
  • 相关阅读:
    C++——修改gcc版本优先级,多版本gcc共存
    C++——pic 与PIC的异同
    Java源码执行顺序
    ubuntu开机鼠标键盘没反应
    加载动态库失败
    ubuntu 重启网络
    交叉编译iperf3
    查看动态库的函数名
    UI组件Kendo UI for jQuery数据管理入门指南 TaskBoard/列
    B/S端界面控件DevExtreme ASP.NET MVC入门指南 调用方法
  • 原文地址:https://www.cnblogs.com/gengaixue/p/6866564.html
Copyright © 2020-2023  润新知