Error when using persistence jobs with quartz

[gfaerman]

Hi everybody,

RAM persistance is working fine with the following context configuration file

 <bean id="schedulerFactoryBean" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">
    <property name="transactionManager">
      <ref bean="transactionManager"/>
    </property>
    <property name="target">
      <ref local="schedulerFactoryBeanTarget"/>
    </property>
    <property name="transactionAttributes">
      <props>
        <prop key="*">PROPAGATION_REQUIRED</prop>
      </props>
    </property>
  </bean>
  <bean id="schedulerFactoryBeanTarget" class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
    <property name="autoStartup">
      <value>false</value>
    </property>
 </bean>

Now when I add a dataSource property (using jtds driver to access MS SQLServer 2000),

<bean id="schedulerFactoryBeanTarget" class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
    <property name="autoStartup">
      <value>false</value>
    </property>
    <property name="dataSource">
      <ref bean="dataSource"/>
    </property>
 </bean>

got an exception. I might be missing something. Any help would be great.

Thanks in advance,
Gustavo.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'scheduler' defined in class path resource [com/vaau/rbacx/context/test-scheduling-context.xml]: Can't resolve reference to bean 'schedulerFactoryBean' while setting property 'springScheduler'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'schedulerFactoryBean' defined in class path resource [com/vaau/rbacx/context/test-scheduling-context.xml]: Can't resolve reference to bean 'schedulerFactoryBeanTarget' while setting property 'target'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'schedulerFactoryBeanTarget' defined in class path resource [com/vaau/rbacx/context/test-scheduling-context.xml]: Initialization of bean failed; nested exception is org.quartz.SchedulerConfigException: Failure occured during job recovery.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'schedulerFactoryBean' defined in class path resource [com/vaau/rbacx/context/test-scheduling-context.xml]: Can't resolve reference to bean 'schedulerFactoryBeanTarget' while setting property 'target'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'schedulerFactoryBeanTarget' defined in class path resource [com/vaau/rbacx/context/test-scheduling-context.xml]: Initialization of bean failed; nested exception is org.quartz.SchedulerConfigException: Failure occured during job recovery.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'schedulerFactoryBeanTarget' defined in class path resource [com/vaau/rbacx/context/test-scheduling-context.xml]: Initialization of bean failed; nested exception is org.quartz.SchedulerConfigException: Failure occured during job recovery.

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: java.sql.SQLException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.]]
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.initialize(JobStoreSupport.java:504)
	at org.quartz.impl.jdbcjobstore.JobStoreCMT.initialize(JobStoreCMT.java:150)
	at org.springframework.scheduling.quartz.LocalDataSourceJobStore.initialize(LocalDataSourceJobStore.java:118)
	at org.quartz.impl.StdSchedulerFactory.instantiate(StdSchedulerFactory.java:915)
	at org.quartz.impl.StdSchedulerFactory.getScheduler(StdSchedulerFactory.java:1047)
	at org.springframework.scheduling.quartz.SchedulerFactoryBean.createScheduler(SchedulerFactoryBean.java:543)
	at org.springframework.scheduling.quartz.SchedulerFactoryBean.afterPropertiesSet(SchedulerFactoryBean.java:494)

* Nested Exception (Underlying Cause) ---------------

java.sql.SQLException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.
	at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:367)
	at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2282)
	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:1747)
	at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:515)
	at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:275)
	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:644)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
	at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.obtainLock(StdRowLockSemaphore.java:142)
	at org.quartz.impl.jdbcjobstore.JobStoreCMT.cleanVolatileTriggerAndJobs(JobStoreCMT.java:218)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.initialize(JobStoreSupport.java:501)
	at org.quartz.impl.jdbcjobstore.JobStoreCMT.initialize(JobStoreCMT.java:150)
	at org.springframework.scheduling.quartz.LocalDataSourceJobStore.initialize(LocalDataSourceJobStore.java:118)
	at org.quartz.impl.StdSchedulerFactory.instantiate(StdSchedulerFactory.java:915)
	at org.quartz.impl.StdSchedulerFactory.getScheduler(StdSchedulerFactory.java:1047)
	at org.springframework.scheduling.quartz.SchedulerFactoryBean.createScheduler(SchedulerFactoryBean.java:543)
	at org.springframework.scheduling.quartz.SchedulerFactoryBean.afterPropertiesSet(SchedulerFactoryBean.java:494)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1065)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:343)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:260)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:221)

* Nested Exception (Underlying Cause) ---------------

org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR. [See nested exception: java.sql.SQLException: Line 1: FOR UPDATE clause allowed only for DECLARE CURSOR.]
	at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.obtainLock(StdRowLockSemaphore.java:160)
	at org.quartz.impl.jdbcjobstore.JobStoreCMT.cleanVolatileTriggerAndJobs(JobStoreCMT.java:218)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.initialize(JobStoreSupport.java:501)
	at org.quartz.impl.jdbcjobstore.JobStoreCMT.initialize(JobStoreCMT.java:150)
	at org.springframework.scheduling.quartz.LocalDataSourceJobStore.initialize(LocalDataSourceJobStore.java:118)
	at org.quartz.impl.StdSchedulerFactory.instantiate(StdSchedulerFactory.java:915)
	at org.quartz.impl.StdSchedulerFactory.getScheduler(StdSchedulerFactory.java:1047)
	at org.springframework.scheduling.quartz.SchedulerFactoryBean.createScheduler(SchedulerFactoryBean.java:543)
	at org.springframework.scheduling.quartz.SchedulerFactoryBean.afterPropertiesSet(SchedulerFactoryBean.java:494)

[gfaerman]

I just added this line to my configuration (got it from thread http://forum.springframework.org/vie...rtz+properties)

<prop key="org.quartz.jobStore.selectWithLockSQL">SELECT * FROM &#123;0&#125;LOCKS UPDLOCK WHERE LOCK_NAME = ?</prop>

and tests are ok. But do not know the consequences.
It is clearly a Quartz forum oriented question, but just in case, would anyone share with us what was going wrong and what this line fixes?

Thanks,
Gustavo.

[croudet]

Found in Quartz JavaStoreSupport:

    /**
     * <p>
     * set the SQL statement to use to select and lock a row in the "locks"
     * table.
     * </p>
     * 
     * @see StdRowLockSemaphore
     */
    public void setSelectWithLockSQL&#40;String string&#41; &#123;
        selectWithLockSQL = string;
    &#125;

[gfaerman]

Thxs Christophe!

[amirsky]

Sorry for resurrecting a yea ol'thread but I had recently encoutered this issue and the suggested property setting above did fix it. However I am not sure if there could be an unwanted sideffect.

The documentation for Quartz says:
org.quartz.jobStore.selectWithLockSQL
Must be a SQL string that selects a row in the "LOCKS" table and places a lock on the row. If not set, the default is "SELECT * FROM {0}LOCKS WHERE LOCK_NAME = ? FOR UPDATE", which works for most databases. The "{0}" is replaced during run-time with the TABLE_PREFIX that you configured above.

Does changing the select to "SELECT * FROM {0}LOCKS UPDLOCK WHERE LOCK_NAME = ?" as suggested still fullfill the requirement Quartz describes above? Or could it lead to some terrible times with locking issues and conflicts?

Thanks in advance!