spring-cloud / spring-cloud-dataflow

A microservices-based Streaming and Batch data processing in Cloud Foundry and Kubernetes
https://dataflow.spring.io
Apache License 2.0
1.1k stars 578 forks source link

Database schema of Data Flow Server cannot be changed #4700

Open uunilohi opened 2 years ago

uunilohi commented 2 years ago

We tried to change the database schema of Data Flow Server (2.8.2). With the configuration below, the tables are created to a database schema called "scdf_dataflow" as expected.

spring:
  batch:
    table-prefix: scdf_dataflow.batch_
  cloud
    task:
      table-prefix: scdf_dataflow.task_
  flyway:
    default-schema: scdf_dataflow
    schemas: scdf_dataflow
  jpa:
    properties:
      hibernate:
        default_schema: scdf_dataflow

However when navigating to "Job executions" in Dashboard, an exception is thrown:

2021-09-27 08:25:20.367 ERROR 1 --- [nio-8080-exec-9] o.s.c.d.s.c.RestControllerAdvice         : Caught exception while handling a request
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT E.JOB_EXECUTION_ID, E.START_TIME, E.END_TIME, E.STATUS, E.EXIT_CODE, E.EXIT_MESSAGE, E.CREATE_TIME, E.LAST_UPDATED, E.VERSION, I.JOB_INSTANCE_ID, I.JOB_NAME, (SELECT COUNT(*) FROM BATCH_STEP_EXECUTION S WHERE S.JOB_EXECUTION_ID = E.JOB_EXECUTION_ID) as STEP_COUNT FROM BATCH_JOB_EXECUTION E, BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID ORDER BY JOB_EXECUTION_ID DESC LIMIT 20]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "batch_job_execution" does not exist
  Position: 277
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1541)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:465)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:475)
    at org.springframework.cloud.dataflow.server.batch.JdbcSearchableJobExecutionDao.getJobExecutionsWithStepCount(JdbcSearchableJobExecutionDao.java:401)
    at org.springframework.cloud.dataflow.server.batch.SimpleJobService.listJobExecutionsWithStepCount(SimpleJobService.java:204)
    at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService.listJobExecutionsWithStepCount(DefaultTaskJobService.java:114)
    at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService$$FastClassBySpringCGLIB$$d486827e.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:779)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:692)
    at org.springframework.cloud.dataflow.server.service.impl.DefaultTaskJobService$$EnhancerBySpringCGLIB$$22f2d224.listJobExecutionsWithStepCount(<generated>)
    at org.springframework.cloud.dataflow.server.controller.JobExecutionThinController.listJobsOnly(JobExecutionThinController.java:93)

The reason for this seem to be that a hard-coded table prefix is set in class org.springframework.cloud.dataflow.server.configDataFlowServerConfiguration:

@Bean
DataflowJobExecutionDao dataflowJobExecutionDao(DataSource dataSource) {
    return new JdbcDataflowJobExecutionDao(dataSource, AbstractJdbcBatchMetadataDao.DEFAULT_TABLE_PREFIX);
}

Are there any plans for supporting a custom database schema (e.g. supporting spring.batch.table-prefix property)?

aritzbastida commented 2 years ago

We have the same problem commented by the OP: the table prefix for Spring Cloud Data Flow / Spring Batch is hardcoded to its default value and cannot be configured.

I just uploaded a PR (#4800) which solves the issue for Spring Batch tables. However, I just realized that all SCDF-specific tables are still hardcoded, and have no "%PREFIX%" in their FROM clause... :/