search

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.09k stars 579 forks source link

BadSqlGrammarException for Spring Boot 3 Task Applications #5848

Open klopfdreh opened 5 days ago

klopfdreh commented 5 days ago

Description: As we migrate to Spring Boot 3 / Spring Batch 5 / Spring Cloud Task 3 applications we are currently facing a bug that the started task application seems not to choose the right table prefix even if the environment variables for the kubernetes Pod seem to be set correctly.

We saw in the environment variables of the kubernetes pod: SPRING_BATCH_JDBC_TABLE-PREFIX=BOOT3BATCH SPRING_CLOUD_TASK_TABLEPREFIX=BOOT3TASK

but we are still receiving an exception relating to the database schema.

Release versions:

{
  "versions": {
    "implementation": {
      "name": "spring-cloud-data-flow-server",
      "version": "2.11.2"
    },
    "core": {
      "name": "Spring Cloud Data Flow Core",
      "version": "2.11.2"
    },
    "dashboard": {
      "name": "Spring Cloud Dataflow UI",
      "version": "3.4.2"
    },
    "shell": {
      "name": "Spring Cloud Data Flow Shell",
      "version": "2.11.2",
      "url": "https://repo.maven.apache.org/maven2/org/springframework/cloud/spring-cloud-dataflow-shell/2.11.2/spring-cloud-dataflow-shell-2.11.2.jar"
    }
  },
  "features": {
    "streams": true,
    "tasks": true,
    "schedules": true,
    "monitoringDashboardType": "NONE"
  },
  "runtimeEnvironment": {
    "appDeployer": {
      "deployerImplementationVersion": "2.11.2",
      "deployerName": "Spring Cloud Skipper Server",
      "deployerSpiVersion": "2.11.2",
      "javaVersion": "17.0.10",
      "platformApiVersion": "",
      "platformClientVersion": "",
      "platformHostVersion": "",
      "platformSpecificInfo": {},
      "platformType": "Skipper Managed",
      "springBootVersion": "2.7.18",
      "springVersion": "5.3.31"
    },
    "taskLaunchers": [
      {
        "deployerImplementationVersion": "unknown",
        "deployerName": "KubernetesTaskLauncher",
        "deployerSpiVersion": "unknown",
        "javaVersion": "17.0.10",
        "platformApiVersion": "v1",
        "platformClientVersion": "unknown",
        "platformHostVersion": "unknown",
        "platformSpecificInfo": {
          "namespace": "xxxx",
          "master-url": "https://10.96.0.1:443/"
        },
        "platformType": "Kubernetes",
        "springBootVersion": "2.7.18",
        "springVersion": "5.3.31"
      }
    ]
  },
  "monitoringDashboardInfo": {
    "url": "",
    "source": "default-scdf-source",
    "refreshInterval": 15
  },
  "security": {
    "isAuthentication": true,
    "isAuthenticated": true,
    "username": "xxxx",
    "roles": [
      "ROLE_CREATE",
      "ROLE_DEPLOY",
      "ROLE_DESTROY",
      "ROLE_MANAGE",
      "ROLE_MODIFY",
      "ROLE_SCHEDULE",
      "ROLE_VIEW"
    ]
  },
  "git": {
    "commit": "9765ab7"
  }
}

Custom apps: N/A

Steps to reproduce: N/A

Screenshots: image image image

