Upgrading from 5.0c to 6.07 via 5.1g; multiple schema issues.

[James56]

Hi,

We've an old 5.0c alfresco instance that we need to update, using a mysql database, installed a 5.1g instance from the file alfresco-community-installer-201605-linux-x64.bin. That uses a postgres database, so I installed a mysql database imported the dump, changed the connection parameters, added the mysql connector, and got it to come up, after modifying the database with the lower_Case

I used this docker-compose file https://github.com/keensoft/docker-alfresco for 6.07, then replaced postgres following the instructions here https://github.com/keensoft/docker-alfresco/wiki/Using-MariaDB-instead-of-PostgreSQL-as-database. I set up a standalone mysql docker container first to upload the data using the same volume format as the docker-compose, uploaded the data, then shut the container before copying the volumes to the location expected by the docker-compose file. Tried starting the docker-compose, but it complained about not being able to find the ACT_GE_PROPERTY table, when a lower case one existed. I then converted all the table names to upper case, and it got through that error after a restart. Now I stuck with this error;

|  2020-09-17 10:52:49,660  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Connecting to database: jdbc:mariadb://db:3306/alfresco?useUnicode=true&chacterEncoding=UTF-8, UserName=alfresco, MariaDB connector/J
alfresco       |  2020-09-17 10:52:49,660  INFO  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema managed by database dialect org.alfresco.repo.domain.dialect.MariaDBDialect.
alfresco       |  2020-09-17 10:52:49,707  ERROR [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema auto-update failed
alfresco       |  org.alfresco.error.AlfrescoRuntimeException: 08170000
alfresco       | More than one Alfresco schema was found when querying the database metadata.
alfresco       |    Limit the database user's permissions or set the 'hibernate.default_schema' property in 'custom-hibernate-dialect.properties'.
alfresco       |        at org.alfresco.repo.domain.schema.SchemaBootstrap.countAppliedPatches(SchemaBootstrap.java:595)
alfresco       |        at org.alfresco.repo.domain.schema.SchemaBootstrap.updateSchema(SchemaBootstrap.java:860)
alfresco       |        at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.java:1646)
alfresco       |        at org.springframework.extensions.surf.util.AbstractLifecycleBean.onApplicationEvent(AbstractLifecycleBean.java:56)
alfresco       |        at org.alfresco.repo.management.SafeApplicationEventMulticaster.multicastEventInternal(SafeApplicationEventMulticaster.java:221)
alfresco       |        at org.alfresco.repo.management.SafeApplicationEventMulticaster.multicastEvent(SafeApplicationEventMulticaster.java:186)
alfresco       |        at org.alfresco.repo.management.SafeApplicationEventMulticaster.multicastEvent(SafeApplicationEventMulticaster.java:206)
alfresco       |        at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:399)
alfresco       |        at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:353)
alfresco       |        at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:887)
alfresco       |        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:552)
alfresco       |        at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:409)
alfresco       |        at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:291)
alfresco       |        at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:103)
alfresco       |        at org.alfresco.web.app.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:70)
alfresco       |        at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4753)
alfresco       |        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5215)
alfresco       |        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
alfresco       |        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:752)
alfresco       |        at org.apache.catalina.core.ContainerBase.access$000(ContainerBase.java:129)
alfresco       |        at org.apache.catalina.core.ContainerBase$PrivilegedAddChild.run(ContainerBase.java:150)
alfresco       |        at org.apache.catalina.core.ContainerBase$PrivilegedAddChild.run(ContainerBase.java:140)
alfresco       |        at java.security.AccessController.doPrivileged(Native Method)
alfresco       |        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:726)
alfresco       |        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:734)
alfresco       |        at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1141)
alfresco       |        at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1875)
alfresco       |        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
alfresco       |        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
alfresco       |        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
alfresco       |        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
alfresco       |        at java.lang.Thread.run(Thread.java:748)
alfresco       | 17-Sep-2020 10:52:49.719 SEVERE [localhost-startStop-1] org.apache.catalina.core.StandardContext.startInternal One or more listeners failed to start. Full details will be found in the appropriate container log file
alfresco       | 17-Sep-2020 10:52:49.720 SEVERE [localhost-startStop-1] org.apache.catalina.core.StandardContext.startInternal Context [/alfresco] startup failed due to previous errors

Here's a list of the tables in the alfresco database;

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| alfresco           |
| information_schema |
+--------------------+
2 rows in set (0.001 sec)

