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启动服务，控制台打印的quartz数据库配置信息如下：

2018-09-28 14:38:31.957  INFO 13932 --- [           main] com.mchange.v2.c3p0.C3P0Registry         : Initializing c3p0-0.9.1.1 [built 15-March-2007 01:32:31; debug? true; trace: 10]
2018-09-28 14:38:31.988  INFO 13932 --- [           main] org.quartz.impl.StdSchedulerFactory      : Using default implementation for ThreadExecutor
2018-09-28 14:38:32.018  INFO 13932 --- [           main] org.quartz.core.SchedulerSignalerImpl    : Initialized Scheduler Signaller of type: class org.quartz.core.SchedulerSignalerImpl
2018-09-28 14:38:32.018  INFO 13932 --- [           main] org.quartz.core.QuartzScheduler          : Quartz Scheduler v.2.2.3 created.
2018-09-28 14:38:32.019  INFO 13932 --- [           main] org.quartz.impl.jdbcjobstore.JobStoreTX  : Using db table-based data access locking (synchronization).
2018-09-28 14:38:32.021  INFO 13932 --- [           main] org.quartz.impl.jdbcjobstore.JobStoreTX  : JobStoreTX initialized.
2018-09-28 14:38:32.021  INFO 13932 --- [           main] org.quartz.core.QuartzScheduler          : Scheduler meta-data: Quartz Scheduler (v2.2.3) 'MyScheduler' with instanceId 'MyScheduler'
  Scheduler class: 'org.quartz.core.QuartzScheduler' - running locally.
  NOT STARTED.
  Currently in standby mode.
  Number of jobs executed: 0
  Using thread pool 'org.quartz.simpl.SimpleThreadPool' - with 50 threads.
  Using job-store 'org.quartz.impl.jdbcjobstore.JobStoreTX' - which supports persistence. and is clustered.

2018-09-28 14:38:32.021  INFO 13932 --- [           main] org.quartz.impl.StdSchedulerFactory      : Quartz scheduler 'CMDBScheduler' initialized from an externally provided properties instance.
2018-09-28 14:38:32.021  INFO 13932 --- [           main] org.quartz.impl.StdSchedulerFactory      : Quartz scheduler version: 2.2.3
2018-09-28 14:38:32.021  INFO 13932 --- [           main] org.quartz.core.QuartzScheduler          : JobFactory set to: com.cmb.cmdb.bean.JobFactory@26844abb
2018-09-28 14:38:32.083  INFO 13932 --- [           main] c.m.v.c.i.AbstractPoolBackedDataSource   : Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, dataSourceName -> rhcsim9y8knjf2ph7q1r|51a6cc2a, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> rhcsim9y8knjf2ph7q1r|51a6cc2a, idleConnectionTestPeriod -> 50, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/quartz?autoReconnect=true&characterEncoding=utf-8, lastAcquisitionFailureDefaultUser -> null, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 50, maxStatements -> 0, maxStatementsPerConnection -> 120, minPoolSize -> 1, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, preferredTestQuery -> select 1, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> true, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false ]

注意到**maxIdleTime -> 0**，这是因为默认**org.quartz.dataSource.myDS.discardIdleConnectionsSeconds**没有配置。该配置项表示数据库连接在空闲之后放弃连接几秒钟。0表示禁用该功能。默认值为0。

三、Quartz数据源连接池

Quartz各版本数据库连接池技术

Quartz 2.0 以前 DBCP

Quartz 2.0 以后 C3P0（包含2.0）

四、DBCP 和C3P0的使用和区别

DBCP 介绍

DBCP(DataBase connection pool),数据库连接池。是 apache 上的一个 java 连接池项目，也是 tomcat 使用的连接池组件。单独使用DBCP需要3个包：common-dbcp.jar,common-pool.jar,common-collections.jar。由于建立数据库连接是一个非常耗时耗资源的行为，所以通过连接池预先同数据库建立一些连接，放在内存中，应用程序需要建立数据库连接时直接到连接池中申请一个就行，用完后再放回去。
C3P0介绍

C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。
DBCP 和C3P0区别

DBCP没有自动的去回收空闲连接的功能， C3P0有自动回收空闲连接功能。两者主要是对数据连接的处理方式不同，C3P0提供最大空闲时间，DBCP提供最大连接数。前者当连接超过最大空闲连接时间时，当前连接就会被断掉。DBCP当连接数超过最大连接数时，所有连接都会被断开。DBCP的原理是维护多个连接对象Connection,在web项目要连接数据库时直接使用它维护的对象进行连接，省去每次都要创建连接对象的麻烦。提高效率和减少内存使用。C3P0可以自动回收连接，DBCP需要自己手动释放资源返回。不过DBCP效率比较高。
DBCP和C3P0在Spring的配置