Additional context: The Composed Task Runner is still Spring Boot 2.

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_EXECUTION_ID, PARAMETER_NAME, PARAMETER_TYPE, PARAMETER_VALUE, IDENTIFYING
FROM BATCH_JOB_EXECUTION_PARAMS
WHERE JOB_EXECUTION_ID = ?
]
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:103)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1548)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:754)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:783)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:799)
at org.springframework.batch.core.repository.dao.JdbcJobExecutionDao.getJobParameters(JdbcJobExecutionDao.java:484)
at org.springframework.batch.core.repository.dao.JdbcJobExecutionDao$JobExecutionRowMapper.mapRow(JdbcJobExecutionDao.java:510)
at org.springframework.batch.core.repository.dao.JdbcJobExecutionDao$JobExecutionRowMapper.mapRow(JdbcJobExecutionDao.java:495)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94)
at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:733)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:723)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:754)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:767)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:825)
at org.springframework.batch.core.repository.dao.JdbcJobExecutionDao.getLastJobExecution(JdbcJobExecutionDao.java:342)
at org.springframework.batch.core.explore.support.SimpleJobExplorer.getLastJobExecution(SimpleJobExplorer.java:109)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:354)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:392)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
at jdk.proxy3/jdk.proxy3.$Proxy186.getLastJobExecution(Unknown Source)
at org.springframework.batch.core.JobParametersBuilder.getNextJobParameters(JobParametersBuilder.java:362)
at org.springframework.cloud.task.batch.handler.TaskJobLauncherApplicationRunner.execute(TaskJobLauncherApplicationRunner.java:143)
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.executeLocalJobs(JobLauncherApplicationRunner.java:194)
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.launchJobFromProperties(JobLauncherApplicationRunner.java:174)
at org.springframework.cloud.task.batch.handler.TaskJobLauncherApplicationRunner.run(TaskJobLauncherApplicationRunner.java:112)
at org.springframework.boot.autoconfigure.batch.JobLauncherApplicationRunner.run(JobLauncherApplicationRunner.java:164)
at org.springframework.cloud.task.configuration.observation.ObservationApplicationRunner.run(ObservationApplicationRunner.java:59)
at org.springframework.boot.SpringApplication.lambda$callRunner$4(SpringApplication.java:786)
at org.springframework.util.function.ThrowingConsumer$1.acceptWithException(ThrowingConsumer.java:83)
at org.springframework.util.function.ThrowingConsumer.accept(ThrowingConsumer.java:60)
at org.springframework.util.function.ThrowingConsumer$1.accept(ThrowingConsumer.java:88)
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:798)
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:786)
at org.springframework.boot.SpringApplication.lambda$callRunners$3(SpringApplication.java:774)
at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
at java.base/java.util.stream.SortedOps$SizedRefSortingSink.end(SortedOps.java:357)
at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:510)
at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
at java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
at java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:774)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:342)
at org.springframework.boot.builder.SpringApplicationBuilder.run(SpringApplicationBuilder.java:149)
at de.xxx.batch.basic.tasks.s3.uploader.S3UploaderApplication.main(S3UploaderApplication.java:28)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at org.springframework.boot.loader.launch.Launcher.launch(Launcher.java:91)
at org.springframework.boot.loader.launch.Launcher.launch(Launcher.java:53)
at org.springframework.boot.loader.launch.JarLauncher.main(JarLauncher.java:58)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: \"PARAMETER_VALUE\": ungültige ID

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1150)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:770)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:497)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:151)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:936)
at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1171)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1100)
at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1425)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1308)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3745)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3854)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1097)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at com.atomikos.util.DynamicProxySupport.callNativeMethod(DynamicProxySupport.java:180)
at com.atomikos.util.DynamicProxySupport.invoke(DynamicProxySupport.java:123)
at jdk.proxy3/jdk.proxy3.$Proxy194.executeQuery(Unknown Source)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:732)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
... 63 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00904: \"PARAMETER_VALUE\": ungültige ID

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636)
... 86 common frames omitted

ORA-00904: \"PARAMETER_VALUE\": ungültige ID = ORA-00904: \"PARAMETER_VALUE\": invalid ID

As you can see BATCH_JOB_EXECUTION_PARAMS is used and not BOOT3_BATCH_JOB_EXECUTION_PARAMS

Even if we set the table prefix in the application.yml it seems they are not used:

spring:
  batch:
    jdbc:
      table-prefix: BOOT3_BATCH_
  cloud:
    task:
      tablePrefix: BOOT3_TASK_

The dependencies in our task application are also set correctly: image image

As of the documentation SCDF should use the right prefix based on the registration of the application - https://docs.spring.io/spring-cloud-dataflow/docs/current/reference/htmlsingle/#_spring_cloud_task_batch_applications

klopfdreh commented 5 days ago

I found the issue! We are defining the JobRepository and the JobExplorer like mentioned here: https://github.com/spring-projects/spring-batch/issues/3942 - we migrated to spring batch 5, but with SCDF 2.11.x you have to be careful, because you also need to apply the table prefix to both repositories.

Example:

        JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
        factory.setSerializer(batchJackson2ExecutionContextStringSerializer);
        factory.setDataSource(dataSource);
        factory.setTransactionManager(transactionManager);
        factory.setIsolationLevelForCreateEnum(Isolation.DEFAULT);
        String tablePrefix = batchProperties.getJdbc().getTablePrefix();
        if (tablePrefix != null) {
            factory.setTablePrefix(tablePrefix);
        }
        factory.afterPropertiesSet();
        return Objects.requireNonNull(factory.getObject());

Could you add this as a hint to the documentation that you need to be careful while defining those repositories?