Grails Quartz Plugin error with jdbcStore

Question

I trying to use the latest quartz plugin from Grails : quartz:1.0.1.

It works if I leave the jdbcStore = false, fails if I set to true. QuartzConfig.groovy:

    quartz {
       autoStartup = true
       jdbcStore = true
       waitForJobsToCompleteOnShutdown = true
       exposeSchedulerInRepository = false

    props {
        scheduler.skipUpdateCheck = true
     }
    }

Here is the property file:

    org.quartz.scheduler.instanceName reporting_test
    org.quartz.scheduler.instanceId AUTO

    org.quartz.threadPool.class org.quartz.simpl.SimpleThreadPool
    org.quartz.threadPool.threadCount 5
    org.quartz.threadPool.threadPriority 5

    org.quartz.jobStore.misfireThreshold 60000

    org.quartz.jobStore.class org.quartz.impl.jdbcjobstore.JobStoreTX
    org.quartz.jobStore.driverDelegateClass org.quartz.impl.jdbcjobstore.StdJDBCDelegate

    org.quartz.jobStore.useProperties false
    org.quartz.jobStore.tablePrefix QRTZ_
    org.quartz.jobStore.isClustered true
    org.quartz.jobStore.clusterCheckinInterval 5000

    org.quartz.plugin.shutdownhook.class org.quartz.plugins.management.ShutdownHookPlugin
    org.quartz.plugin.shutdownhook.cleanShutdown true

I have been trying to experiment with the Datasource setting as I would prefer to use my default DS set up in Datasource.groovy. Regardless of settings for the datasource I still get the following error message on start up.

        2013-12-18 13:43:14,693 [localhost-startStop-1] ERROR context.GrailsContextLoader  - Error initializing the application: 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.]
        org.codehaus.groovy.runtime.InvokerInvocationException: 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 java.util.concurrent.FutureTask.run(FutureTask.java:262)
            at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
            at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
            at java.lang.Thread.run(Thread.java:744)
        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.]
            at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:157)
            at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:113)
            at org.quartz.impl.jdbcjobstore.JobStoreCMT.executeInLock(JobStoreCMT.java:238)
            at org.quartz.impl.jdbcjobstore.JobStoreSupport.storeJob(JobStoreSupport.java:1086)
            at org.quartz.core.QuartzScheduler.addJob(QuartzScheduler.java:969)
            at org.quartz.core.QuartzScheduler.addJob(QuartzScheduler.java:958)
            at org.quartz.impl.StdScheduler.addJob(StdScheduler.java:268)
            at QuartzGrailsPlugin.scheduleJob(QuartzGrailsPlugin.groovy:268)
            at QuartzGrailsPlugin$_closure5_closure23.doCall(QuartzGrailsPlugin.groovy:248)
            at QuartzGrailsPlugin$_closure5.doCall(QuartzGrailsPlugin.groovy:247)
            ... 4 more
        Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
            at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
            at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
            at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:96)
            ... 13 more

The tables exist in my database. I actually was using them with quartz2 plugin for grails, however for various reasons decided to try out this plugin.

Any ideas on what i am setting up incorrectly?

Answer 1

Found the answer.

There is a query that is being run to lock the rows, in this case when failing it was:

SELECT * FROM QRTZ_LOCKS WHERE SCHED_NAME = 'reporting_test' AND LOCK_NAME = 'STATE_ACCESS' FOR UPDATE

The FOR UPDATE does not work for MSSQL Database. I found i was using the wrong delegate. I switched my property to:

org.quartz.jobStore.driverDelegateClass org.quartz.impl.jdbcjobstore.MSSQLDelegate

This worked, hope this helps someone else.