Problem Storing Spring-Batch Job Parameters

We have been using Spring-Batch at inPowered for a while for relatively simple jobs with only a date parameter; MySQL is the backing store for meta data. When we added a job with a parameter of type string the job launcher had problems even getting the job started and the Spring-Batch UI displayed this error:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_PARAMS(JOB_INSTANCE_ID, KEY_NAME, TYPE_CD, STRING_VAL, DATE_VAL, LONG_VAL, DOUBLE_VAL) values (?, ?, ?, ?, ?, ?, ?)]; Data truncation: Incorrect datetime value: '1970-01-01 00:00:00' for column 'DATE_VAL' at row 1; nested exception is com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '1970-01-01 00:00:00' for column 'DATE_VAL' at row 1


Turns out that this happens when Spring-Batch tried to store the string parameter. For some reason it tries to store '1970-01-01 00:00:00' in the DATE_VAL field, probably to indicate that there is no value.

Checking the table definition for the respective table

CREATE TABLE BATCH_JOB_PARAMS  (
  JOB_INSTANCE_ID BIGINT NOT NULL ,
  TYPE_CD VARCHAR(6) NOT NULL ,
  KEY_NAME VARCHAR(100) NOT NULL , 
  STRING_VAL VARCHAR(250) , 
  DATE_VAL TIMESTAMP ,
  LONG_VAL BIGINT ,
  DOUBLE_VAL DOUBLE PRECISION ,
  constraint JOB_INST_PARAMS_FK foreign key (JOB_INSTANCE_ID)
  references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ENGINE=InnoDB;

revealed that the data type for the DATE_VAL field is TIMESTAMP and '1970-01-01 00:00:00' is outside the valid range. The smallest valid value for a TIMESTAMP in MySQL is actually '1970-01-01 00:00:01'. Simply modifying the table resolved that issue:

alter table BATCH_JOB_PARAMS modify DATE_VAL datetime;

The above table definition was the suggested one at the time we started using Spring-Batch. The release we're currently using (2.1.9.RELEASE) already had the SQL that ships with the core jar adjusted and the new table looks like this (of course we weren't checking those files after the system was set up...):

CREATE TABLE BATCH_JOB_PARAMS  (
  JOB_INSTANCE_ID BIGINT NOT NULL ,
  TYPE_CD VARCHAR(6) NOT NULL ,
  KEY_NAME VARCHAR(100) NOT NULL , 
  STRING_VAL VARCHAR(250) , 
  DATE_VAL DATETIME DEFAULT NULL ,
  LONG_VAL BIGINT ,
  DOUBLE_VAL DOUBLE PRECISION ,
  constraint JOB_INST_PARAMS_FK foreign key (JOB_INSTANCE_ID)
  references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ENGINE=InnoDB;

While this works fine it's unclear to me why Spring-Batch makes up a NULL-equivalent-value instead of just setting DATE_VAL to NULL for none-date parameters. In my experience making up a value that represents NULL almost always comes back to haunt you and it's usually better to simply store NULL since that is what it's for.

Learnings:

• When you encounter the exception above, check your table definition of BATCH_JOB_PARAMS.
•  Don't make up values to represent NULL unless you really have to (which you usually don't), just store NULL.