spring-boot-starter-quartz jdbc example

Question

Starting with spring-boot 2.x.x they started offering spring-boot-starter-quartz which is great! Out of the box it does an in-memory store. I want to change it to be a clustered environment but I'm having issues with the configuration I think mostly because I need to put the qrtz_ tables in a different schema than my default data source. Does anyone have an example of using an alternate datasource? I'm currently attempting to set the properties field (as you can see below) but its like they are not being picked up by the configuration bean. Any help is appreciated.

Configuration

spring:
  quartz:
    job-store-type: jdbc
    jdbc:
      initialize-schema: never
    properties:
      scheduler:
        instanceName : MyClusteredScheduler
        instanceId : AUTO
      threadPool:
        class : org.quartz.simpl.SimpleThreadPool
        threadCount : 25
        threadPriority : 5
      jobStore:
        misfireThreshold : 60000
        class : org.quartz.impl.jdbcjobstore.JobStoreTX
        driverDelegateClass : org.quartz.impl.jdbcjobstore.StdJDBCDelegate
        useProperties : false
        dataSource : myDS
        tablePrefix : QRTZ_
        isClustered : true
        clusterCheckinInterval : 20000
      dataSource:
        myDS:
          driver : com.mysql.jdbc.Driver
          URL : jdbc:mysql://127.0.0.1:3306/quartz
          user : removed
          password : removed
          maxConnections : 5
          validationQuery : select 0 from dual

Output from log

2017-11-06 13:33:02.853  INFO 7082 --- [           main] org.quartz.core.QuartzScheduler          : Quartz Scheduler v.2.3.0 created.
2017-11-06 13:33:02.856  INFO 7082 --- [           main] o.s.s.quartz.LocalDataSourceJobStore     : Using db table-based data access locking (synchronization).
2017-11-06 13:33:02.858  INFO 7082 --- [           main] o.s.s.quartz.LocalDataSourceJobStore     : JobStoreCMT initialized.
2017-11-06 13:33:02.859  INFO 7082 --- [           main] org.quartz.core.QuartzScheduler          : Scheduler meta-data: Quartz Scheduler (v2.3.0) 'quartzScheduler' with instanceId 'NON_CLUSTERED'
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 10 threads.
Using job-store 'org.springframework.scheduling.quartz.LocalDataSourceJobStore' - which supports persistence. and is not clustered.

2017-11-06 13:33:02.859  INFO 7082 --- [           main] org.quartz.impl.StdSchedulerFactory      : Quartz scheduler 'quartzScheduler' initialized from an externally provided properties instance.
2017-11-06 13:33:02.860  INFO 7082 --- [           main] org.quartz.impl.StdSchedulerFactory      : Quartz scheduler version: 2.3.0
2017-11-06 13:33:02.860  INFO 7082 --- [           main] org.quartz.core.QuartzScheduler          : JobFactory set to: org.springframework.boot.autoconfigure.quartz.AutowireCapableBeanJobFactory@21132086
2017-11-06 13:33:03.214  INFO 7082 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2017-11-06 13:33:03.216  INFO 7082 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Bean with name 'dataSource' has been autodetected for JMX exposure
2017-11-06 13:33:03.223  INFO 7082 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource]
2017-11-06 13:33:03.227  INFO 7082 --- [           main] o.s.c.support.DefaultLifecycleProcessor  : Starting beans in phase 2147483647
2017-11-06 13:33:03.227  INFO 7082 --- [           main] o.s.s.quartz.SchedulerFactoryBean        : Starting Quartz Scheduler now
2017-11-06 13:33:05.250  WARN 7082 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.context.ApplicationContextException: Failed to start bean 'quartzScheduler'; nested exception is org.springframework.scheduling.SchedulingException: Could not start Quartz Scheduler; nested exception is org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: No database selected [See nested exception: java.sql.SQLException: No database selected]]
2017-11-06 13:33:05.250  INFO 7082 --- [           main] o.s.s.quartz.SchedulerFactoryBean        : Shutting down Quartz Scheduler
2017-11-06 13:33:05.251  INFO 7082 --- [           main] org.quartz.core.QuartzScheduler          : Scheduler quartzScheduler_$_NON_CLUSTERED shutting down.
2017-11-06 13:33:05.251  INFO 7082 --- [           main] org.quartz.core.QuartzScheduler          : Scheduler quartzScheduler_$_NON_CLUSTERED paused.
2017-11-06 13:33:05.251  INFO 7082 --- [           main] org.quartz.core.QuartzScheduler          : Scheduler quartzScheduler_$_NON_CLUSTERED shutdown complete.
2017-11-06 13:33:05.252  INFO 7082 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown
2017-11-06 13:33:05.253  INFO 7082 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans
2017-11-06 13:33:05.254  INFO 7082 --- [           main] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2017-11-06 13:33:05.255  INFO 7082 --- [           main] com.zaxxer.hikari.HikariDataSource       : testdb - Shutdown initiated...
2017-11-06 13:33:05.264  INFO 7082 --- [           main] com.zaxxer.hikari.HikariDataSource       : testdb - Shutdown completed.
2017-11-06 13:33:05.265  INFO 7082 --- [           main] o.apache.catalina.core.StandardService   : Stopping service [Tomcat]
2017-11-06 13:33:05.283  INFO 7082 --- [           main] utoConfigurationReportLoggingInitializer : 

