Upgrade Alfresco 5.0.2 to 7.0.1 via Alfresco 5.2.7 - schema validation problems...

[jego]

Hello community,

we are currently upgrading an Alfresco system from version 5.0.2 to 7.0.1 via 5.2.7.

We have installed Alfresco 5.2.7 via installer besides the current system 5.0.2, done a backup of db/contentstore and configured database connection/content store of Alfresco 5.2.7 to the existing installation of 5.0.2.

Alfresco 5.2.7 starts with 26 schema validation problems for ALF tables after the upgrade, executes all patches and starts correctly. 

Following you can see the log output with the interesting log statements: 

2021-11-01 15:51:31,865  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Connecting to database: jdbc:sqlserver://xxxx:1433;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;multiSubnetFailover=false;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=Alfresco;applicationName=Microsoft JDBC Driver for SQL Server;applicationIntent=readwrite;, UserName=alfresco, Microsoft JDBC Driver 4.2 for SQL Server
2021-11-01 15:51:31,865  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema managed by database dialect org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect.
2021-11-01 15:51:33,562  WARN  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema validation found 27 potential problems, results written to: C:\ALFRES~1\tomcat\temp\Alfresco\Alfresco-AlfrescoSQLServerDialect-Validation-Pre-Upgrade-alf_-5779302402461212900.txt
2021-11-01 15:51:33,798  WARN  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema validation found 5 potential problems, results written to: C:\ALFRES~1\tomcat\temp\Alfresco\Alfresco-AlfrescoSQLServerDialect-Validation-Pre-Upgrade-act_-2912479917371243762.txt
2021-11-01 15:51:34,580  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Normalized schema dumped to file C:\ALFRES~1\tomcat\temp\Alfresco\Alfresco-schema-AlfrescoSQLServerDialect-pre-upgrade-alf_-275507361636859328.xml.
2021-11-01 15:51:34,581  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Normalized schema dumped to file C:\ALFRES~1\tomcat\temp\Alfresco\Alfresco-schema-AlfrescoSQLServerDialect-pre-upgrade-act_-2020508141509391966.xml.
2021-11-01 15:51:34,592  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Executing database script C:\ALFRES~1\tomcat\temp\Alfresco\AlfrescoSchema-AlfrescoSQLServerDialect-Update-5091873916181496267.sql (Copied from classpath:alfresco/dbscripts/create/org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect/AlfrescoCreate-AuthorizationTables.sql).
2021-11-01 15:51:34,671  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Executing database script C:\ALFRES~1\tomcat\temp\Alfresco\AlfrescoSchema-AlfrescoSQLServerDialect-Update-971135275112593823.sql (Copied from classpath:alfresco/dbscripts/upgrade/4.2/org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect/migrate-activiti-workflows.sql).
2021-11-01 15:51:36,546  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Executing database script C:\ALFRES~1\tomcat\temp\Alfresco\AlfrescoSchema-AlfrescoSQLServerDialect-Update-3803297271346606423.sql (Copied from classpath:alfresco/dbscripts/upgrade/5.0/org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect/update-activiti-default-timestamp-column.sql).
2021-11-01 15:51:36,553  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Executing database script C:\ALFRES~1\tomcat\temp\Alfresco\AlfrescoSchema-AlfrescoSQLServerDialect-Update-6376613993017353624.sql (Copied from classpath:alfresco/dbscripts/upgrade/5.0/org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect/activiti-correct-tenant-id-MSSQL.sql).
2021-11-01 15:51:36,609  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Executing database script C:\ALFRES~1\tomcat\temp\Alfresco\AlfrescoSchema-AlfrescoSQLServerDialect-Update-4256814354780035160.sql (Copied from classpath:alfresco/dbscripts/upgrade/5.1/org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect/activiti-upgrade-5-19-0.sql).
2021-11-01 15:51:36,764  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] All executed statements:  C:\ALFRES~1\tomcat\temp\Alfresco\AlfrescoSchema-AlfrescoSQLServerDialect-All_Statements-8520004682295926697.sql.
2021-11-01 15:51:37,355  WARN  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema validation found 26 potential problems, results written to: C:\ALFRES~1\tomcat\temp\Alfresco\Alfresco-AlfrescoSQLServerDialect-Validation-Post-Upgrade-alf_-783086563026627211.txt
2021-11-01 15:51:37,595  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Compared database schema with reference schema (all OK): class path resource [alfresco/dbscripts/create/org.alfresco.repo.domain.hibernate.dialect.AlfrescoSQLServerDialect/Schema-Reference-ACT.xml]
2021-11-01 15:51:38,272  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Normalized schema dumped to file C:\ALFRES~1\tomcat\temp\Alfresco\Alfresco-schema-AlfrescoSQLServerDialect-post-upgrade-alf_-6540824298169654101.xml.
2021-11-01 15:51:38,272  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Normalized schema dumped to file C:\ALFRES~1\tomcat\temp\Alfresco\Alfresco-schema-AlfrescoSQLServerDialect-post-upgrade-act_-875357230888710983.xml.
...
2021-11-01 15:52:54,883  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] Checking for patches to apply ...
2021-11-01 15:52:55,309  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.downloadsFolder' (Ensures the Syste Downloads folder exists.).
2021-11-01 15:52:55,348  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.alfrescoModelAdministrators' (Adds the 'GROUP_ALFRESCO_MODEL_ADMINISTRATORS' group).
2021-11-01 15:52:55,576  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.addInviteAddDirectEmailTemplates' (Adds the email templates for the add-direct invite flow).
2021-11-01 15:52:55,914  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.imapSpacesTemplates4' (Replaces content templates for IMAP clients).
2021-11-01 15:52:57,479  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.addSurfConfigFolders' (Adds 'cm:extensions' and 'cm:module-deployments' folders into surf-config folder.).
2021-11-01 15:52:57,744  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.spacesBootstrapSmartTemplatesFolder' (Adds Smart Templates Folder in Data Dictionary.).
2021-11-01 15:52:57,836  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.spacesBootstrapSmartFolderExample' (Adds smartFoldersExample.json file in Smart Templates Folder.).
2021-11-01 15:52:57,936  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.spacesBootstrapSmartDownloadFolder' (Adds Smart Download Folder in Data Dictionary.).
2021-11-01 15:52:58,019  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.redeployInvitationNominatedAddDirectActivitiWorkflow' (Patch that redeploys activitiInvitationNominatedAddDirect workflow after upgrade, needed for tenants created before 5.1).