<!-- 配置dbcp数据源 -->      
<bean id="dataSource2" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">      <property name="driverClassName" value="${jdbc.driverClassName}"/>        
   <property name="url" value="${jdbc.url}"/>        
   <property name="username" value="${jdbc.username}"/>        
   <property name="password" value="${jdbc.password}"/>        
   <!-- 池启动时创建的连接数量 -->        
   <property name="initialSize" value="5"/>        
   <!-- 同一时间可以从池分配的最多连接数量。设置为0时表示无限制。 -->        
   <property name="maxActive" value="30"/>        
   <!-- 池里不会被释放的最多空闲连接数量。设置为0时表示无限制。 -->        
   <property name="maxIdle" value="20"/>        
   <!-- 在不新建连接的条件下，池中保持空闲的最少连接数。 -->        
   <property name="minIdle" value="3"/>        
   <!-- 设置自动回收超时连接 -->          
   <property name="removeAbandoned" value="true" />        
   <!-- 自动回收超时时间(以秒数为单位) -->          
   <property name="removeAbandonedTimeout" value="200"/>        
   <!-- 设置在自动回收超时连接的时候打印连接的超时错误  -->         
   <property name="logAbandoned" value="true"/>        
   <!-- 等待超时以毫秒为单位，在抛出异常之前，池等待连接被回收的最长时间（当没有可用连接时）。设置为-1表示无限等待。  -->          
   <property name="maxWait" value="100"/>        
 </bean>       
 
 <!-- 配置c3p0数据源 -->    
 <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">     <property name="jdbcUrl" value="${jdbc.url}" />        
    <property name="driverClass" value="${jdbc.driverClassName}" />        
    <property name="user" value="${jdbc.username}" />        
    <property name="password" value="${jdbc.password}" />       
    <!--连接池中保留的最大连接数。Default: 15 -->       
    <property name="maxPoolSize" value="100" />        
    <!--连接池中保留的最小连接数。-->        
    <property name="minPoolSize" value="1" />        
    <!--初始化时获取的连接数，取值应在minPoolSize与maxPoolSize之间。Default: 3 -->        
    <property name="initialPoolSize" value="10" />        
    <!--最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->        
    <property name="maxIdleTime" value="30" />        
    <!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->        
    <property name="acquireIncrement" value="5" />        
    <!--JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements属于单个         connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。如果maxStatements与                   maxStatementsPerConnection均为0，则缓存被关闭。Default: 0-->        
    <property name="maxStatements" value="0" />                 
    <!--每60秒检查所有连接池中的空闲连接。Default: 0 -->        
    <property name="idleConnectionTestPeriod" value="60" />                 
    <!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->        
    <property name="acquireRetryAttempts" value="30" />                
    <!--获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效保留，并在下次调用getConnection()的时候继续尝试获取连接。如果设为true，那么在尝试获取连接失败后该数据源将申明已断开并永久关闭。Default: false-->        
    <property name="breakAfterAcquireFailure" value="true" />                 
    <!--因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable等方法来提升连接测试的性能。Default: false -->           <property name="testConnectionOnCheckout"  value="false" />            
</bean>

五、Quartz配置DataSources

如果您使用JDBC-Jobstore，则需要使用DataSource（或使用两个DataSource，如果您使用JobStoreCMT）。

DataSources可以通过三种方式进行配置：

在quartz.properties文件中指定的所有池属性，以便Quartz可以自己创建DataSource。
可以指定应用程序服务器管理的Datasource的JNDI位置，以便Quartz可以使用它。
自定义的org.quartz.utils.ConnectionProvider实现。

建议您将Datasource max连接大小配置为至少线程池中的工作线程数量加上三个。如果您的应用程序也频繁调用调度程序API，则可能需要其他连接。如果您使用JobStoreCMT，则“非受管理”数据源的最大连接大小应至少为4。

您定义的每个DataSource（通常为一个或两个）必须为一个名称，并且您为每个定义的属性必须包含该名称，如下所示。DataSource的“NAME”可以是任何您想要的，除了能够在分配给JDBCJobStore之后能够识别它之外，没有什么意义。

Property Name	Required	Type	Default Value
org.quartz.dataSource.NAME.driver	yes	String	null
org.quartz.dataSource.NAME.URL	yes	String	null
org.quartz.dataSource.NAME.user	no	String	""
org.quartz.dataSource.NAME.password	no	String	""
org.quartz.dataSource.NAME.maxConnections	no	int	10
org.quartz.dataSource.NAME.validationQuery	no	String	null
org.quartz.dataSource.NAME.idleConnectionValidationSeconds	no	int	50
org.quartz.dataSource.NAME.validateOnCheckout	no	boolean	false
org.quartz.dataSource.NAME.discardIdleConnectionsSeconds	no	int	0 (disabled)

org.quartz.dataSource.NAME.driver

必须是数据库的JDBC驱动程序的java类名称。

org.quartz.dataSource.NAME.URL

连接到数据库的连接URL（主机，端口等）。

org.quartz.dataSource.NAME.user

