PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY =?

Question

I want the Spring Batch metadata to be created on the MySQL server and used all the existing tables from Oracle to fetch data from it and put it into the MongoDB.

I created the following configurations, but somehow missing the trick to create the Spring Batch metadata tables though configuration.

spring.data.mongodb.host=localhost
spring.data.mongodb.port=27017
spring.data.mongodb.database=MY_DB

#By default, Spring runs all the job as soon as it has started its context.
spring.batch.job.enabled=false

spring.batch.initialize-schema=always
spring.batch.tablePrefix=test.BATCH_
#spring.batch.initializer.enabled=false
spring.batch.schema=org/springframework/batch/core/schema-mysql.sql

spring.datasource.url=jdbc:oracle:thin:@localhost:1527:OR_DEV
spring.datasource.username=EDR_USR
spring.datasource.password=txz$2Zhr
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

jdbc.batch.jdbcUrl=jdbc:mysql://localhost:3306/test?useSSL=false
jdbc.batch.username=root
jdbc.batch.password=root
jdbc.batch.driver-class-name=com.mysql.cj.jdbc.Driver

DBConfig.java

@Configuration
@ComponentScan
public class DBConfig {

    @Autowired
    private Environment env;

    @Bean(name="oracleDS")
    public DataSource batchDataSource(){          
           return DataSourceBuilder.create()
                    .url(env.getProperty("spring.datasource.url"))
                    .driverClassName(env.getProperty("spring.datasource.driver-class-name"))
                    .username(env.getProperty("spring.datasource.username"))
                    .password(env.getProperty("spring.datasource.password"))
                    .build();          
    } 

    @Bean(name="mysqlDS")
    @Primary
    public DataSource mysqlBatchDataSource(){          
           return DataSourceBuilder.create()
                    .url(env.getProperty("jdbc.batch.jdbcUrl"))
                    .driverClassName(env.getProperty("jdbc.batch.driver-class-name"))
                    .username(env.getProperty("jdbc.batch.username"))
                    .password(env.getProperty("jdbc.batch.password"))
                    .build();          
    }
}

Job

@GetMapping("/save-student")
    public String saveStudent() {
        JobParameters params = new JobParametersBuilder()
                .addString("JobID", String.valueOf(System.currentTimeMillis()))
                .addString("Job_ID", String.valueOf(System.currentTimeMillis()))
                .addDate("date", new Date())
                .toJobParameters();
        try {
            JobExecution jobExecution = jobLauncher.run(countryJob, params);
            log.debug("Job Status : " + jobExecution.getStatus());
        } catch (JobExecutionAlreadyRunningException | JobRestartException | JobInstanceAlreadyCompleteException
                | JobParametersInvalidException e) {
            log.error("Job Failed : "+e.getMessage());
        }
        return "";
    }

Error:

{
    "timestamp": "2019-03-27T14:57:52.745+0000",
    "status": 500,
    "error": "Internal Server Error",
    "message": "PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? and JOB_KEY = ?]; nested exception is java.sql.SQLSyntaxErrorException: Table 'test.batch_job_instance' doesn't exist",
    "path": "/save-student"
}

Somnifacient

Please share your spring batch configuration as well. Do you use a custom batch configurer? I see you are manually creating data sources, do you use Spring Boot? – Procopius

Yes I am using Spring Boot... but I am not using custom batch configurer. Could you please guide ? – Somnifacient

I can't see from what you shared what's wrong. The datasource you annotated with @Primary should be the one used for Spring Batch and initialized by Spring Boot (according to spring.batch.initialize-schema=always) with batch meta-data. – Procopius

Since we've two dataSources, Spring Batch is unable to create the metadata tables automatically and hence this error is coming. I executed the script manually as a workaround, but is there any way to fixed this issue through code? – Somnifacient

Answer 1

To fixed this issue, I executed the MYSQL spring batch metadata tables manually by executing the script from here: MySQL Spring Batch Metadata tables script

Since we've two dataSources, Spring Batch is unable to create the metadata tables automatically and hence this error is coming. I executed the script manually as a workaround, but is there any way to fixed this issue through code?

Somnifacient

Answer 2

Write below code in application.properites file. It will automatically generate tables required for the spring batch.

spring.batch.initialize-schema=always

Keavy

Its now spring.batch.jdbc.initialize-schema=always – Meaghanmeagher

Answer 3

spring.batch.jdbc.initialize-schema=always

Ventricose

Answer 4

I was getting this error when I migrated to Spring boot 3 and jdk 17.

Apart from adding spring.batch.jdbc.initialize-schema=always in application file.

You should remove @EnableBatchProcessing from your spring batch config. As it is no longer required in spring batch 5.

https://github.com/spring-projects/spring-batch/issues/4336

Damaging

After upgrading to java 21 and Spring Boot 3.2.1, this solution worked for me. – Tuber

Answer 5

After spend some time, I could achieve the results i hoped by setting the batch datasource as @Primary. Like this:

Datasource configuration

@Bean
@Primary
public DataSource batchDataSource() {
    return batchDataSourceProperties().initializeDataSourceBuilder().build();
}

@Bean
@ConfigurationProperties("spring.datasource.batch")
public DataSourceProperties batchDataSourceProperties() {
    return new DataSourceProperties();
}

OR, In the Batch configuration i set the data source

@Component
public class BatchDSConfiguration extends DefaultBatchConfigurer {
@Override
@Autowired(required = false)
public void setDataSource(@Qualifier("batchDataSource") DataSource dataSource) {
    super.setDataSource(dataSource);        
}
...

}

Application properties

spring.datasource.batch.driverClassName=org.h2.Driver
spring.datasource.batch.url=jdbc:h2:mem:batchdb
spring.datasource.batch.username=sb
spring.datasource.batch.password=
spring.batch.jdbc.initialize-schema=ALWAYS

I Tried to create a custom JobRepository in the BatchConfigurer, but could not create the batch schema with multiple data sources. I Will try more and if i can do it i'll come back here to tell.

Hope it helps.

Mothy

Answer 6

If you use OracleDB first you should delete all the Batch related tables in oracleDB which is automatically generated..

Phoenicia