...
2021-11-01 15:52:58,158  INFO  [management.subsystems.ChildApplicationContextFactory] [localhost-startStop-1] Startup of 'Authentication' subsystem, ID: [Authentication, managed, ldap1] complete
2021-11-01 15:52:58,202  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.updateAdminUserWhenDefault' (Update Admin User by removing the default SHA256 and falling back to the MD4 (please consider using BCRYPT instead)).
2021-11-01 15:52:58,233  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.addInviteModeratedEmailTemplates' (Adds the email template for the invite moderated flow).
2021-11-01 15:52:58,282  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 	Applying patch 'patch.redeployInvitationModeratedActivitiWorkflow' (Redeploy invitation moderated workflow.).
2021-11-01 15:52:58,349  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.db-V4.1-AuthorizationTables
RESULT: 
Script completed
=====================================
2021-11-01 15:52:58,349  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.db-v4.2-migrate-activiti-workflows
RESULT: 
Script completed
=====================================
2021-11-01 15:52:58,349  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.db-V5.0-update-activiti-default-timestamp-column
RESULT: 
Script completed
=====================================
2021-11-01 15:52:58,349  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.db-V5.0-activiti-correct-tenant-id-MSSQL
RESULT: 
Script completed
=====================================
2021-11-01 15:52:58,349  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.downloadsFolder
RESULT: 
Bootstrap location already exists: /sys:system/sys:downloads
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.alfrescoModelAdministrators
RESULT: 
Adding group authority.
Successfully added group authority: GROUP_ALFRESCO_MODEL_ADMINISTRATORS
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.addInviteAddDirectEmailTemplates
RESULT: 
Imported view into bootstrap location: /app:company_home/app:dictionary/app:email_templates/cm:invite (workspace://SpacesStore/ff3f9e40-52ef-421c-8638-f13dfc7314f8)
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.db-V5.1-upgrade-to-activiti-5.19.0
RESULT: 
Script completed
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.imapSpacesTemplates4
RESULT: 
Imported view into bootstrap location: /app:company_home/app:dictionary/app:imap_configs/app:imap_templates (workspace://SpacesStore/ff3f9e40-52ef-421c-8638-f13dfc7314f8)
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.addSurfConfigFolders
RESULT: 
Adding surf-config children.
Successfully added 'extensions' folders and set the required permissions for 'extensions' folders. 
Skipped 'module-deployments' folders as they already exist, but set the required permissions for 'module-deployments' folders.
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.spacesBootstrapSmartDownloadFolder
RESULT: 
Imported view into bootstrap location: /app:company_home/app:dictionary (workspace://SpacesStore/ff3f9e40-52ef-421c-8638-f13dfc7314f8)
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.spacesBootstrapSmartFolderExample
RESULT: 
Imported view into bootstrap location: /app:company_home/app:dictionary/app:smart_folders (workspace://SpacesStore/ff3f9e40-52ef-421c-8638-f13dfc7314f8)
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.spacesBootstrapSmartTemplatesFolder
RESULT: 
Imported view into bootstrap location: /app:company_home/app:dictionary (workspace://SpacesStore/ff3f9e40-52ef-421c-8638-f13dfc7314f8)
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.redeployInvitationNominatedAddDirectActivitiWorkflow
RESULT: 
Re-deployed 1 workflows. 
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.updateAdminUserWhenDefault
RESULT: 
Update Admin User SHA256 When default
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.addInviteModeratedEmailTemplates
RESULT: 
Imported view into bootstrap location: /app:company_home/app:dictionary/app:email_templates/cm:invite (workspace://SpacesStore/ff3f9e40-52ef-421c-8638-f13dfc7314f8)
=====================================
2021-11-01 15:52:58,350  INFO  [admin.patch.PatchExecuter] [localhost-startStop-1] 
=== Applied patch                 === 
ID: patch.redeployInvitationModeratedActivitiWorkflow
RESULT: 
Re-deployed 1 workflows. 
=====================================
...
2021-11-01 15:52:59,192  INFO  [service.descriptor.DescriptorService] [localhost-startStop-1] Alfresco Content Services started (Enterprise). Current version: 5.2.7 (.2 rde4c74f1-b29) schema 10.095. Originally installed version: 3.2.0 (r 416) schema 3.400.

The validation problems are listed here:

Validation: primary key dbo.alf_access_control_entry.PK__t_alf_ac__3213E83F93461389.name="PK__t_alf_ac__3213E83F93461389" fails to match rule: name must match pattern 'PK__alf_acce__[0-9A-Z]+'
Redundancy: 2 items matching Index[name=aclm_acl_id], matches: Index[name=aclm_acl_id], Index[name=UQ__alf_acl___A667CB5302FC7413]
Redundancy: 2 items matching Index[name=fk_alf_cass_cnode], matches: Index[name=fk_alf_cass_cnode], Index[name=IDX_ALF_CASS_ASSOC_CHILDNODEID]
Difference: expected column dbo.alf_access_control_entry.authority_id.order="4", but was dbo.alf_access_control_entry.authority_id.order="5"
Difference: expected column dbo.alf_access_control_entry.allowed.order="5", but was dbo.alf_access_control_entry.allowed.order="4"
Difference: expected index dbo.alf_access_control_list.acl_id.name="acl_id", but was dbo.alf_access_control_list.UQ__alf_acce__1A85023974AE54BC.name="UQ__alf_acce__1A85023974AE54BC"
Difference: expected index dbo.alf_acl_member.aclm_acl_id.name="aclm_acl_id", but was dbo.alf_acl_member.UQ__alf_acl___A667CB5302FC7413.name="UQ__alf_acl___A667CB5302FC7413"
Difference: expected column dbo.alf_activity_feed.activity_summary.type="nvarchar(1024)", but was dbo.alf_activity_feed.activity_summary.type="nvarchar(4000)"
Difference: expected column dbo.alf_activity_post.activity_data.type="nvarchar(1024)", but was dbo.alf_activity_post.activity_data.type="nvarchar(4000)"
Difference: expected index dbo.alf_authority.authority.name="authority", but was dbo.alf_authority.UQ__alf_auth__6A581B3C2BFE89A6.name="UQ__alf_auth__6A581B3C2BFE89A6"
Difference: expected index dbo.alf_authority_alias.auth_id.name="auth_id", but was dbo.alf_authority_alias.UQ__alf_auth__5E9DBE3632AB8735.name="UQ__alf_auth__5E9DBE3632AB8735"
Difference: expected index dbo.alf_child_assoc.parent_node_id.name="parent_node_id", but was dbo.alf_child_assoc.UQ__alf_chil__4F6E35D0395884C4.name="UQ__alf_chil__4F6E35D0395884C4"
Difference: expected index dbo.alf_child_assoc.fk_alf_cass_cnode.name="fk_alf_cass_cnode", but was dbo.alf_child_assoc.IDX_ALF_CASS_ASSOC_CHILDNODEID.name="IDX_ALF_CASS_ASSOC_CHILDNODEID"
Difference: expected index dbo.alf_locale.locale_str.name="locale_str", but was dbo.alf_locale.UQ__alf_loca__4083C4DD4A8310C6.name="UQ__alf_loca__4083C4DD4A8310C6"
Difference: expected index dbo.alf_namespace.uri.name="uri", but was dbo.alf_namespace.UQ__alf_name__DD77841255009F39.name="UQ__alf_name__DD77841255009F39"
Difference: missing index from database, expected at path: dbo.alf_node_properties.idx_alf_nprop_b
Difference: missing index from database, expected at path: dbo.alf_node_properties.idx_alf_nprop_d
Difference: missing index from database, expected at path: dbo.alf_node_properties.idx_alf_nprop_f
Difference: expected index dbo.alf_permission.type_qname_id.name="type_qname_id", but was dbo.alf_permission.UQ__alf_perm__2481F3F870A8B9AE.name="UQ__alf_perm__2481F3F870A8B9AE"
Difference: expected index dbo.alf_qname.ns_id.name="ns_id", but was dbo.alf_qname.UQ__alf_qnam__9A67BDF27755B73D.name="UQ__alf_qnam__9A67BDF27755B73D"
Difference: expected index dbo.alf_server.ip_address.name="ip_address", but was dbo.alf_server.UQ__alf_serv__5376BCC47E02B4CC.name="UQ__alf_serv__5376BCC47E02B4CC"
Difference: expected index dbo.alf_store.protocol.name="protocol", but was dbo.alf_store.UQ__alf_stor__DFDC478604AFB25B.name="UQ__alf_stor__DFDC478604AFB25B"
Difference: unexpected table found in database with path: dbo.alf_audit_config
Difference: unexpected table found in database with path: dbo.alf_audit_date
Difference: unexpected table found in database with path: dbo.alf_audit_fact
Difference: unexpected table found in database with path: dbo.alf_audit_source

How can these integritiy errors occur? Another upgrade process was not working fully?

How would you deal with these problems? Ignore them? Or solve them because it could lead to follow up problems when upgrading to Alfresco 7.0.1?

Thanks

Jens

[angelborroy · Accepted solution]

I'd try the full upgrade path to 7.0.1 in order to understand what is the final result. Probably many of these errors can be ignored.

[jego]

Thanks for your answer. Yes, we are doing this and I will report back.

We just asked because we have a database migration from MSSQL to Postgres as well and I do not want to run into complicated issues with schema validation problems.