Problems with mysql schema definition

[ndw]

I've encountered a couple of problems with the spring-batch-schema-mysql.sql definition:

• Looks like TIMESTAMP in mysql will cannot be set to null by default (see http://dev.mysql.com/doc/refman/5.1/en/timestamp.html).
  So without the NULL qualifier M5 definitions of JOB_EXECUTION and STEP_EXECUTION END_TIME will always be populated.
  
  One consequence of this is that Job will always have an END_TIME and JobExecution.isRunning() will always return false.

• Secondly the foreign key constraints fail due to type differences on ID columns (BIGINT unsigned vs BIGINT).

So Definition 1. below fails with:

SQL state [HY000]; error code [1005]; Can't create table '.\dml\batch_job_execution.frm' (errno: 150); nested exception is java.sql.SQLException: Can't create table '.\dml\batch_job_execution.frm' (errno: 150)

Defintion 2. appears to work and supports the NULL END_TIME requirement

• Definition 1.

CREATE TABLE BATCH_JOB_EXECUTION  (
	JOB_EXECUTION_ID BIGINT unsigned PRIMARY KEY ,
	VERSION BIGINT,  
	JOB_INSTANCE_ID BIGINT NOT NULL,
	START_TIME TIMESTAMP, 
	END_TIME TIMESTAMP ,
	STATUS VARCHAR(10),
	CONTINUABLE CHAR(1),
	EXIT_CODE VARCHAR(20),
	EXIT_MESSAGE VARCHAR(2500),
	constraint JOB_INSTANCE_EXECUTION_FK foreign key(JOB_INSTANCE_ID)
	references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
);

• Definition 2.

CREATE TABLE BATCH_JOB_EXECUTION  (
	JOB_EXECUTION_ID BIGINT unsigned PRIMARY KEY ,
	VERSION BIGINT,  
	JOB_INSTANCE_ID BIGINT unsigned NOT NULL, // added unsigned
	START_TIME TIMESTAMP, 
	END_TIME TIMESTAMP NULL, // added NULL
	STATUS VARCHAR(10),
	CONTINUABLE CHAR(1),
	EXIT_CODE VARCHAR(20),
	EXIT_MESSAGE VARCHAR(2500),
	constraint JOB_INSTANCE_EXECUTION_FK foreign key (JOB_INSTANCE_ID)
	references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
);

[lucasward]

Not sure if you already posted this or not, but it's in jira:

http://jira.springframework.org/browse/BATCH-452

[ndw]

Hadn't seen http://jira.springframework.org/browse/BATCH-452, but it doesn't address the TIMESTAMP NULL problem. Could/should that be rolled into BATCH-452?

[lucasward]

I could roll it into 452, can you add a comment to the issue? It looks like you've updated the schema to solve both issues, do you mind attaching it to the issue as well?