MariaDB [(none)]> use alfresco;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [alfresco]> show tables;
+--------------------------------+
| Tables_in_alfresco             |
+--------------------------------+
| ACT_EVT_LOG                    |
| ACT_GE_BYTEARRAY               |
| ACT_GE_PROPERTY                |
| ACT_HI_ACTINST                 |
| ACT_HI_ATTACHMENT              |
| ACT_HI_COMMENT                 |
| ACT_HI_DETAIL                  |
| ACT_HI_IDENTITYLINK            |
| ACT_HI_PROCINST                |
| ACT_HI_TASKINST                |
| ACT_HI_VARINST                 |
| ACT_ID_GROUP                   |
| ACT_ID_INFO                    |
| ACT_ID_MEMBERSHIP              |
| ACT_ID_USER                    |
| ACT_PROCDEF_INFO               |
| ACT_RE_DEPLOYMENT              |
| ACT_RE_MODEL                   |
| ACT_RE_PROCDEF                 |
| ACT_RU_EVENT_SUBSCR            |
| ACT_RU_EXECUTION               |
| ACT_RU_IDENTITYLINK            |
| ACT_RU_JOB                     |
| ACT_RU_TASK                    |
| ACT_RU_VARIABLE                |
| ALF_ACCESS_CONTROL_ENTRY       |
| ALF_ACCESS_CONTROL_LIST        |
| ALF_ACE_CONTEXT                |
| ALF_ACL_CHANGE_SET             |
| ALF_ACL_MEMBER                 |
| ALF_ACTIVITY_FEED              |
| ALF_ACTIVITY_FEED_CONTROL      |
| ALF_ACTIVITY_POST              |
| ALF_APPLIED_PATCH              |
| ALF_AUDIT_APP                  |
| ALF_AUDIT_ENTRY                |
| ALF_AUDIT_MODEL                |
| ALF_AUTHORITY                  |
| ALF_AUTHORITY_ALIAS            |
| ALF_AUTH_STATUS                |
| ALF_BOOTSTRAP_LOCK             |
| ALF_CHILD_ASSOC                |
| ALF_CONTENT_DATA               |
| ALF_CONTENT_URL                |
| ALF_CONTENT_URL_ENCRYPTION     |
| ALF_ENCODING                   |
| ALF_LOCALE                     |
| ALF_LOCK                       |
| ALF_LOCK_RESOURCE              |
| ALF_MIMETYPE                   |
| ALF_NAMESPACE                  |
| ALF_NODE                       |
| ALF_NODE_ASPECTS               |
| ALF_NODE_ASSOC                 |
| ALF_NODE_PROPERTIES            |
| ALF_PERMISSION                 |
| ALF_PROP_CLASS                 |
| ALF_PROP_DATE_VALUE            |
| ALF_PROP_DOUBLE_VALUE          |
| ALF_PROP_LINK                  |
| ALF_PROP_ROOT                  |
| ALF_PROP_SERIALIZABLE_VALUE    |
| ALF_PROP_STRING_VALUE          |
| ALF_PROP_UNIQUE_CTX            |
| ALF_PROP_VALUE                 |
| ALF_QNAME                      |
| ALF_SERVER                     |
| ALF_STORE                      |
| ALF_SUBSCRIPTIONS              |
| ALF_TENANT                     |
| ALF_TRANSACTION                |
| ALF_USAGE_DELTA                |
| AVM_ASPECTS                    |
| AVM_CHILD_ENTRIES              |
| AVM_HISTORY_LINKS              |
| AVM_MERGE_LINKS                |
| AVM_NODES                      |
| AVM_NODE_PROPERTIES            |
| AVM_STORES                     |
| AVM_STORE_PROPERTIES           |
| AVM_VERSION_LAYERED_NODE_ENTRY |
| AVM_VERSION_ROOTS              |
| JBPM_ACTION                    |
| JBPM_BYTEARRAY                 |
| JBPM_BYTEBLOCK                 |
| JBPM_COMMENT                   |
| JBPM_DECISIONCONDITIONS        |
| JBPM_DELEGATION                |
| JBPM_EVENT                     |
| JBPM_EXCEPTIONHANDLER          |
| JBPM_JOB                       |
| JBPM_LOG                       |
| JBPM_MODULEDEFINITION          |
| JBPM_MODULEINSTANCE            |
| JBPM_NODE                      |
| JBPM_POOLEDACTOR               |
| JBPM_PROCESSDEFINITION         |
| JBPM_PROCESSINSTANCE           |
| JBPM_RUNTIMEACTION             |
| JBPM_SWIMLANE                  |
| JBPM_SWIMLANEINSTANCE          |
| JBPM_TASK                      |
| JBPM_TASKACTORPOOL             |
| JBPM_TASKCONTROLLER            |
| JBPM_TASKINSTANCE              |
| JBPM_TOKEN                     |
| JBPM_TOKENVARIABLEMAP          |
| JBPM_TRANSITION                |
| JBPM_VARIABLEACCESS            |
| JBPM_VARIABLEINSTANCE          |
| MPH_NODE_TRANSACTION_CHECK     |
| SUBSCRIPTIONS                  |
| SUB_TRANSACTION                |
| vw_alf_acl_member              |
| vw_alf_node                    |
| vw_alf_node_properties         |
+--------------------------------+

Any help or advice would be gratefully received.

Thanks

James

[EddieMay]

Hi @James56 

Have you seen these resources? Keensoft guide, Keensoft Community guide, and Angel Borroy's DevCon 2019 slides & presentation.

HTH,

[afaust]

The error message already provides a helpful suggestion of what to do: Setting hibernate.default_schema inside alfresco-global.properties (or via -D flags in the JAVA_OPTS of the Docker-based instance) to specify which of the Alfresco schemas on the MySQL instance to use.

[James56]

Do you have an example of acceptable values for that parameter, or a link where I could find out more?

Thanks