java - MySQL 数据库的 JDBC CommunicationsException

Question

我的 MySQL 连接池有点问题。是这样的:

通过 Quartz 安排不同的作业。所有作业都连接到不同的数据库，这些数据库全天工作正常，而夜间计划的作业因 CommunicationsException 而失败...

quartz 作业:

Job1 runs 0 0 6,10,14,18 * * ?
Job2 runs 0 30 10,18 * * ?
Job3 runs 0 0 5 * * ?

如您所见，最后一个作业在 18 点运行，运行时间约为 1 小时。早上 5 点的第一份工作是失败的。我已经在我的资源配置中尝试了各种参数组合，这是我现在正在运行的:

<!-- Database 1 (MySQL) -->
<Resource
 auth="Container"
 driverClassName="com.mysql.jdbc.Driver"
 maxActive="100"
 maxIdle="30"
 maxWait="10000"
 removeAbandoned="true"
 removeAbandonedTimeout="60"
 logAbandoned="true"
 type="javax.sql.DataSource"
 name="jdbc/appDbProd"
 username="****"
 password="****"
 url="jdbc:mysql://127.0.0.1:3306/appDbProd?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"
 testWhileIdle="true"
 testOnBorrow="true"
 testOnReturn="true"
 validationQuery="SELECT 1"
 timeBetweenEvictionRunsMillis="1800000"
/>

<!-- Database 2 (MySQL) -->
<Resource
 auth="Container"
 driverClassName="com.mysql.jdbc.Driver"
 maxActive="100"
 maxIdle="30"
 maxWait="10000"
 removeAbandoned="true"
 removeAbandonedTimeout="60"
 logAbandoned="true"
 type="javax.sql.DataSource"
 name="jdbc/prodDbCopy"
 username="****"
 password="****"
 url="jdbc:mysql://127.0.0.1:3306/prodDbCopy?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"
 testWhileIdle="true"
 testOnBorrow="true"
 testOnReturn="true"
 validationQuery="SELECT 1"
 timeBetweenEvictionRunsMillis="1800000"
/>
<!-- Database 3 (MSSQL)-->
<Resource
 auth="Container"
 driverClassName="net.sourceforge.jtds.jdbc.Driver"
 maxActive="30"
 maxIdle="30"
 maxWait="100"
 removeAbandoned="true"
 removeAbandonedTimeout="60"
 logAbandoned="true"
 name="jdbc/catalogDb"
 username="****"
 password="****"
 type="javax.sql.DataSource"
 url="jdbc:jtds:sqlserver://127.0.0.1:1433;databaseName=catalog;useNdTLMv2=false"
 testWhileIdle="true"
 testOnBorrow="true"
 testOnReturn="true"
 validationQuery="SELECT 1"
 timeBetweenEvictionRunsMillis="1800000"
/>

由于显而易见的原因，我更改了 IP、用户名和密码，但可以假设它们是正确的，因为应用程序成功运行了一整天。

最烦的是:第一个运行的第一个作业成功查询 Database2 但由于某种原因无法查询 Database1(CommunicationsException):

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 39,376,539 milliseconds ago. The last packet sent successfully to the server was 39,376,539 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.

有什么想法吗？谢谢!

Answer 1

如其所说，在连接池上启用连接验证。