I have configured the application properties to create quartz schemas when application starts but it does not create the tables and throw SQLServer exception. I am using quartz starter version 2.2.0 and MSSQL 2017.
EDIT: I have manually created the schema and it still gives the same exceptions. So I believe the problem is that quartz don't know which datasource to use.
Here is my spring configuration:
spring.datasource.url=jdbc:sqlserver://127.0.0.1:51433;database=xxxx
spring.datasource.username=xxxxx
spring.datasource.password=xxxxx
spring.datasource.initialization-mode=never
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.xa.data-source-class-name=com.microsoft.sqlserver.jdbc.SQLServerXADataSource
spring.datasource.testOnBorrow=true
spring.datasource.testWhileIdle=true
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.validationQuery=SELECT 1
spring.quartz.job-store-type=jdbc
spring.quartz.jdbc.initialize-schema=always
spring.quartz.wait-for-jobs-to-complete-on-shutdown=true
spring.quartz.properties.org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreTX
spring.quartz.properties.org.quartz.scheduler.instanceName=xxxx
spring.quartz.properties.org.quartz.scheduler.instanceId=AUTO
spring.quartz.properties.org.quartz.jobStore.isClustered=true
and the error log
org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:184) ~[quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:113) ~[quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.doCheckin(JobStoreSupport.java:3335) [quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.manage(JobStoreSupport.java:3935) [quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.initialize(JobStoreSupport.java:3920) [quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.schedulerStarted(JobStoreSupport.java:692) [quartz-2.3.1.jar:na]
at org.quartz.core.QuartzScheduler.start(QuartzScheduler.java:539) [quartz-2.3.1.jar:na]
at org.quartz.impl.StdScheduler.start(StdScheduler.java:142) [quartz-2.3.1.jar:na]
at org.springframework.scheduling.quartz.SchedulerFactoryBean.startScheduler(SchedulerFactoryBean.java:728) [spring-context-support-5.2.0.RELEASE.jar:5.2.0.RELEASE]
at org.springframework.scheduling.quartz.SchedulerFactoryBean.start(SchedulerFactoryBean.java:800) [spring-context-support-5.2.0.RELEASE.jar:5.2.0.RELEASE]
at org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:182) [spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
at org.springframework.context.support.DefaultLifecycleProcessor.access$200(DefaultLifecycleProcessor.java:53) [spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
at org.springframework.context.support.DefaultLifecycleProcessor$LifecycleGroup.start(DefaultLifecycleProcessor.java:360) [spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
at org.springframework.context.support.DefaultLifecycleProcessor.startBeans(DefaultLifecycleProcessor.java:158) [spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
at org.springframework.context.support.DefaultLifecycleProcessor.onRefresh(DefaultLifecycleProcessor.java:122) [spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:894) [spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.finishRefresh(ServletWebServerApplicationContext.java:162) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:553) [spring-context-5.2.0.RELEASE.jar:5.2.0.RELEASE]
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) [spring-boot-2.2.0.RELEASE.jar:2.2.0.RELEASE]
at com.company.customer.batch.CompanyApplication.main(CompanyApplication.java:33) [classes/:na]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at bitronix.tm.resource.jdbc.JdbcUncachedPreparedStatementHandle.executeQuery(JdbcUncachedPreparedStatementHandle.java:81) ~[btm-2.1.4.jar:2.1.4]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_271]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_271]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_271]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_271]
at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:64) ~[btm-2.1.4.jar:2.1.4]
at com.sun.proxy.$Proxy125.executeQuery(Unknown Source) ~[na:na]
at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:123) ~[quartz-2.3.1.jar:na]
... 24 common frames omitted
2021-01-05 10:01:08.049 INFO 11296 --- [ main] org.quartz.core.QuartzScheduler : Scheduler customer-batch_$_192.168.1.43_8f3f3040-8fda-4136-b484-428388f605d6 started.
2021-01-05 10:01:08.123 ERROR 11296 --- [_MisfireHandler] o.s.s.quartz.LocalDataSourceJobStore : MisfireHandler: Error handling misfires: Database error recovering from misfires.
org.quartz.JobPersistenceException: Database error recovering from misfires.
at org.quartz.impl.jdbcjobstore.JobStoreSupport.doRecoverMisfires(JobStoreSupport.java:3274) ~[quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport$MisfireHandler.manage(JobStoreSupport.java:4012) [quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport$MisfireHandler.run(JobStoreSupport.java:4033) [quartz-2.3.1.jar:na]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'QRTZ_TRIGGERS'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at bitronix.tm.resource.jdbc.JdbcUncachedPreparedStatementHandle.executeQuery(JdbcUncachedPreparedStatementHandle.java:81) ~[btm-2.1.4.jar:2.1.4]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_271]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_271]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_271]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_271]
at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:64) ~[btm-2.1.4.jar:2.1.4]
at com.sun.proxy.$Proxy125.executeQuery(Unknown Source) ~[na:na]
at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.countMisfiredTriggersInState(StdJDBCDelegate.java:393) ~[quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.doRecoverMisfires(JobStoreSupport.java:3253) ~[quartz-2.3.1.jar:na]
... 2 common frames omitted
2021-01-05 10:01:08.127 INFO 11296 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 9005 (http) with context path ''
2021-01-05 10:01:08.131 INFO 11296 --- [ main] c.n.i.batch.CompanyApplication: Started CompanyApplicationin 27.316 seconds (JVM running for 28.526)
2021-01-05 10:01:08.131 ERROR 11296 --- [SchedulerThread] org.quartz.core.ErrorLogger : An error occurred while scanning for the next triggers to fire.
org.quartz.JobPersistenceException: Couldn't acquire next trigger: Invalid object name 'QRTZ_TRIGGERS'.
at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:2923) ~[quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport$41.execute(JobStoreSupport.java:2805) ~[quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport$41.execute(JobStoreSupport.java:2803) ~[quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3864) ~[quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTriggers(JobStoreSupport.java:2802) ~[quartz-2.3.1.jar:na]
at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:287) ~[quartz-2.3.1.jar:na]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'QRTZ_TRIGGERS'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:602) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7375) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3206) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446) ~[mssql-jdbc-8.4.1.jre8.jar:na]
at bitronix.tm.resource.jdbc.JdbcUncachedPreparedStatementHandle.executeQuery(JdbcUncachedPreparedStatementHandle.java:81) ~[btm-2.1.4.jar:2.1.4]
at sun.reflect.GeneratedMethodAccessor82.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_271]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_271]
at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:64) ~[btm-2.1.4.jar:2.1.4]
at com.sun.proxy.$Proxy125.executeQuery(Unknown Source) ~[na:na]
at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.selectTriggerToAcquire(StdJDBCDelegate.java:2613) ~[quartz-2.3.1.jar:na]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.acquireNextTrigger(JobStoreSupport.java:2844) ~[quartz-2.3.1.jar:na]
... 5 common frames omitted
Any ideas how to fix it?
The remove .scheduler from property name.
The property name is not....
spring.quartz.properties.org.quartz.scheduler.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate
...as railey suggested but:
spring.quartz.properties.org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.MSSQLDelegate