Camunda Upgrade Issues with database

hedza06 · February 22, 2022, 5:04pm

Hello there,
I’m upgrading Camunda from 2.3.0 to 3.2.0. Precisely, I’m upgrading from 7.8.0 to 7.10.0. I’m getting an error: “Unknown column ‘TYPE_’ in ‘field list’”.

Does anyone has a database migration scripts and is it possible to manually migrate database structure (schema) to new version?

Thank you in advance.

Niall · February 23, 2022, 7:48am

You’ll find the details on upgrading are here:
https://docs.camunda.org/manual/7.16/update/minor/

hedza06 · February 25, 2022, 8:26am

I did it the hard way:

Generate new version of database (3.2.0)
Compare every table with old version (2.2.0 vs. 3.2.0)

And here is the result:

ALTER TABLE `ACT_GE_BYTEARRAY` 
ADD COLUMN `TYPE_` INT(11) NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `CREATE_TIME_` DATETIME NULL DEFAULT NULL AFTER `TYPE_`,
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `CREATE_TIME_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_ACTINST` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_ATTACHMENT` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`,
ADD COLUMN `CREATE_TIME_` DATETIME NULL DEFAULT NULL AFTER `REMOVAL_TIME_`;


ALTER TABLE `ACT_HI_BATCH` 
ADD COLUMN `CREATE_USER_ID_` VARCHAR(255) NULL DEFAULT NULL AFTER `END_TIME_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `CREATE_USER_ID_`;


ALTER TABLE `ACT_HI_COMMENT` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_DEC_IN` 
ADD COLUMN `CREATE_TIME_` DATETIME NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `CREATE_TIME_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_DECINST` 
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `REMOVAL_TIME_`;


