Batch does not complain if job repository tables are missing

[maddenj.ie]

Hi everyone,

I have set up a jobRepository using the following bean as outlined in the documentation.

<job-repository id="jobRepository"
    data-source="dataSource"
    transaction-manager="transactionManager"
    isolation-level-for-create="SERIALIZABLE"
    table-prefix="BATCH_"
	max-varchar-length="1000"
/>

Spring Batch Version : 2.1.9.RELEASE
Database MySql : 5.5.24
java version : 1.6.0_33

If the tables are not present in the database, there doesn't appear to be an error message. When a job is run, there are no messages to indicate a problem. Is this functionality not present or am i missing some configuration?

Thanks for the help.

[dr_pompeii]

Hello

If the tables are not present in the database, there doesn't appear to be an error message.

You can create such tables in-memory (HSQLDB or H2 ) or not.

If is not in-memory like Mysql and these tables are not created, the framework must thrown an exception.

Spring Batch come with many sql scripts for many db providers, they are located in the spring-batch-core-2.1.9.RELEASE.jar file. Specifically within the package org.springframework.batch.core

Therefore Spring Batch for create and drop its own db structure for the management of the repository for Mysql, it uses the files

• schema-mysql.sql
• schema-drop-mysql.sql

For example for my DB mini project, these own SB's tables are created within my own db

mysql> show tables;
+------------------------------+
| Tables_in_manolobank         |
+------------------------------+
| batch_job_execution          |
| batch_job_execution_context  |
| batch_job_execution_seq      |
| batch_job_instance           |
| batch_job_params             |
| batch_job_seq                |
| batch_step_execution         |
| batch_step_execution_context |
| batch_step_execution_seq     |
| c3p0_test_table              |
| cliente                      |
| clientecuentacabecera        |
| moneda                       |
| tasacastigointeres           |
+------------------------------+
14 rows in set (0.00 sec)
mysql>

HTH

[dr_pompeii]

Hello

I forgot to say, I must extract only the schema-mysql.sql script file and execute the follow

mysql> source D:/schema-mysql.sql;

With that I got the SB's own tables shown in my previous post.

HTH

[maddenj.ie]

Hi,

Thanks for the reply. I've successfully set up my application to use spring batch and use a mysql database as job repository. I can execute a job and the database is updated appropriately.

The issue I have is that if the database has not been set up correctly with the batch tables, there is no indication of a problem when I attempt to run a job. Is there a way of detecting this issue? I'd like to terminate my application with an appropriate error message if this scenario occurs.

Any help appreciated.
Joe

[mminella]

Batch does fail when running against a database if the tables cannot be found:

2012-10-26 13:18:16,975 ERROR main [org.springframework.batch.core.launch.support.CommandLineJobRunner] - <Job Terminated in error: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? order by JOB_INSTANCE_ID desc]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'spring_batch.batch_job_instance' doesn't exist>
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT JOB_INSTANCE_ID, JOB_NAME from BATCH_JOB_INSTANCE where JOB_NAME = ? order by JOB_INSTANCE_ID desc]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'spring_batch.batch_job_instance' doesn't exist
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:637)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:666)
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:674)
	at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.getJobInstances(JdbcJobInstanceDao.java:330)
	at org.springframework.batch.core.explore.support.SimpleJobExplorer.getJobInstances(SimpleJobExplorer.java:163)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.getNextJobParameters(CommandLineJobRunner.java:464)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.start(CommandLineJobRunner.java:343)
	at org.springframework.batch.core.launch.support.CommandLineJobRunner.main(CommandLineJobRunner.java:574)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'spring_batch.batch_job_instance' doesn't exist
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

Did you have the dataSourceInitializer configured? If so, that will create the tables on startup.

[maddenj.ie]

Thanks for the definitive information. I've been able to confirm your feedback in my own app. My app also uses spring-integration and I believe I'm somehow losing the exception in the flow. Many thanks again,

Joe