Quarkus Quartz Extension not working with Micosoft SQL server since 3.2.x

[chvsnraju]

Describe the bug

We use Quartz scheduler for our cronjob executions. We started with Quarkus 2.6.x, and it is working until 3.1.x. When we migrated to 3.2.x, we see startup failures with the below exception. I tried with the latest 3.7.x, any help will be greatly appreciated.

It worked fine until 3.1.x, getting below exception anything 3.2.x beyond; I see some refactoring in the Quartz package with 3.2.x, but I need help finding the exact issue.

Can reproduce with docker mssql server container; we are on Java 17 with Hibernate.

15:47:11 ERROR {} [io.qu.ru.Application] (Quarkus Main Thread) (null) IP= reqId= Failed to start application (with profile {0}): java.lang.RuntimeException: Failed to start quarkus
at io.quarkus.runner.ApplicationImpl.doStart(Unknown Source)
at io.quarkus.runtime.Application.start(Application.java:101)
at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:111)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:71)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:44)
at io.quarkus.runtime.Quarkus.run(Quarkus.java:124)
at io.quarkus.runner.GeneratedMain.main(Unknown Source)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at io.quarkus.runner.bootstrap.StartupActionImpl$1.run(StartupActionImpl.java:113)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.lang.IllegalStateException: Unable to start Scheduler
at io.quarkus.quartz.runtime.QuartzSchedulerImpl.start(QuartzSchedulerImpl.java:511)
at io.quarkus.quartz.runtime.QuartzSchedulerImpl_Observer_start_H-6XoNLvj3wcGgHUsn1YAKHoJeM.notify(Unknown Source)
at io.quarkus.arc.impl.EventImpl$Notifier.notifyObservers(EventImpl.java:346)
at io.quarkus.arc.impl.EventImpl$Notifier.notify(EventImpl.java:328)
at io.quarkus.arc.impl.EventImpl.fire(EventImpl.java:82)
at io.quarkus.arc.runtime.ArcRecorder.fireLifecycleEvent(ArcRecorder.java:155)
at io.quarkus.arc.runtime.ArcRecorder.handleLifecycleEvents(ArcRecorder.java:106)
at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy_0(Unknown Source)
at io.quarkus.deployment.steps.LifecycleEventsBuildStep$startupEvent1144526294.deploy(Unknown Source)
... 13 more
Caused by: org.quartz.SchedulerConfigException: Failure occured during job recovery. [See nested exception: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR. [See nested exception: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.]]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.schedulerStarted(JobStoreSupport.java:697)
at org.quartz.core.QuartzScheduler.start(QuartzScheduler.java:539)
at org.quartz.impl.StdScheduler.start(StdScheduler.java:142)
at io.quarkus.quartz.runtime.QuartzSchedulerImpl.start(QuartzSchedulerImpl.java:509)

Expected behavior

Quartz Scheduler should work with Microsoft SQL Servers

Actual behavior

Having startup issues with Quarkus 3.2.x and beyond

How to Reproduce?

Use Quartz Scheduler extension
Create latest Microsoft SQL server docker container
load initial SQL server db scripts
startup the Quarkus servever

Output of uname -a or ver

No response

Output of java -version

No response

Quarkus version or git rev

No response

Build tool (ie. output of mvnw --version or gradlew --version)

No response

Additional information

No response

[quarkus-bot]

/cc @machi1990 (quartz), @mkouba (quartz)

[chvsnraju]

able to reproduct quickly with sample code : https://github.com/quarkusio/quarkus-quickstarts/tree/main/quartz-quickstart

1. created docker container with latest microsoft sql server
2. changed postgresql to mssql for drivers and configuration
3. changed flyway quarts sql script to mssql script from https://github.com/quartznet/quartznet/blob/main/database/tables/tables_sqlServer.sql
4. able to reproduct the issue

[mkouba]

Apparently it's not a Quarkus-specific issue - see for example quartz-scheduler/quartz#669.

