MySQL之—重連，連線遺失的程式碼實例詳解

最近，專案中常遇到MySQL重連，連接遺失的程式碼的問題，研究了下解決方法，現共享出來，大家可以參考一下，下面我們就進入正題。

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]

#2、解決方案

- 如果使用的是JDBC，在JDBC URL上新增?autoReconnect=true，如：

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

- 如果是在Spring中使用DBCP連接池，在定義datasource增加屬性validationQuery和testOnBorrow，如：

<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的時候，新增屬性testConnectionOnCheckin和testConnectionOnCheckout，如：

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

#

以上是MySQL之—重連，連線遺失的程式碼實例詳解的詳細內容。更多資訊請關注PHP中文網其他相關文章！