Upgrade from 4.2.c to 4.2.f: Table 'ACT_RE_MODEL' already exists

[kjcsb]

I am attempting to upgrade from Alfresco 4.2.c to 4.2.f.
When I attempt to start Alfresco it attempts to upgrade the database schema and fails.
I have backed up the contentstore etc and database, installed 4.2.f and restored.

09:02:20,891 INFO  [org.alfresco.repo.admin] Connected to database MySQL version 5.1.73
09:02:41,684 INFO  [org.alfresco.repo.management.subsystems.ChildApplicationContextFactory] Starting 'sysAdmin' subsystem, ID: [sysAdmin, default]
09:02:41,759 INFO  [org.alfresco.repo.management.subsystems.ChildApplicationContextFactory] Startup of 'sysAdmin' subsystem, ID: [sysAdmin, default] complete
09:03:04,048 INFO  [org.springframework.extensions.webscripts.TemplateProcessorRegistry] Registered template processor Repository Template Processor for extension ftl
09:03:04,058 INFO  [org.springframework.extensions.webscripts.ScriptProcessorRegistry] Registered script processor Repository Script Processor for extension js
09:03:30,257 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Connecting to database: jdbc:mysql://localhost/alfresco_4_2_f?useUnicode=yes&characterEncoding=UTF-8, UserName=root@localhost, MySQL-AB JDBC Driver
09:03:30,257 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema managed by database dialect org.hibernate.dialect.MySQLInnoDBDialect.
09:03:35,269 WARN  [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema validation found 17 potential problems, results written to: /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/Alfresco-MySQLInnoDBDialect-Validation-Pre-Upgrade-alf_-2711090414622780509.txt
09:03:35,492 WARN  [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema validation found 3 potential problems, results written to: /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/Alfresco-MySQLInnoDBDialect-Validation-Pre-Upgrade-avm_-1187863595313832211.txt
09:03:36,121 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Compared database schema with reference schema (all OK): class path resource [alfresco/dbscripts/create/org.hibernate.dialect.MySQLInnoDBDialect/Schema-Reference-JBPM.xml]
09:03:36,594 WARN  [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema validation found 18 potential problems, results written to: /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/Alfresco-MySQLInnoDBDialect-Validation-Pre-Upgrade-act_-7995733133348594976.txt
09:03:38,186 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Normalized schema dumped to file /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/Alfresco-schema-MySQLInnoDBDialect-pre-upgrade-alf_-6601106991497332255.xml.
09:03:38,191 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Normalized schema dumped to file /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/Alfresco-schema-MySQLInnoDBDialect-pre-upgrade-avm_-6117092282234732860.xml.
09:03:38,192 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Normalized schema dumped to file /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/Alfresco-schema-MySQLInnoDBDialect-pre-upgrade-jbpm_-6787820686950500025.xml.
09:03:38,192 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Normalized schema dumped to file /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/Alfresco-schema-MySQLInnoDBDialect-pre-upgrade-act_-6417080319777992445.xml.
09:03:38,220 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-6011029607781483738.sql (Copied from classpath:alfresco/dbscripts/upgrade/4.1/org.hibernate.dialect.MySQLInnoDBDialect/dropAlfQnameFKIndexes.sql).
09:03:38,320 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-3448499369601272625.sql (Copied from classpath:alfresco/dbscripts/upgrade/4.2/org.hibernate.dialect.MySQLInnoDBDialect/remove-index-acl_id.sql).
09:03:38,330 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-6872751190083715898.sql (Copied from classpath:alfresco/dbscripts/upgrade/4.1/org.hibernate.dialect.MySQLInnoDBDialect/drop-activiti-feed-format.sql).
09:03:38,369 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-3062912971675197324.sql (Copied from classpath:alfresco/dbscripts/upgrade/4.1/org.hibernate.dialect.MySQLInnoDBDialect/fix-Repo-seqs-order.sql).
09:03:38,376 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-3246386263499213445.sql (Copied from classpath:alfresco/dbscripts/upgrade/4.1/org.hibernate.dialect.MySQLInnoDBDialect/ChildAssoc-OrderBy.sql).
09:03:50,518 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-8318623224242649507.sql (Copied from classpath:alfresco/dbscripts/upgrade/4.1/org.hibernate.dialect.MySQLInnoDBDialect/fix-AVM-seqs-order.sql).
09:03:50,525 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-499829208512531061.sql (Copied from classpath:alfresco/dbscripts/upgrade/4.2/org.hibernate.dialect.MySQLInnoDBDialect/drop-AVM-index.sql).
09:03:50,631 INFO  [org.alfresco.repo.domain.schema.SchemaBootstrap] Executing database script /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-6563793137898615044.sql (Copied from classpath:alfresco/dbscripts/upgrade/4.2/org.hibernate.dialect.MySQLInnoDBDialect/activiti-upgrade-5-11.sql).
09:03:50,829 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Statement execution failed:
   SQL: create table ACT_RE_MODEL (
    ID_ varchar(64) not null,
    REV_ integer,
    NAME_ varchar(255),
    KEY_ varchar(255),
    CATEGORY_ varchar(255),
    CREATE_TIME_ timestamp null,
    LAST_UPDATE_TIME_ timestamp null,
    VERSION_ integer,
    META_INFO_ varchar(4000),
    DEPLOYMENT_ID_ varchar(64),
    EDITOR_SOURCE_VALUE_ID_ varchar(64),
    EDITOR_SOURCE_EXTRA_VALUE_ID_ varchar(64),
    primary key (ID_)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_bin
   Error: Table 'ACT_RE_MODEL' already exists
   File: /mnt/Amazon-EBS-Volume/alfresco/alfresco-4.2.f/tomcat/temp/Alfresco/AlfrescoSchema-MySQLInnoDBDialect-Update-6563793137898615044.sql
   Line: 51
09:03:50,831 ERROR [org.alfresco.repo.domain.schema.SchemaBootstrap] Schema auto-update failed
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'ACT_RE_MODEL' already exists

Any suggestions appreciated

[kaynezhang]

activiti-upgrade-5-11.sql script is used to upgrade Activiti tables to 5.11 version,it seems table 'ACT_RE_MODEL' already exists when you  attempted to upgrade from Alfresco 4.2.c to 4.2.f.

You can bypass the alfresco patch that upgrade Activiti tables to 5.11 and manually uprade Activiti tables.
You can try below steps:

1.Stop alfresco server
2.Executing SQL Statements in file ${TOMCAT_HOME}\webapps\alfresco\WEB-INF\classes\alfresco\dbscripts\upgrade\4.2\org.hibernate.dialect.MySQLInnoDBDialect\activiti-upgrade-5-11.sql using mysql command or source command.
3.make a backup of file ${TOMCAT_HOME}\webapps\alfresco\WEB-INF\classes\alfresco\dbscripts\upgrade\4.2\org.hibernate.dialect.MySQLInnoDBDialect\activiti-upgrade-5-11.sql
4.Clear all content in file ${TOMCAT_HOME}\webapps\alfresco\WEB-INF\classes\alfresco\dbscripts\upgrade\4.2\org.hibernate.dialect.MySQLInnoDBDialect\activiti-upgrade-5-11.sql
5.Start alfresco server again
6.restore file ${TOMCAT_HOME}\webapps\alfresco\WEB-INF\classes\alfresco\dbscripts\upgrade\4.2\org.hibernate.dialect.MySQLInnoDBDialect\activiti-upgrade-5-11.sql
‍‍‍‍‍‍‍‍

[kjcsb]

Upgrade script used ${TRUE}
It turned out there was a problem with a number of the MySQL scripts - they attempted to use variable ${TRUE} which didn't work on the version on MySQL that I'm using. So I updated the script dropped and recreated the database, reimported the database from Alfresco 4.2.c and then the upgrade worked correctly.

For example from
INSERT INTO alf_applied_patch
  (id, description, fixes_from_schema, fixes_to_schema, applied_to_schema, target_schema, applied_on_date, applied_to_server, was_executed, succeeded, report)
  VALUES
  (
    'patch.db-V4.2-upgrade-to-activiti-5.10', 'Manually executed script upgrade V4.2: Upgraded Activiti tables to 5.10 version',
    0, 5111, -1, 5112, null, 'UNKNOWN', ${TRUE}, ${TRUE}, 'Script completed'
  );

To

INSERT INTO alf_applied_patch
  (id, description, fixes_from_schema, fixes_to_schema, applied_to_schema, target_schema, applied_on_date, applied_to_server, was_executed, succeeded, report)
  VALUES
  (
    'patch.db-V4.2-upgrade-to-activiti-5.10', 'Manually executed script upgrade V4.2: Upgraded Activiti tables to 5.10 version',
    0, 5111, -1, 5112, null, 'UNKNOWN', TRUE, TRUE, 'Script completed'
  );