Announcement Announcement Module
Collapse
No announcement yet.
2.1.9 to 2.2.0 database migration Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 2.1.9 to 2.2.0 database migration

    Hi All,

    I noticed this issue while migrating a batch application from 2.1.9 to 2.2.0

    If you run the migration script provided in release 2.2.0, then attempt to edit the data in or drop the table SPCM_BATCH_JOB_INSTANCE, a foreign key constraint error occurs.

    The mysql migration script provided looks like this:

    Code:
    -- create the requisite table
    
    CREATE TABLE BATCH_JOB_EXECUTION_PARAMS  (
    	JOB_EXECUTION_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 ,
    	IDENTIFYING CHAR(1) NOT NULL ,
    	constraint JOB_EXEC_PARAMS_FK foreign key (JOB_EXECUTION_ID)
    	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
    ) ENGINE=InnoDB;
    
    -- insert script that 'copies' existing batch_job_params to batch_job_execution_params
    -- sets new params to identifying ones
    -- verified on h2, 
    
    INSERT INTO BATCH_JOB_EXECUTION_PARAMS 
    	( JOB_EXECUTION_ID , TYPE_CD, KEY_NAME, STRING_VAL, DATE_VAL, LONG_VAL, DOUBLE_VAL, IDENTIFYING )
    SELECT 
    	JE.JOB_EXECUTION_ID , JP.TYPE_CD , JP.KEY_NAME , JP.STRING_VAL , JP.DATE_VAL , JP.LONG_VAL , JP.DOUBLE_VAL , 1 
    FROM 
    	BATCH_JOB_PARAMS JP,BATCH_JOB_EXECUTION JE
    WHERE 
    	JP.JOB_INSTANCE_ID = JE.JOB_INSTANCE_ID;
    The BATCH_JOB_PARAMS does not exist in the new schema but the migration script does not drop it. Therefore any data in it may still reference data in the SPCM_BATCH_JOB_INSTANCE table and hence the error.

    Shouldn't the migration script drop the table in this case?

    Regards,

    Joe
Working...
X