mysql 连接超时异常

mysql 连接超时错误 自己的小网站在测试机器上长时间不访问后（默认8小时过期），再次访问发现有如下错误： Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received fro m the server was 63,020,509 m

自己的小网站在测试机器上长时间不访问后（默认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"

?

目前我不想添加： ?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

?