[BUG]DatabaseCreator validateTables fails when no table-prefix is set

[ibrok]

JobRunr Version

7.1.1

JDK Version

17.0.0-tem

Your SQL / NoSQL database

Postgres 15.5

What happened?

We ran into issues while trying to update from version 6.3.5 to 7.1.1.

We manually add the migration scripts to flyway and have no table-prefix set in the spring configuration.

This worked fine for both our integration tests (which had a schema public), and our test and production environments which each have different database schema's. As we configure the schema in our flyway/datesource config, our sql scripts do not use or mention any schema in the migration scripts, just plain table names. (for configuration see how to reproduce section)

After updating to 7.1.1 we faced issues during startup, both during integration tests and on our test environment with following error:
Failed to instantiate [org.jobrunr.storage.StorageProvider]: Factory method 'sqlStorageProvider' threw exception with message: Not all required tables are available by JobRunr!

After reproducing the issue in a test and debugging it, it seems the DatabaseCreator.validateTables takes the tableSchema into account for the completeTableName, even when no schema or prefix is set. So the expectedTables contains no schema and prefix, while the completeTableName does include a schema.

A workaround for now for us is to set the table-prefix as follows:

org:
  jobrunr:
    database:
      skip-create: true
      table-prefix: ${DB_SCHEMA}.    

But given as table-prefix appears to be an optional setting, the validateTables should probably not care about schema if table-prefix is not set.

How to reproduce?

spring:
  flyway:
    schemas:
      - ${DB_SCHEMA}
  datasource:
    hikari:
      schema: ${DB_SCHEMA}

org:
  jobrunr:
    database:
      skip-create: true

We then wrote a unit test using the following spring annotations. The content of the test don't really matter much, as the failure is on spring context startup.

@DataJpaTest
@Import(Repository::class, DataSourceAutoConfiguration::class, JobRunrSqlStorageAutoConfiguration::class, JobRunrAutoConfiguration::class)

Relevant log output

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'storageProvider' defined in class path resource [org/jobrunr/spring/autoconfigure/storage/JobRunrSqlStorageAutoConfiguration.class]: Failed to instantiate [org.jobrunr.storage.StorageProvider]: Factory method 'sqlStorageProvider' threw exception with message: Not all required tables are available by JobRunr!
	at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:648) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:636) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1335) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1165) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:562) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:522) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:326) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:975) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:962) ~[spring-context-6.1.6.jar:6.1.6]
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:624) ~[spring-context-6.1.6.jar:6.1.6]
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:146) ~[spring-boot-3.2.5.jar:3.2.5]
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:754) ~[spring-boot-3.2.5.jar:3.2.5]
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:456) ~[spring-boot-3.2.5.jar:3.2.5]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:334) ~[spring-boot-3.2.5.jar:3.2.5]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1354) ~[spring-boot-3.2.5.jar:3.2.5]
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1343) ~[spring-boot-3.2.5.jar:3.2.5]
	at com.alliander.kv.intake.ApplicationKt.main(Application.kt:25) ~[classes/:na]
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.jobrunr.storage.StorageProvider]: Factory method 'sqlStorageProvider' threw exception with message: Not all required tables are available by JobRunr!
	at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:177) ~[spring-beans-6.1.6.jar:6.1.6]
	at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:644) ~[spring-beans-6.1.6.jar:6.1.6]
	... 19 common frames omitted
Caused by: org.jobrunr.JobRunrException: Not all required tables are available by JobRunr!
	at org.jobrunr.storage.sql.common.DatabaseCreator.validateTables(DatabaseCreator.java:126) ~[jobrunr-7.1.1.jar:7.1.1]
	at org.jobrunr.storage.sql.common.DefaultSqlStorageProvider.setUpStorageProvider(DefaultSqlStorageProvider.java:81) ~[jobrunr-7.1.1.jar:7.1.1]
	at org.jobrunr.storage.sql.common.DefaultSqlStorageProvider.<init>(DefaultSqlStorageProvider.java:66) ~[jobrunr-7.1.1.jar:7.1.1]
	at org.jobrunr.storage.sql.common.DefaultSqlStorageProvider.<init>(DefaultSqlStorageProvider.java:54) ~[jobrunr-7.1.1.jar:7.1.1]
	at org.jobrunr.storage.sql.postgres.PostgresStorageProvider.<init>(PostgresStorageProvider.java:23) ~[jobrunr-7.1.1.jar:7.1.1]
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:na]
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) ~[na:na]
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) ~[na:na]
	at org.jobrunr.storage.sql.common.SqlStorageProviderFactory.getStorageProvider(SqlStorageProviderFactory.java:71) ~[jobrunr-7.1.1.jar:7.1.1]
	at org.jobrunr.storage.sql.common.SqlStorageProviderFactory.getStorageProviderByJdbcUrl(SqlStorageProviderFactory.java:44) ~[jobrunr-7.1.1.jar:7.1.1]
	at org.jobrunr.storage.sql.common.SqlStorageProviderFactory.getStorageProviderUsingDataSource(SqlStorageProviderFactory.java:36) ~[jobrunr-7.1.1.jar:7.1.1]
	at org.jobrunr.storage.sql.common.SqlStorageProviderFactory.using(SqlStorageProviderFactory.java:30) ~[jobrunr-7.1.1.jar:7.1.1]
	at org.jobrunr.spring.autoconfigure.storage.JobRunrSqlStorageAutoConfiguration.sqlStorageProvider(JobRunrSqlStorageAutoConfiguration.java:33) ~[jobrunr-spring-boot-3-starter-7.1.1.jar:7.1.1]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:140) ~[spring-beans-6.1.6.jar:6.1.6]
	... 20 common frames omitted

[auloin]

Hi @ibrok, thanks for reporting, we'll have a look and fix it asap.

[rdehuyss]

Could you please update your GitHub profile (like requested when you create an issue) and create a GitHub repo that reproduces this?

[ibrok]

I updated my profile, I missed that part, it helps if the instructions are a little shorter and to the point.

[edit]reproduced it here as a fork of your example project: https://github.com/ibrok/example-order-fulfillment-bug

clone project, run 'docker compose up' from project directory to start a postgres docker container
run the application or the test (so that the tables are created, to mimic them being created by flyway for example)

then go to application.properties and uncomment line 7: org.jobrunr.database.skip-create=true
then run the application or test again, and you will see the error.

Then go to application.properties again for the workaround and uncomment line 8: org.jobrunr.database.table-prefix=public.
and run the application again, error is no longer there.

I think the validateTables shouldn't take the schema into account, or should default to the datasource schema if no prefix is set.

Issue was introduced in version 7.1.1, if you set the jobrunr version to 7.1.0 there is no issue.

[rdehuyss]

Thank you! We'll take a look!