连接到数据库时要使用的用户名。

org.quartz.dataSource.NAME.password

连接到数据库时使用的密码。

org.quartz.dataSource.NAME.maxConnections

DataSource可以在其连接池中创建的最大连接数。

org.quartz.dataSource.NAME.validationQuery

是可选的SQL查询字符串，DataSource可用于检测和替换失败/损坏的连接。例如，oracle用户可能会选择“从user_tables中选择table_name” - 这是一个不应该失败的查询 - 除非连接实际上是坏的。

org.quartz.dataSource.NAME.idleConnectionValidationSeconds

空闲连接测试之间的秒数 - 仅在设置验证查询属性时启用。默认值为50秒。

org.quartz.dataSource.NAME.validateOnCheckout

每次从池中检索连接时，是否应该执行数据库sql查询来验证连接，以确保它仍然有效。如果为假，则在办理登机手续时将进行验证。默认值为false。

org.quartz.dataSource.NAME.discardIdleConnectionsSeconds

它们在空闲之后放弃连接几秒钟。0禁用该功能。默认值为0。

Quartz定义的DataSource示例

org.quartz.dataSource.myDS.driver = oracle.jdbc.driver.OracleDriver
org.quartz.dataSource.myDS.URL = jdbc：oracle：thin：@ 10.0.1.23：1521：demodb
org.quartz.dataSource.myDS.user = myUser
org.quartz.dataSource.myDS.password = myPassword
org.quartz.dataSource.myDS.maxConnections = 30

对Application Server DataSources的引用使用以下属性定义：

Property Name	Required	Type	Default Value
org.quartz.dataSource.NAME.jndiURL	yes	String	null
org.quartz.dataSource.NAME.java.naming.factory.initial	no	String	null
org.quartz.dataSource.NAME.java.naming.provider.url	no	String	null
org.quartz.dataSource.NAME.java.naming.security.principal	no	String	null
org.quartz.dataSource.NAME.java.naming.security.credentials	no	String	null

org.quartz.dataSource.NAME.jndiURL

由应用程序服务器管理的DataSource的JNDI URL。

org.quartz.dataSource.NAME.java.naming.factory.initial

要使用的JNDI InitialContextFactory的（可选）类名。

org.quartz.dataSource.NAME.java.naming.provider.url

用于连接到JNDI上下文的（可选）URL。

org.quartz.dataSource.NAME.java.naming.security.principal

用于连接到JNDI上下文的（可选）用户主体。

org.quartz.dataSource.NAME.java.naming.security.credentials

用于连接到JNDI上下文的（可选）用户凭据。

从应用程序服务器引用的数据源示例

org.quartz.dataSource.myOtherDS.jndiURL = JDBC / myDataSource
org.quartz.dataSource.myOtherDS.java.naming.factory.initial = com.evermind.server.rmi.RMIInitialContextFactory
org.quartz.dataSource.myOtherDS.java.naming.provider.url = ormi：// localhost
 org.quartz.dataSource.myOtherDS.java.naming.security.principal = admin
org.quartz.dataSource.myOtherDS.java.naming.security.credentials = 123

自定义ConnectionProvider实现

Property Name	Required	Type	Default Value
org.quartz.dataSource.NAME.connectionProvider.class	yes	String (class name)	null

org.quartz.dataSource.NAME.connectionProvider.class

要使用的ConnectionProvider的类名。实例化之后，Quartz可以自动设置实例上的配置属性，bean样式。

使用自定义ConnectionProvider实现的示例

org.quartz.dataSource.myCustomDS.connectionProvider.class = com.foo.FooConnectionProvider
org.quartz.dataSource.myCustomDS.someStringProperty = someValue
org.quartz.dataSource.myCustomDS.someIntProperty = 5

六、解决方案

方案一：

quzrtz的dataSource增加如下配置，加上最大空闲时间，设置为60s：

org.quartz.datasource.qzDS.validateOnCheckout=true
org.quartz.datasource.qzDS.validationQuery=select 1
org.quartz.dataSource.myDS.discardIdleConnectionsSeconds=60

方案二：

自定义Druid数据库连接池，需要实现org.quartz.utils.ConnectionProvider接口,同时引入Druid相关的jar包，具体实现可以参考：https://blog.csdn.net/minicto/article/details/77897577或https://www.cnblogs.com/zouhao510/p/5313600.html

参考文章

posted @ 2018-09-28 17:12 cyd_shuihan 阅读(4410) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

cyd_shuihan

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

一、问题描述

二、问题分析

数据库连接池技术C3P0

Quartz数据库配置

三、Quartz数据源连接池

Quartz各版本数据库连接池技术

四、DBCP 和C3P0的使用和区别

DBCP 介绍

C3P0介绍

DBCP 和C3P0区别

DBCP和C3P0在Spring的配置

五、Quartz配置DataSources

六、解决方案

方案一：

方案二：

参考文章

公告