spring-cloud-dataflow icon indicating copy to clipboard operation
spring-cloud-dataflow copied to clipboard

Published 20 hours ago • verified publisher icon spring-cloud

BadSqlGrammarException for Spring Boot 3 Task Applications

Open klopfdreh opened this issue 1 year ago • 1 comments
trafficstars

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=BOOT3_BATCH_ SPRING_CLOUD_TASK_TABLEPREFIX=BOOT3_TASK_

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 avatar Jun 26 '24 06:06 klopfdreh

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?

klopfdreh avatar Jun 26 '24 08:06 klopfdreh

@klopfdreh Thank you for reporting this!

cppwfs avatar Jul 12 '24 15:07 cppwfs

Thanks to you that you added the documentation! 👍

We also saw that this is also required if you have two datasources and define your own https://github.com/spring-cloud/spring-cloud-task/blob/main/spring-cloud-task-core/src/main/java/org/springframework/cloud/task/configuration/DefaultTaskConfigurer.java - but I guess this is not worth a documentation entry, isn’t it?

klopfdreh avatar Jul 12 '24 16:07 klopfdreh