問(wèn)題描述

2018-08-13 16:50:54  [ qtp2048834776-21:Slf4JLogger:79539 ] - [ ERROR ]  Begin Transaction error !
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 40,578,310 milliseconds ago.  The last packet sent successfully to the server was 40,578,315 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:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)

錯(cuò)誤原因

Mysql服務(wù)器默認(rèn)的“wait_timeout”是8小時(shí)【也就是默認(rèn)的值默認(rèn)是28800秒】，也就是說(shuō)一個(gè)connection空閑超過(guò)8個(gè)小時(shí)，Mysql將自動(dòng)斷開(kāi)該connection，通俗的講就是一個(gè)連接在8小時(shí)內(nèi)沒(méi)有活動(dòng)，就會(huì)自動(dòng)斷開(kāi)該連接。而應(yīng)用連接池卻認(rèn)為該連接還是有效的(因?yàn)椴⑽葱ｒ?yàn)連接的有效性)，當(dāng)應(yīng)用申請(qǐng)使用該連接時(shí)，就會(huì)導(dǎo)致上面的報(bào)錯(cuò)。

解決方法

1、autoReconnect=true

按照日志提示，設(shè)置autoReconnect=true會(huì)自動(dòng)連接。但是查看線上配置環(huán)境已經(jīng)這么設(shè)置了，網(wǎng)上很多人說(shuō)mysql客戶端5.0后不支持這個(gè)配置了，即設(shè)置了也沒(méi)有任何用。

2、dpcp連接池

dpcp屬性明細(xì).jpg

3、c3p0

通過(guò)idleConnectionTestPeriod和maxIdleTime 這兩個(gè)參數(shù)的配置，具體就是必須小于mysq server端的wait_time時(shí)間設(shè)置