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