Error starting ApplicationContext. To display the auto-configuration report re-run your application with 'debug' enabled.
2017-11-06 13:33:05.293 ERROR 7082 --- [           main] o.s.boot.SpringApplication               : Application startup failed

org.springframework.context.ApplicationContextException: Failed to start bean 'quartzScheduler'; nested exception is org.springframework.scheduling.SchedulingException: Could not start Quartz Scheduler; nested exception is org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: No database selected [See nested exception: java.sql.SQLException: No database selected]]
at org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:186) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
at org.springframework.context.support.DefaultLifecycleProcessor.access$200(DefaultLifecycleProcessor.java:52) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
at org.springframework.context.support.DefaultLifecycleProcessor$LifecycleGroup.start(DefaultLifecycleProcessor.java:358) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
at org.springframework.context.support.DefaultLifecycleProcessor.startBeans(DefaultLifecycleProcessor.java:159) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
at org.springframework.context.support.DefaultLifecycleProcessor.onRefresh(DefaultLifecycleProcessor.java:123) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:884) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.finishRefresh(ServletWebServerApplicationContext.java:143) ~[spring-boot-2.0.0.M5.jar:2.0.0.M5]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:552) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:122) ~[spring-boot-2.0.0.M5.jar:2.0.0.M5]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:750) [spring-boot-2.0.0.M5.jar:2.0.0.M5]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:386) [spring-boot-2.0.0.M5.jar:2.0.0.M5]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:327) [spring-boot-2.0.0.M5.jar:2.0.0.M5]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1245) [spring-boot-2.0.0.M5.jar:2.0.0.M5]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1233) [spring-boot-2.0.0.M5.jar:2.0.0.M5]
at com.tci.reader.incident.parser.IncidentParserApplication.main(IncidentParserApplication.java:18) [classes/:na]
Caused by: org.springframework.scheduling.SchedulingException: Could not start Quartz Scheduler; nested exception is org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: No database selected [See nested exception: java.sql.SQLException: No database selected]]
at org.springframework.scheduling.quartz.SchedulerFactoryBean.start(SchedulerFactoryBean.java:738) ~[spring-context-support-5.0.0.RELEASE.jar:5.0.0.RELEASE]
at org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:183) ~[spring-context-5.0.0.RELEASE.jar:5.0.0.RELEASE]
... 14 common frames omitted
Caused by: org.quartz.SchedulerConfigException: Failure occured during job recovery.
at org.quartz.impl.jdbcjobstore.JobStoreSupport.schedulerStarted(JobStoreSupport.java:697) ~[quartz-2.3.0.jar:na]
at org.quartz.core.QuartzScheduler.start(QuartzScheduler.java:539) ~[quartz-2.3.0.jar:na]
at org.quartz.impl.StdScheduler.start(StdScheduler.java:142) ~[quartz-2.3.0.jar:na]
at org.springframework.scheduling.quartz.SchedulerFactoryBean.startScheduler(SchedulerFactoryBean.java:664) ~[spring-context-support-5.0.0.RELEASE.jar:5.0.0.RELEASE]
at org.springframework.scheduling.quartz.SchedulerFactoryBean.start(SchedulerFactoryBean.java:735) ~[spring-context-support-5.0.0.RELEASE.jar:5.0.0.RELEASE]
... 15 common frames omitted
Caused by: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: No database selected
at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:157) ~[quartz-2.3.0.jar:na]
at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:113) ~[quartz-2.3.0.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3842) ~[quartz-2.3.0.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.recoverJobs(JobStoreSupport.java:839) ~[quartz-2.3.0.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.schedulerStarted(JobStoreSupport.java:695) ~[quartz-2.3.0.jar:na]
... 19 common frames omitted
Caused by: java.sql.SQLException: No database selected
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966) ~[mysql-connector-java-5.1.44.jar:5.1.44]
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-2.7.2.jar:na]
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) ~[HikariCP-2.7.2.jar:na]
at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:96) ~[quartz-2.3.0.jar:na]
... 23 common frames omitted