The suggested solution is to use a different SQL select for MS SQL server: SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?; in case of Quarkus it's the quarkus-quartz-select-with-lock-sql config property.

[chvsnraju]

@mkouba - Thanks for the quick reply.

Do you have any idea why this issue started only after Quarkus 3.2.x? It was working fine until Quarkus 3.1.x

Also, I just tried adding this property in application.properties but still see the same, Do you have any further thoughts or help on this?

quarkus.quartz.select-with-lock-sql=SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?

[mkouba]

Do you have any idea why this issue started only after Quarkus 3.2.x? It was working fine until Quarkus 3.1.x

I have no idea. The quartz version is the same in 3.1 and 3.2.

Also, I just tried adding this property in application.properties but still see the same, Do you have any further thoughts or help on this?

Can you see the same exception?

[chvsnraju]

@mkouba - Yes, I see the same exact error even after I adding that property, I am thining somehow that is not getting applied

[chvsnraju]

@mkouba - I see a lot of differences in the Quartz package between 3.1.3 and. 3.2.0, seems some of the refactoring causing the issue, see below screenshots

Quarkus Quartz 3.1.3

Quarkus Quartz 3.2.0

[mkouba]

@chvsnraju I can see the problem now. There was an issue introduced in #33534 (3.2.0.CR1) and it was fixed in #35255 (3.2.4.Final).

So an upgrade to Quarkus 3.2.4.Final should solve the problem.

[chvsnraju]

@mkouba - Thanks for checking, I tried with 3.2.4.Final as well latest 3.7.2 with both the below property and without the property but still getting same issue, not sure if I am missing anything

quarkus.quartz.select-with-lock-sql=SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?

Caused by: org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR. [See nested exception: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.]

[mkouba]

I see. Well, then I'm running out of ideas...

I suppose that you have the quarkus.datasource.db-kind=mssql config property in your project? Could you share the whole quartz config section from your application.properties?

[chvsnraju]

@mkouba - thanks, Here is my properties file, it is just Quarkus sample code for Quartz, just changed to mssql.

The project is working fine when I switch to 3.1.3 but anything beyond 3.2.x is not working.

quarkus.scheduler.start-mode=forced
quarkus.quartz.store-type=jdbc-cmt
quarkus.quartz.select-with-lock-sql=SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?

quarkus.hibernate-orm.database.generation=none
quarkus.hibernate-orm.log.sql=true
quarkus.hibernate-orm.sql-load-script=no-file

quarkus.flyway.connect-retries=10
quarkus.flyway.table=flyway_quarkus_history
quarkus.flyway.migrate-at-start=true
quarkus.flyway.baseline-on-migrate=true
quarkus.flyway.baseline-version=1.0
quarkus.flyway.baseline-description=Quartz

quarkus.datasource.db-kind=mssql
quarkus.datasource.username=sa
quarkus.datasource.password=Database
quarkus.datasource.jdbc.url=jdbc:sqlserver://localhost:1433;database=quartzdb;encrypt=false
quarkus.datasource.jdbc.max-size=16

[mkouba]

Hm, your application.properties is missing the quarkus.quartz.clustered=true and it seems that quarkus.quartz.select-with-lock-sql is ignored unless the clustered mode is enabled...

[chvsnraju]

@mkouba - Thank you very much; you are the savior, finally able to make it work with combination of these properties, if one of them not there, I still see same error, thanks again, we are good now

quarkus.quartz.clustered=true
quarkus.quartz.select-with-lock-sql=SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?

[chvsnraju]

@mkouba - Do you want me to close this, or will this be addressed in future releases without additional SQL as property?

[mkouba]

@mkouba - Do you want me to close this, or will this be addressed in future releases without additional SQL as property?

Well, I'm not quite sure if this should be addressed in Quarkus. The original issue looks like a bug in Quartz MS SQL integration - so the special select is not something we should add in Quarkus. And the fact that quarkus.quartz.select-with-lock-sql is ignored unless the quarkus.quartz.clustered=true is set seems reasonable to me...