Spring Boot ShedLock "relation "shedlock" does not exist"

Question

I added ShedLock to my project to prevent working of scheduled job more than one time. I configured it like below but I'm getting

"org.postgresql.util.PSQLException: ERROR: relation "shedlock" does not exist" error.

This is lockProviderBean:

@Bean
    public LockProvider lockProvider(DataSource dataSource) {
        return new JdbcTemplateLockProvider(
                JdbcTemplateLockProvider.Configuration.builder()
                        .withJdbcTemplate(new JdbcTemplate(dataSource))
                        .usingDbTime() 
                        .build()
        );
    }

This is scheduled job:

@Scheduled(cron = "${cronProperty:0 00 23 * * *}")
@SchedulerLock(name = "schedulerLockName")
public void scheduledJob() {
       ..............
}

I added these notations to my class which contains schduledJob method:

@EnableScheduling
@Component
@Configuration
@EnableSchedulerLock(defaultLockAtMostFor = "2m")

I'm using Spring Data to do database operations and using these properties:

spring.datasource.url = jdbc:postgresql://ip:port/databaseName?currentSchema=schemeName
spring.datasource.driver-class-name = org.postgresql.Driver
spring.jpa.database = postgresql
spring.datasource.platform = postgresql
spring.datasource.hikari.maximum-pool-size=5
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.datasource.username = username
spring.datasource.password = password

Answer 1

If you are struggling with below error with shedlock scheduler implementation database entry

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO shedlock(name, lock_until, locked_at, locked_by) VALUES(?, ?, ?, ?)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Fix is just in created bean with LockProvider provide you schema name with table name

@Configuration
public class AppConfig {
 
    @Bean
    public LockProvider lockProvider(DataSource dataSource) {
        return new JdbcTemplateLockProvider(dataSource, "YOUR_SCHEMA_NAME.SHEDLOCK");
    }
}

Answer 2

maybe this is what you are missing:

If you need to specify a schema, you can set it in the table name using the usual dot notation new JdbcTemplateLockProvider(datasource, "my_schema.shedlock")

Answer 3

I face this problem too even though shedlock table has been created.

Workarounds for this is by

1. Setting pg's user default schema using ALTER ROLE YourPgUser SET search_path TO ... , or
2. Specifing shedlock schema on LockProvider bean

    @Bean
    public LockProvider getLockProvider(@Autowired JdbcTemplate jdbcTemplate) {
        jdbcTemplate.execute("SET search_path TO domaindbschema");
        return new JdbcTemplateLockProvider(jdbcTemplate);
    }

or another style

    @Bean
    public LockProvider getLockProvider(@Autowired JdbcTemplate jdbcTemplate) {
        return new JdbcTemplateLockProvider(jdbcTemplate, "domaindbschema.shedlock");
    }

Answer 4

You have to create the table as described in the documentation.

Answer 5

If This is in the server, you need to modify the privileges to the table. contact to BD team