It seems spring-boot-starter-quartz is so well documented, that noone could provide an answer in 3 months — Vlad Dinulescu, Feb 27 '18 at 17:53

score 1 · Answer 1 · answered Mar 28 '18 at 12:13

You have to use the correct properties for the quartz properties. Your properties are missing ...org.quartz...

For the none-yml notation, try this for the threadCount: spring.quartz.properties.org.quartz.threadPool.threadCount=25

For the yml notation:
Add two layers under the 'properties', e.g.

spring:
  quartz:
    properties:
      org:
        quartz:
          threadPool:
            threadCount : 25

score -1 · Answer 2 · answered Dec 08 '17 at 17:37

Application.yml file:

spring:
  jpa:
    hibernate:
      ddl-auto: none
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/Quartz
    username: Your Username
    password: Your Password

quartz:
  scheduler:
    instanceName: Scheduler
  threadPool:
    class: org.quartz.simpl.SimpleThreadPool
    threadCount: 3
  context:
    key:
      QuartzTopic: QuartzPorperties
  jobStore:
    driver: com.mysql.jdbc.Driver
    class: org.quartz.impl.jdbcjobstore.JobStoreTX
    driverDelegateClass: org.quartz.impl.jdbcjobstore.StdJDBCDelegate
    misfireThreshold: 6000
    tablePrefix: QRTZ_

Next create a Configuration class which reads these properties from the yml file and then creates a Scheduler Bean:

@Configuration
public class SchedulerConfig {

    private final Logger logger = LoggerFactory.getLogger(this.getClass());
    @Value("${quartz.scheduler.instanceName}")
    private String instanceName;
    @Value("${quartz.threadPool.class}")
    private String threadClass;
    @Value("${quartz.threadPool.threadCount}")
    private String threadCount;
    @Value("${quartz.context.key.QuartzTopic}")
    private String quartzTopic;
    @Value("${quartz.jobStore.class}")
    private String jobStoreClass;
    @Value("${quartz.jobStore.driverDelegateClass}")
    private String driverDelegateClass;
    @Value("${quartz.jobStore.misfireThreshold}")
    private String misfireThreshold;
    @Value("${quartz.jobStore.tablePrefix}")
    private String tablePrefix; 
    @Value("${quartz.jobStore.driver}")
    private String dataSourceDriver;
    @Value("${spring.datasource.url}")
    private String dataSourceUrl;
    @Value("${spring.datasource.username}")
    private String dataSourceUsername;
    @Value("${spring.datasource.password}")
    private String databasePassword;

    @Bean({"Scheduler"})
    public Scheduler getScheduler() {
        Scheduler scheduler = null;
        try {
            StdSchedulerFactory factory = new StdSchedulerFactory();
            Properties props = new Properties();
            props.put("org.quartz.scheduler.instanceName", instanceName);
            props.put("org.quartz.threadPool.class", threadClass);
            props.put("org.quartz.threadPool.threadCount", threadCount);
            props.put("org.quartz.context.key.QuartzTopic", quartzTopic);
            props.put("org.quartz.jobStore.class", jobStoreClass);
            props.put("org.quartz.jobStore.driverDelegateClass", driverDelegateClass);
            props.put("quartz.jobStore.misfireThreshold", misfireThreshold);
            props.put("org.quartz.jobStore.tablePrefix", tablePrefix);
            props.put("org.quartz.jobStore.dataSource", "myDS");
            props.put("org.quartz.dataSource.myDS.driver", dataSourceDriver);
            props.put("org.quartz.dataSource.myDS.URL", dataSourceUrl);
            props.put("org.quartz.dataSource.myDS.user", dataSourceUsername);
            props.put("org.quartz.dataSource.myDS.password", databasePassword);
            props.put("org.quartz.dataSource.myDS.maxConnections", "10");
            factory.initialize(props);
            scheduler = factory.getScheduler();
            scheduler.start();
            scheduler.resumeAll(); // This is to resume the entire scheduler when a new build is deployed
        } catch (Exception e) {
            logger.error("{} - SchedulerConfig class - getScheduler() - Error creating Scheduler instance: {}",
                    appName, e);
        }
        return scheduler;
    }
}

Finally, use the Bean as below in any class you want:

@Autowired
    private Scheduler scheduler;

spring-boot-starter-quartz jdbc example

2 Answers2