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
Solved! Go to 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.
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.
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.
Ask for and offer help to other Alfresco Content Services Users and members of the Alfresco team.
Related links:
By using this site, you are agreeing to allow us to collect and use cookies as outlined in Alfresco’s Cookie Statement and Terms of Use (and you have a legitimate interest in Alfresco and our products, authorizing us to contact you in such methods). If you are not ok with these terms, please do not use this website.