MySql jdbc autoReconnect 的应用「建议收藏」

大家好，欢迎来到IT知识分享网。

MySql 的jdbc 配置选项：http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

在High Availability and Clustering. 这一部分提到，

autoReconnect

Should the driver try to re-establish stale and/or dead connections? If enabled 
the driver will throw an exception for a queries issued on a stale or dead 
connection, which belong to the current transaction, but will attempt reconnect 
before the next query issued on the connection in a new transaction. The use of 
this feature is not recommended, because it has side effects related to session 
state and data consistency when applications don't handle SQLExceptions properly, 
and is only designed to be used when you are unable to configure your application 
to handle SQLExceptions resulting from dead and stale connections properly. 
Alternatively, as a last option, investigate setting the MySQL server variable 
"wait_timeout" to a high value, rather than the default of 8 hours.

Default: false

Since version: 1.1

很明显，官方是不建议使用该配置的，除非你自己不能处理SQLExceptions ，这种情况我倒是还没有遇到。

但是，有种情景下，这个参数是非常有用的：需要不停地查询数据库，没有多线程需求，那么为了效率，最好与数据库保持一个长连接，如果数据库宕机了怎么办？

[1104 17:05:25 854 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted
[1104 17:05:27 857 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 2,003 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago.
[1104 17:05:29 859 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
[1104 17:05:31 859 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
[1104 17:05:33 859 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
[1104 17:05:35 860 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
[1104 17:05:37 860 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.

可以看到，jdbc 驱动在连接失败后，只会不停地报异常（程序的查询请求都是通过同一个Statement 发出的），当数据库服务重新启动后，仍然没有反应。必须重启应用吗？

这时可以使用这个参数来要求jdbc 驱动在发现数据库连接异常后会自动地重新连接

jdbc:mysql://localhost:3306/scheduler?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&maxReconnects=2&initialTimeout=5

问题解决

[1105 14:45:52 453 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.MySQLQueryInterruptedException: Query execution was interrupted
[1105 14:46:01 458 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 2 times. Giving up.
[1105 14:46:10 471 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 2 times. Giving up.
[1105 14:46:19 475 ERROR] [main] scheduler.service.DatabaseService - com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 2 times. Giving up.

可以看到，在尝试重试建立连接失败后，放弃，再重试……

数据库服务一旦恢复正常，就可以自动建立连接，程序可以继续跑了。