Announcement Announcement Module
Collapse
No announcement yet.
Problems with mysql schema definition Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problems with mysql schema definition

    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.
    Code:
    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.
    Code:
    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)
    );
    Last edited by ndw; Mar 13th, 2008, 01:08 PM. Reason: Incorrect statement of JobExecution.isRunning() behaviour

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

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

    Comment


    • #3
      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?

      Comment


      • #4
        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?

        Comment

        Working...
        X