mysql 连接超时错误
自己的小网站在测试机器上长时间不访问后（默认8小时过期），再次访问发现有如下错误：
caused by: com.mysql.jdbc.exceptions.jdbc4.communicationsexception: the last packet successfully received fro
m the server was 63,020,509 milliseconds ago. ?the last packet sent successfully to the server was 63,020,509
?milliseconds ago. is longer than the server configured value of 'wait_timeout'. you should consider either e
xpiring and/or testing connection validity before use in your application, increasing the server configured v
alues for client timeouts, or using the connector/j connection property 'autoreconnect=true' to avoid this pr
oblem.
? ? ? ? at sun.reflect.nativeconstructoraccessorimpl.newinstance0(native method)
? ? ? ? at sun.reflect.nativeconstructoraccessorimpl.newinstance(nativeconstructoraccessorimpl.java:57)
? ? ? ? at sun.reflect.delegatingconstructoraccessorimpl.newinstance(delegatingconstructoraccessorimpl.java:4
5)
? ? ? ? at java.lang.reflect.constructor.newinstance(constructor.java:526)
? ? ? ? at com.mysql.jdbc.util.handlenewinstance(util.java:411)
? ? ? ? at com.mysql.jdbc.sqlerror.createcommunicationsexception(sqlerror.java:1116)
? ? ? ? at com.mysql.jdbc.mysqlio.send(mysqlio.java:3851)
? ? ? ? at com.mysql.jdbc.mysqlio.sendcommand(mysqlio.java:2471)
? ? ? ? at com.mysql.jdbc.mysqlio.sqlquerydirect(mysqlio.java:2651)
? ? ? ? at com.mysql.jdbc.connectionimpl.execsql(connectionimpl.java:2683)
? ? ? ? at com.mysql.jdbc.preparedstatement.executeinternal(preparedstatement.java:2144)
? ? ? ? at com.mysql.jdbc.preparedstatement.execute(preparedstatement.java:1379)
? ? ? ? at org.apache.tomcat.dbcp.dbcp.delegatingpreparedstatement.execute(delegatingpreparedstatement.java:172)
? ? ? ? at org.apache.tomcat.dbcp.dbcp.delegatingpreparedstatement.execute(delegatingpreparedstatement.java:172)
?
fix:?if only the connection pool could check if the the connection it is about to return is live or not, the porblem is fixed. this can be done in apache-common-dbcp (i know this one coz i used it, please look into documentation of the connection-pool you are using). here’s how you do it: you add the following properties to dbcp configuration.
validationquery=”select 1″testonborrow=”true”and that does the trick.
?
暂时我对我的mysql连接加上： 试试效果
validationquery=select 1
?testonborrow=true?
目前我不想添加： ?autoreconnect=true? 因为我对此的理解不深，mysql文档这样写的：
?
this parameter is?false?by default. this forces disconnected api nodes (including mysql servers acting as sql nodes) to use a new connection to the cluster rather than attempting to re-use an existing one, as re-use of connections can cause problems when using dynamically-allocated node ids. (bug #45921)
如果有问题再添加 autoreconnect选项
like this:
?
? ? ? ? ? ? ? ?initialsize=10 maxactive=100 maxidle=30 maxwait=10000
? ? ? ? ? ? ? ?username=*** password=*** driverclassname=com.mysql.jdbc.driver
? ? ? ? ? ? ? ?url=jdbc:mysql://localhost:3306/saasnettest
? ? ? ? ? ? ? ?validationquery=select 1
? ? ? ? ? ? ? ?testonborrow=true
?
? ? />
?
参考文档：http://stackoverflow.com/questions/9674165/mysql-jdbc-timeout-even-with-autoreconnect-true
http://amitcodes.com/2008/07/26/16/
http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency
?