CODEWITHSUNDEEP
javamysqlspringquartz-scheduler
davioooh
davioooh

Reputation: 24706

Quartz JDBCJobStore problems with MySQL

I'm trying to use Job persistence via job store in my spring mvc project, but something seems wrong in my database structure.

My database is MySQL (v 5.6.15) and I created tables using scripts in Quartz jar (v 1.8.6).

Executing some test I found that jobs and triggers are persisted in DB and (I hope) correctly loaded, but they aren't executed. Trigger status is always on "WAITING".

I tried creating tables structure on a SqlServer instance (always using scripts provided) and, now it works!!! So, what's wrong with MySql?

The only difference I notice is that table names in MySQL are lowercase, instead of all uppercase as in SqlServer, could it be a problem?

My configuration is the following:

Scheduler configuration via spring

<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
    <property name="quartzProperties">
        <map>
            <entry key="org.quartz.scheduler.instanceName" value="gep_scheduler" />
            <!-- ThreadPool -->
            <entry key="org.quartz.threadPool.class" value="org.quartz.simpl.SimpleThreadPool" />
            <entry key="org.quartz.threadPool.threadCount" value="3" />
            <entry
                key="org.quartz.threadPool.threadsInheritContextClassLoaderOfInitializingThread"
                value="true" />
            <!-- JobStore -->
            <entry key="org.quartz.jobStore.class" value="org.quartz.impl.jdbcjobstore.JobStoreTX" />
            <entry key="org.quartz.jobStore.driverDelegateClass" value="org.quartz.impl.jdbcjobstore.StdJDBCDelegate" />
            <entry key="org.quartz.jobStore.tablePrefix" value="qrtz_" />
            <entry key="org.quartz.jobStore.dataSource" value="qzDS" />
            <entry key="org.quartz.jobStore.isClustered" value="false" />
            <entry key="org.quartz.jobStore.misfireThreshold" value="60000" />
            <!-- DataSource -->
            <entry key="org.quartz.dataSource.qzDS.driver" value="${db.driver}" />
            <entry key="org.quartz.dataSource.qzDS.URL" value="${db.url}" />
            <entry key="org.quartz.dataSource.qzDS.user" value="${db.user}" />
            <entry key="org.quartz.dataSource.qzDS.password" value="${db.password}" />
            <entry key="org.quartz.dataSource.qzDS.maxConnections" value="${db.maxActivePools}" />
            </map>
    </property>
</bean>

NOTE no exception is thrown.

Upvotes: 1

Views: 3521

Answers (1)

davioooh
davioooh

Reputation: 24706

I finally caught an exception from the scheduler:

org.quartz.JobPersistenceException: Couldn't acquire next trigger: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=5' at line 1

The problem was in MySQL driver. I was using v5.1.6 that seems not compatible with MySQL 5.6.x versions.

I solved importing last version of mysql-connector (at the moment v5.1.31).

Upvotes: 5

Related Questions