ALTER TABLE `ACT_HI_DEC_OUT` 
ADD COLUMN `CREATE_TIME_` DATETIME NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `CREATE_TIME_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_DETAIL` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `OPERATION_ID_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_EXT_TASK_LOG` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `REV_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_IDENTITYLINK` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_INCIDENT` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `JOB_DEF_ID_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_JOB_LOG` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_OP_LOG` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_PROCINST` 
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `STATE_`,
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `REMOVAL_TIME_`;


ALTER TABLE `ACT_HI_TASKINST` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `TENANT_ID_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`;


ALTER TABLE `ACT_HI_VARINST` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `STATE_`,
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `ROOT_PROC_INST_ID_`,
ADD COLUMN `CREATE_TIME_` DATETIME NULL DEFAULT NULL AFTER `REMOVAL_TIME_`;


ALTER TABLE `ACT_ID_USER` 
ADD COLUMN `LOCK_EXP_TIME_` TIMESTAMP NULL DEFAULT NULL AFTER `PICTURE_ID_`,
ADD COLUMN `ATTEMPTS_` INT(11) NULL DEFAULT NULL AFTER `LOCK_EXP_TIME_`;


ALTER TABLE `ACT_RE_PROCDEF` 
ADD COLUMN `STARTABLE_` TINYINT(1) NOT NULL DEFAULT 1 AFTER `HISTORY_TTL_`;


ALTER TABLE `ACT_RU_BATCH` 
ADD COLUMN `CREATE_USER_ID_` VARCHAR(255) NULL DEFAULT NULL AFTER `TENANT_ID_`;


ALTER TABLE `ACT_RU_EXECUTION` 
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `TENANT_ID_`;


ALTER TABLE `ACT_RU_JOB` 
ADD COLUMN `CREATE_TIME_` DATETIME NULL DEFAULT NULL AFTER `TENANT_ID_`;

Maybe some new indexes missing, but with this everything works fine.

hedza06 · May 5, 2022, 11:53am

One more hint if you need to migrate DB from 3.2.0 to 7.17.0:

-- NEW TABLES
CREATE TABLE `ACT_GE_SCHEMA_LOG` (
  `ID_` VARCHAR(64) COLLATE utf8_bin NOT NULL,
  `TIMESTAMP_` datetime DEFAULT NULL,
  `VERSION_` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `ACT_RE_CAMFORMDEF` (
  `ID_` varchar(64) COLLATE utf8_bin NOT NULL,
  `REV_` int(11) DEFAULT NULL,
  `KEY_` varchar(255) COLLATE utf8_bin NOT NULL,
  `VERSION_` int(11) NOT NULL,
  `DEPLOYMENT_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `RESOURCE_NAME_` varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  `TENANT_ID_` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE `ACT_RU_TASK_METER_LOG` (
  `ID_` varchar(64) COLLATE utf8_bin NOT NULL,
  `ASSIGNEE_HASH_` bigint(20) DEFAULT NULL,
  `TIMESTAMP_` datetime DEFAULT NULL,
  PRIMARY KEY (`ID_`),
  KEY `ACT_IDX_TASK_METER_LOG_TIME` (`TIMESTAMP_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- History Tables
ALTER TABLE `ACT_HI_DETAIL`
ADD COLUMN `INITIAL_` TINYINT(1) NULL DEFAULT NULL AFTER `REMOVAL_TIME_`;

ALTER TABLE `ACT_HI_INCIDENT`
ADD COLUMN `FAILED_ACTIVITY_ID_` VARCHAR(255) NULL DEFAULT NULL AFTER `REMOVAL_TIME_`,
ADD COLUMN `HISTORY_CONFIGURATION_` VARCHAR(255) NULL DEFAULT NULL AFTER `FAILED_ACTIVITY_ID_`,
ADD COLUMN `ANNOTATION_` VARCHAR(4000) NULL DEFAULT NULL AFTER `HISTORY_CONFIGURATION_`,
ADD INDEX `ACT_IDX_HI_INCIDENT_ROOT_PI` (`ROOT_PROC_INST_ID_` ASC),
ADD INDEX `ACT_IDX_HI_INCIDENT_RM_TIME` (`REMOVAL_TIME_` ASC),
ADD INDEX `ACT_IDX_HI_INCIDENT_CREATE_TIME` (`CREATE_TIME_` ASC),
ADD INDEX `ACT_IDX_HI_INCIDENT_END_TIME` (`END_TIME_` ASC);

ALTER TABLE `ACT_HI_JOB_LOG`
ADD COLUMN `FAILED_ACT_ID_` VARCHAR(255) NULL DEFAULT NULL AFTER `REMOVAL_TIME_`,
ADD COLUMN `HOSTNAME_` VARCHAR(255) NULL DEFAULT NULL AFTER `FAILED_ACT_ID_`,
ADD INDEX `ACT_IDX_HI_JOB_LOG_ROOT_PI` (`ROOT_PROC_INST_ID_` ASC),
ADD INDEX `ACT_IDX_HI_JOB_LOG_RM_TIME` (`REMOVAL_TIME_` ASC),
ADD INDEX `ACT_IDX_HI_JOB_LOG_JOB_CONF` (`JOB_DEF_CONFIGURATION_` ASC);

ALTER TABLE `ACT_HI_OP_LOG`
ADD COLUMN `CATEGORY_` VARCHAR(64) NULL DEFAULT NULL AFTER `REMOVAL_TIME_`,
ADD COLUMN `EXTERNAL_TASK_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `CATEGORY_`,
ADD COLUMN `ANNOTATION_` VARCHAR(4000) NULL DEFAULT NULL AFTER `EXTERNAL_TASK_ID_`,
ADD INDEX `ACT_IDX_HI_OP_LOG_ROOT_PI` (`ROOT_PROC_INST_ID_` ASC),
ADD INDEX `ACT_IDX_HI_OP_LOG_RM_TIME` (`REMOVAL_TIME_` ASC),
ADD INDEX `ACT_IDX_HI_OP_LOG_TASK` (`TASK_ID_` ASC),
ADD INDEX `ACT_IDX_HI_OP_LOG_TIMESTAMP` (`TIMESTAMP_` ASC),
ADD INDEX `ACT_IDX_HI_OP_LOG_USER_ID` (`USER_ID_` ASC),
ADD INDEX `ACT_IDX_HI_OP_LOG_OP_TYPE` (`OPERATION_TYPE_` ASC),
ADD INDEX `ACT_IDX_HI_OP_LOG_ENTITY_TYPE` (`ENTITY_TYPE_` ASC);

ALTER TABLE `ACT_HI_PROCINST`
ADD INDEX `ACT_IDX_HI_PI_PDEFID_END_TIME` (`PROC_DEF_ID_` ASC, `END_TIME_` ASC),
ADD INDEX `ACT_IDX_HI_PRO_INST_ROOT_PI` (`ROOT_PROC_INST_ID_` ASC),
ADD INDEX `ACT_IDX_HI_PRO_INST_RM_TIME` (`REMOVAL_TIME_` ASC);

ALTER TABLE `ACT_HI_TASKINST`
ADD INDEX `ACT_IDX_HI_TASK_INST_RM_TIME` (`REMOVAL_TIME_` ASC),
ADD INDEX `ACT_IDX_HI_TASK_INST_START` (`START_TIME_` ASC),
ADD INDEX `ACT_IDX_HI_TASK_INST_END` (`END_TIME_` ASC);

ALTER TABLE `ACT_HI_VARINST`
ADD INDEX `ACT_IDX_HI_VARINST_ROOT_PI` (`ROOT_PROC_INST_ID_` ASC),
ADD INDEX `ACT_IDX_HI_VARINST_RM_TIME` (`REMOVAL_TIME_` ASC),
ADD INDEX `ACT_IDX_HI_VAR_PI_NAME_TYPE` (`PROC_INST_ID_` ASC, `NAME_` ASC, `VAR_TYPE_` ASC),
ADD INDEX `ACT_IDX_HI_VARINST_NAME` (`NAME_` ASC),
ADD INDEX `ACT_IDX_HI_VARINST_ACT_INST_ID` (`ACT_INST_ID_` ASC);

-- Repository Tables
ALTER TABLE `ACT_RE_DEPLOYMENT`
CHANGE COLUMN `DEPLOY_TIME_` `DEPLOY_TIME_` DATETIME NULL DEFAULT NULL;

-- Runtime Tables
ALTER TABLE `ACT_RU_AUTHORIZATION`
ADD COLUMN `REMOVAL_TIME_` DATETIME NULL DEFAULT NULL AFTER `PERMS_`,
ADD COLUMN `ROOT_PROC_INST_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `REMOVAL_TIME_`,
ADD INDEX `ACT_IDX_AUTH_ROOT_PI` (`ROOT_PROC_INST_ID_` ASC),
ADD INDEX `ACT_IDX_AUTH_RM_TIME` (`REMOVAL_TIME_` ASC);

ALTER TABLE `ACT_RU_CASE_EXECUTION`
DROP INDEX `ACT_FK_CASE_EXE_CASE_INST` ,
ADD INDEX `ACT_IDX_CASE_EXE_CASE_INST` (`CASE_INST_ID_` ASC);

ALTER TABLE `ACT_RU_EVENT_SUBSCR`
CHANGE COLUMN `CREATED_` `CREATED_` DATETIME NOT NULL;

ALTER TABLE `ACT_RU_EXECUTION`
ADD INDEX `ACT_IDX_EXEC_ROOT_PI` (`ROOT_PROC_INST_ID_` ASC);

ALTER TABLE `ACT_RU_EXT_TASK`
CHANGE COLUMN `LOCK_EXP_TIME_` `LOCK_EXP_TIME_` DATETIME NULL DEFAULT NULL ,
ADD COLUMN `LAST_FAILURE_LOG_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `PRIORITY_`;

ALTER TABLE `ACT_RU_INCIDENT`
CHANGE COLUMN `INCIDENT_TIMESTAMP_` `INCIDENT_TIMESTAMP_` DATETIME NOT NULL ,
ADD COLUMN `FAILED_ACTIVITY_ID_` VARCHAR(255) NULL DEFAULT NULL AFTER `JOB_DEF_ID_`,
ADD COLUMN `ANNOTATION_` VARCHAR(4000) NULL DEFAULT NULL AFTER `FAILED_ACTIVITY_ID_`;

ALTER TABLE `ACT_RU_JOB`
CHANGE COLUMN `LOCK_EXP_TIME_` `LOCK_EXP_TIME_` DATETIME NULL DEFAULT NULL ,
CHANGE COLUMN `DUEDATE_` `DUEDATE_` DATETIME NULL DEFAULT NULL ,
ADD COLUMN `REPEAT_OFFSET_` BIGINT(20) NULL DEFAULT '0' AFTER `REPEAT_`,
ADD COLUMN `LAST_FAILURE_LOG_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `CREATE_TIME_`,
ADD COLUMN `FAILED_ACT_ID_` VARCHAR(255) NULL DEFAULT NULL AFTER `LAST_FAILURE_LOG_ID_`;

ALTER TABLE `ACT_RU_JOBDEF`
ADD COLUMN `DEPLOYMENT_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `TENANT_ID_`;

ALTER TABLE `ACT_RU_METER_LOG`
CHANGE COLUMN `TIMESTAMP_` `TIMESTAMP_` DATETIME NULL DEFAULT NULL;

ALTER TABLE `ACT_RU_TASK`
CHANGE COLUMN `CREATE_TIME_` `CREATE_TIME_` DATETIME NULL DEFAULT NULL ,
ADD INDEX `ACT_IDX_TASK_OWNER` (`OWNER_` ASC);

ALTER TABLE `ACT_RU_VARIABLE`
ADD COLUMN `PROC_DEF_ID_` VARCHAR(64) NULL DEFAULT NULL AFTER `PROC_INST_ID_`,
ADD COLUMN `BATCH_ID_` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL AFTER `TASK_ID_`,
ADD INDEX `ACT_IDX_BATCH_ID` (`BATCH_ID_` ASC),
ADD INDEX `ACT_IDX_VARIABLE_TASK_NAME_TYPE` (`TYPE_` ASC, `NAME_` ASC, `TASK_ID_` ASC);
ALTER TABLE `ACT_RU_VARIABLE`
ADD CONSTRAINT `ACT_FK_VAR_BATCH`
  FOREIGN KEY (`BATCH_ID_`)
  REFERENCES `ACT_RU_BATCH` (`ID_`)
  ON DELETE RESTRICT
  ON UPDATE RESTRICT;