Spring boot quartz schema other than public doesn't work

Question

I am not able to use other schema (than public) for quartz tables. This is my quartz setup:

spring.quartz.job-store-type=jdbc
spring.quartz.jdbc.initialize-schema=always
spring.quartz.properties.org.quartz.jobStore.driverDelegateClass=org.quartz.impl.jdbcjobstore.PostgreSQLDelegate
spring.quartz.properties.org.quartz.jobStore.isClustered=true
spring.quartz.properties.org.quartz.jobStore.clusterCheckinInterval=2000
spring.quartz.properties.org.quartz.scheduler.instanceId=AUTO
spring.quartz.properties.org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
spring.quartz.properties.org.quartz.jobStore.useProperties=false
spring.quartz.properties.org.quartz.jobStore.tablePrefix=QRTZ_

And config class:

@Bean
public SchedulerFactoryBean schedulerFactory(ApplicationContext applicationContext, DataSource dataSource, QuartzProperties quartzProperties) {
    SchedulerFactoryBean schedulerFactoryBean = new SchedulerFactoryBean();
    AutowireCapableBeanJobFactory jobFactory = new AutowireCapableBeanJobFactory(applicationContext.getAutowireCapableBeanFactory());
    Properties properties = new Properties();
    properties.putAll(quartzProperties.getProperties());

    schedulerFactoryBean.setOverwriteExistingJobs(true);
    schedulerFactoryBean.setDataSource(dataSource);
    schedulerFactoryBean.setQuartzProperties(properties);
    schedulerFactoryBean.setJobFactory(jobFactory);

    return schedulerFactoryBean;
}

@Bean
public Scheduler scheduler(ApplicationContext applicationContext, DataSource dataSource, QuartzProperties quartzProperties)
    throws SchedulerException {
    Scheduler scheduler = schedulerFactory(applicationContext, dataSource, quartzProperties).getScheduler();
    scheduler.start();
    return scheduler;
}

This works fine, and the tables are getting created. However I would like to have the tables in a different schema. So I set quartz to use 'quartz' schema.

spring.quartz.properties.org.quartz.jobStore.tablePrefix=quartz.QRTZ_

This is the error I'm getting:

[ClusterManager: Error managing cluster: Failure obtaining db row lock: ERROR: current transaction is aborted, commands ignored until end of transaction block] [org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: ERROR: current transaction is aborted, commands ignored until end of transaction block

Any ideas on how to solve it?

xerx593 · Answer 1 · 2021-10-01T13:29:07.790

It was a bold hope that "tablePrefix" can also adjsut the "db schema", (and there is no documented property concerning "db schema"), but you could get more lucky, if you configure it on the datasource. i.e. you would introduce/configure different (spring) datasource( bean)s for every user/schema used by your application ...

(like here:) Spring Boot Configure and Use Two DataSources
or here

, then you'd wire the scheduler factory with the appropriate datasource (quartz).

schedulerFactoryBean.setDataSource(quartzDataSource);

Or via (@Autowired) parameter injection, or method invocation : @Bean initialization - difference between parameter injection vs. direct method access?

UPDATE (regarding "wiring"):

..from current spring-boot doc:

To have Quartz use a DataSource other than the application’s main DataSource, declare a DataSourcebean, annotating its @Bean method with @QuartzDataSource. Doing so ensures that the Quartz-specific DataSource is used by both the SchedulerFactoryBean and for schema initialization. Similarly, to have Quartz use a TransactionManager other than the application’s main ... declare a TransactionManager bean, ...@QuartzTransactionManager.

You can take even more control by customizing:

spring.quartz.jdbc.initialize-schema 
Database schema initialization mode.
default: embedded (embedded|always|never)

spring.quartz.jdbc.schema 
Path to the SQL file to use to initialize the database schema.
default: classpath:org/quartz/impl/jdbcjobstore/tables_@@platform@@.sql

... properties, where @@platform@@ refers to your db vendor.

But it is useless for your requirement... since looking at and complying with the original schemes - they seem schema independent/free. (So the data source approach looks more promising, herefor.)

REFS:

I've upvoted your answer as it brings valuable information. Still, I got the idea with tablePrefix=schema.tablePrefix from stack overflow research, It seems it works for other people. EG. https://stackoverflow.com/questions/64045340/how-to-set-the-database-schema-for-the-quartz-scheduler-with-spring-boot-and-fly Can't find the exact example. Maybe someone found a workaround — UnguruBulan, Oct 01 '21 at 07:47
thank you for upvote and interesting facts! ...s i tried, but didn't emphasize enough: all of these user/schema/data source "things" are very vendor-specific. *And* the (datasource) [`default_schema`](https://stackoverflow.com/q/2737420/592355) + initialization details are of course relevant. .. On what (kind of) db do you operate? — xerx593, Oct 01 '21 at 12:09
and (looking again at the [built-in schemes](https://github.com/quartz-scheduler/quartz/tree/master/quartz-core/src/main/resources/org/quartz/impl/jdbcjobstore)) we must realize that any other table_prefix than `QRTZ_` should fail (using that scripts) — xerx593, Oct 01 '21 at 12:39
I'm using postgres. I even saw someone using Quartz.NET and said the 'hack' with tablePrefix worked. — UnguruBulan, Oct 01 '21 at 15:27
Found a workoaround. Will let flyway create the scripts in the diferent schema, left the tablePrefix=schema.QRTZ_. Works as a charm — UnguruBulan, Oct 01 '21 at 15:29

score 1 · Answer 2 · answered Oct 01 '21 at 15:34

So the idea is that Quartz doesn't create the tables using spring.quartz.properties.org.quartz.jobStore.tablePrefix

Table names are static. Eg qrtz_triggers. as @xerx593 pointed out.

What we can do is to create the tables (manual, flyway, liquibase) in a different schema, update tablePrefix=schema.qrtz_ and it will work.

Tested with Postgres

Spring boot quartz schema other than public doesn't work

2 Answers2

UPDATE (regarding "wiring"):