Quzrtz系列（一）：Failed to override connection auto commit/transaction isolation

一、问题描述

使用quartz对作业进行调度时，报出下列异常：

2018-09-27 06:00:00,149 WARN [org.quartz.impl.jdbcjobstore.JobStoreTX] Failed to override connection auto commit/transaction isolation.
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 437823 seconds ago.The last packet sent successfully to the server was 437823 seconds ago, which 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.GeneratedConstructorAccessor246.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl. java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) 
 
[Scheduler_QuartzSchedulerThread] WARN org.quartz.impl.jdbcjobstore.JobStoreTX - Failed to override connection auto commit/transaction isolation.
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.net.SocketException
MESSAGE: Broken pipe

STACKTRACE:

java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2744)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1612)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
    at com.mysql.jdbc.Connection.setAutoCommit(Connection.java:5442)
    at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:237)
    at org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.setAutoCommit(AttributeRestoringConnectionInvocationHandler.java:91)
    at org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.invoke(AttributeRestoringConnectionInvocationHandler.java:65)
    at $Proxy4.setAutoCommit(Unknown Source)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.getConnection(JobStoreSupport.java:711)
    at org.quartz.impl.jdbcjobstore.JobStoreTX.getNonManagedTXConnection(JobStoreTX.java:72)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3757)
    at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:2729)
    at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:266)


** END NESTED EXCEPTION **

使用的quartz版本为2.2.3，数据库的配置如下：

org.quartz.jobStore.dataSource = myDS
org.quartz.dataSource.myDS.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.myDS.URL = jdbc:mysql://localhost:3306/quartz?autoReconnect=true&characterEncoding=utf-8
org.quartz.dataSource.myDS.user = root
org.quartz.dataSource.myDS.password = root
org.quartz.dataSource.myDS.maxConnections =50

二、问题分析

• 数据库连接池技术C3P0

  MySql服务器默认的“wait_timeout”是8小时，也就是说一个connection空闲超过8个小时，MySql将自动断开该connection。 这就是问题的所在，在C3P0 pools中的connections如果空闲超过8小时，MySql将其断开，而C3P0并不知道该connection已经失效，如果这时有Client请求connection，C3P0将该失效的Connection提供给Client，将会造成上面的异常。

• Quartz数据库配置

  quartz定时任务使用时数据库连接默认是maxIdleTime=0，即永不放弃连接。在Spring Boot启动服务，控制台打印的quart