Moving a Mysql alfresco database from 201707 to docker 6.07

[James56]

Hi,

Having lots of errors moving this database across from 201707 Alfresco Mysql instance which was working. I restored the data on a separate mariadb, and moved the volumes with the data across to the 6.07. All systems are linux, although I had to run lower_case_table_names = 1 on the 201707 system for the mysql database. However there's a huge problem with  DBlock failing before timing out. Here's one of the later cycles before the system gives up.

2020-09-29 15:32:28,741 INFO [domain.schema.SchemaBootstrap] [localhost-startStop-1] DB lock failed o
n schema bootstrap. Attempt: 22
2020-09-29 15:32:34,557 WARN [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema validatio
n found 105 potential problems, results written to: /usr/local/tomcat/temp/Alfresco/Alfresco-MariaDBDialect-Validation-Pre-Upgrade-alf_-3354213175866470103.txt
2020-09-29 15:32:35,014 WARN [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema validatio
n found 50 potential problems, results written to: /usr/local/tomcat/temp/Alfresco/Alfresco-MariaDBDiale
ct-Validation-Pre-Upgrade-act_-1068095970747366843.txt
2020-09-29 15:32:36,152 INFO [domain.schema.SchemaBootstrap] [localhost-startStop-1] Normalized schem
a dumped to file /usr/local/tomcat/temp/Alfresco/Alfresco-schema-MariaDBDialect-pre-upgrade-alf_-3301880
316733150697.xml.
2020-09-29 15:32:36,152 INFO [domain.schema.SchemaBootstrap] [localhost-startStop-1] Normalized schem
a dumped to file /usr/local/tomcat/temp/Alfresco/Alfresco-schema-MariaDBDialect-pre-upgrade-act_-1232049
871542441346.xml.
2020-09-29 15:32:36,152 INFO [domain.schema.SchemaBootstrap] [localhost-startStop-1] DB lock failed o
n schema bootstrap. Attempt: 23
2020-09-29 15:32:41,158 ERROR [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema auto-upda
te failed
org.alfresco.error.AlfrescoRuntimeException: 08290000 A long running upgrade was found to be running on
another node of the cluster. This node is shutting down.
at org.alfresco.repo.domain.schema.SchemaBootstrap.onBootstrap(SchemaBootstrap.jav

The problems with the potential problems are listed below; here's the 105 problems;

Difference: expected primary key .alf_access_control_entry..name="", but was .alf_access_control_entry.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_access_control_entry.PRIMARY
Difference: expected primary key .alf_access_control_list..name="", but was .alf_access_control_list.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_access_control_list.PRIMARY
Difference: expected primary key .alf_ace_context..name="", but was .alf_ace_context.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_ace_context.PRIMARY
Difference: expected primary key .alf_acl_change_set..name="", but was .alf_acl_change_set.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_acl_change_set.PRIMARY
Difference: expected primary key .alf_acl_member..name="", but was .alf_acl_member.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_acl_member.PRIMARY
Difference: expected primary key .alf_activity_feed..name="", but was .alf_activity_feed.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_activity_feed.PRIMARY
Difference: expected primary key .alf_activity_feed_control..name="", but was .alf_activity_feed_control.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_activity_feed_control.PRIMARY
Difference: expected primary key .alf_activity_post..name="", but was .alf_activity_post.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_activity_post.PRIMARY
Difference: expected primary key .alf_applied_patch..name="", but was .alf_applied_patch.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_applied_patch.PRIMARY
Difference: expected primary key .alf_audit_app..name="", but was .alf_audit_app.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_audit_app.PRIMARY
Difference: expected primary key .alf_audit_entry..name="", but was .alf_audit_entry.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_audit_entry.PRIMARY
Difference: expected primary key .alf_audit_model..name="", but was .alf_audit_model.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_audit_model.PRIMARY
Difference: expected primary key .alf_auth_status..name="", but was .alf_auth_status.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_auth_status.PRIMARY
Difference: expected primary key .alf_authority..name="", but was .alf_authority.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_authority.PRIMARY
Difference: expected primary key .alf_authority_alias..name="", but was .alf_authority_alias.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_authority_alias.PRIMARY
Difference: expected primary key .alf_child_assoc..name="", but was .alf_child_assoc.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_child_assoc.PRIMARY
Difference: expected primary key .alf_content_data..name="", but was .alf_content_data.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_content_data.PRIMARY
Difference: expected primary key .alf_content_url..name="", but was .alf_content_url.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_content_url.PRIMARY
Difference: missing index from database, expected at path: .alf_content_url.idx_alf_conturl_sz
Difference: expected primary key .alf_content_url_encryption..name="", but was .alf_content_url_encryption.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_content_url_encryption.PRIMARY
Difference: expected primary key .alf_encoding..name="", but was .alf_encoding.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_encoding.PRIMARY
Difference: expected primary key .alf_locale..name="", but was .alf_locale.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_locale.PRIMARY
Difference: expected primary key .alf_lock..name="", but was .alf_lock.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_lock.PRIMARY
Difference: missing index from database, expected at path: .alf_lock_resource.PRIMARY
Difference: expected primary key .alf_mimetype..name="", but was .alf_mimetype.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_mimetype.PRIMARY
Difference: expected primary key .alf_namespace..name="", but was .alf_namespace.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_namespace.PRIMARY
Difference: expected primary key .alf_node..name="", but was .alf_node.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_node.PRIMARY
Difference: missing index from database, expected at path: .alf_node.idx_alf_node_mdq
Difference: missing index from database, expected at path: .alf_node.idx_alf_node_cor
Difference: missing index from database, expected at path: .alf_node.idx_alf_node_crd
Difference: missing index from database, expected at path: .alf_node.idx_alf_node_mor
Difference: missing index from database, expected at path: .alf_node.idx_alf_node_mod
Difference: unexpected index found in database with path: .alf_node.idx_alf_node_uuid
Difference: expected primary key .alf_node_aspects..name="", but was .alf_node_aspects.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_node_aspects.PRIMARY
Difference: expected primary key .alf_node_assoc..name="", but was .alf_node_assoc.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_node_assoc.PRIMARY
Difference: expected primary key .alf_node_properties..name="", but was .alf_node_properties.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_node_properties.PRIMARY
Difference: missing index from database, expected at path: .alf_node_properties.idx_alf_nprop_s
Difference: missing index from database, expected at path: .alf_node_properties.idx_alf_nprop_l
Difference: missing index from database, expected at path: .alf_node_properties.idx_alf_nprop_b
Difference: missing index from database, expected at path: .alf_node_properties.idx_alf_nprop_d
Difference: missing index from database, expected at path: .alf_node_properties.idx_alf_nprop_f
Difference: expected primary key .alf_permission..name="", but was .alf_permission.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_permission.PRIMARY
Difference: expected primary key .alf_prop_class..name="", but was .alf_prop_class.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_prop_class.PRIMARY
Difference: expected primary key .alf_prop_date_value..name="", but was .alf_prop_date_value.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_prop_date_value.PRIMARY
Difference: expected primary key .alf_prop_double_value..name="", but was .alf_prop_double_value.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_prop_double_value.PRIMARY
Difference: expected primary key .alf_prop_link..name="", but was .alf_prop_link.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_prop_link.PRIMARY
Difference: expected primary key .alf_prop_root..name="", but was .alf_prop_root.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_prop_root.PRIMARY
Difference: expected primary key .alf_prop_serializable_value..name="", but was .alf_prop_serializable_value.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_prop_serializable_value.PRIMARY
Difference: expected primary key .alf_prop_string_value..name="", but was .alf_prop_string_value.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_prop_string_value.PRIMARY
Difference: expected primary key .alf_prop_unique_ctx..name="", but was .alf_prop_unique_ctx.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_prop_unique_ctx.PRIMARY
Difference: expected primary key .alf_prop_value..name="", but was .alf_prop_value.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_prop_value.PRIMARY
Difference: expected primary key .alf_qname..name="", but was .alf_qname.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_qname.PRIMARY
Difference: expected primary key .alf_server..name="", but was .alf_server.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_server.PRIMARY
Difference: expected primary key .alf_store..name="", but was .alf_store.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_store.PRIMARY
Difference: expected primary key .alf_subscriptions..name="", but was .alf_subscriptions.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_subscriptions.PRIMARY
Difference: expected primary key .alf_tenant..name="", but was .alf_tenant.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_tenant.PRIMARY
Difference: expected primary key .alf_transaction..name="", but was .alf_transaction.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_transaction.PRIMARY
Difference: expected primary key .alf_usage_delta..name="", but was .alf_usage_delta.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .alf_usage_delta.PRIMARY
Difference: unexpected table found in database with path: .alf_bootstrap_lock

and the 50 problems

Difference: expected primary key .act_ge_bytearray..name="", but was .act_ge_bytearray.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_ge_bytearray.PRIMARY
Difference: expected primary key .act_ge_property..name="", but was .act_ge_property.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_ge_property.PRIMARY
Difference: expected primary key .act_hi_actinst..name="", but was .act_hi_actinst.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_hi_actinst.PRIMARY
Difference: expected primary key .act_hi_attachment..name="", but was .act_hi_attachment.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_hi_attachment.PRIMARY
Difference: expected primary key .act_hi_comment..name="", but was .act_hi_comment.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_hi_comment.PRIMARY
Difference: expected primary key .act_hi_detail..name="", but was .act_hi_detail.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_hi_detail.PRIMARY
Difference: expected primary key .act_hi_identitylink..name="", but was .act_hi_identitylink.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_hi_identitylink.PRIMARY
Difference: expected primary key .act_hi_procinst..name="", but was .act_hi_procinst.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_hi_procinst.PRIMARY
Difference: expected primary key .act_hi_taskinst..name="", but was .act_hi_taskinst.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_hi_taskinst.PRIMARY
Difference: expected primary key .act_hi_varinst..name="", but was .act_hi_varinst.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_hi_varinst.PRIMARY
Difference: expected primary key .act_id_group..name="", but was .act_id_group.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_id_group.PRIMARY
Difference: expected primary key .act_id_info..name="", but was .act_id_info.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_id_info.PRIMARY
Difference: expected primary key .act_id_membership..name="", but was .act_id_membership.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_id_membership.PRIMARY
Difference: expected primary key .act_id_user..name="", but was .act_id_user.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_id_user.PRIMARY
Difference: expected primary key .act_re_deployment..name="", but was .act_re_deployment.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_re_deployment.PRIMARY
Difference: expected primary key .act_re_model..name="", but was .act_re_model.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_re_model.PRIMARY
Difference: expected primary key .act_re_procdef..name="", but was .act_re_procdef.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_re_procdef.PRIMARY
Difference: expected primary key .act_ru_event_subscr..name="", but was .act_ru_event_subscr.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_ru_event_subscr.PRIMARY
Difference: expected primary key .act_ru_execution..name="", but was .act_ru_execution.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_ru_execution.PRIMARY
Difference: expected primary key .act_ru_identitylink..name="", but was .act_ru_identitylink.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_ru_identitylink.PRIMARY
Difference: expected primary key .act_ru_job..name="", but was .act_ru_job.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_ru_job.PRIMARY
Difference: expected primary key .act_ru_task..name="", but was .act_ru_task.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_ru_task.PRIMARY
Difference: expected primary key .act_ru_variable..name="", but was .act_ru_variable.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_ru_variable.PRIMARY
Difference: expected primary key .act_evt_log..name="", but was .act_evt_log.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_evt_log.PRIMARY
Difference: expected primary key .act_procdef_info..name="", but was .act_procdef_info.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_procdef_info.PRIMARY

Any help would be gratefully received,

Thanks

[kaynezhang]

Which mariadb driver are you using? it seems you are using a wrong mariadb driver

[James56]

Hi,

I followed the instructions here; 

https://github.com/keensoft/docker-alfresco/wiki/Using-MariaDB-instead-of-PostgreSQL-as-database

and am using the mariadb-java-client-2.2.3.jar as advised. If you can offer any more help, I'd be really grateful!

Thanks

James

medicalphysics@AlfrescoTest:~/alfresco-docker6.0x/docker-alfresco/alfresco$ cat Dockerfile
FROM alfresco/alfresco-content-repository-community:6.0.7-ga

# Fix to https://issues.alfresco.com/jira/browse/ALF-22013
ENV LC_ALL C.UTF-8 \
LANG C.UTF-8

ARG TOMCAT_DIR=/usr/local/tomcat

# Logs always under $TOMCAT/logs folder
RUN echo "cd $TOMCAT_DIR/logs" > $TOMCAT_DIR/bin/setenv.sh \
&& chmod +x $TOMCAT_DIR/bin/setenv.sh

# Install modules and addons
RUN mkdir -p $TOMCAT_DIR/amps
COPY modules/amps $TOMCAT_DIR/amps
COPY modules/jars $TOMCAT_DIR/webapps/alfresco/WEB-INF/lib

RUN java -jar $TOMCAT_DIR/alfresco-mmt/alfresco-mmt*.jar install \
$TOMCAT_DIR/amps $TOMCAT_DIR/webapps/alfresco -directory -nobackup -force

# Install AOS
RUN yum -y update && \
yum -y install wget && \
yum -y install unzip && \
yum clean all

RUN set -x \
&& mkdir /tmp/aos \
&& wget --no-check-certificate https://download.alfresco.com/cloudfront/release/community/201806-GA-build-00113/alfresco-aos-module... \
&& unzip alfresco-aos-module-distributionzip-1.2.0.zip -d /tmp/aos \
&& mv /tmp/aos/extension/* $TOMCAT_DIR/shared/classes/alfresco/extension \
&& mv /tmp/aos/alfresco-aos-module-1.2.0.amp amps \
&& mv /tmp/aos/aos-module-license.txt licenses \
&& mv /tmp/aos/_vti_bin.war $TOMCAT_DIR/webapps \
&& rm -rf /tmp/aos alfresco-aos-module-distributionzip-1.2.0.zip

COPY tomcat/lib $TOMCAT_DIR/libmedicalphysics@AlfrescoTest:~/alfresco-docker6.0x/docker-alfresco/alfresco$ docker exec -it a97acbcbd72f /bin/bash
bash: warning: setlocale: LC_ALL: cannot change locale (C.UTF-8 LANG C.UTF-8)
/bin/sh: warning: setlocale: LC_ALL: cannot change locale (C.UTF-8 LANG C.UTF-8)
[root@a97acbcbd72f tomcat]# find / -iname 'mariadb-java*'
/usr/local/tomcat/lib/mariadb-java-client-2.2.3.jar
[root@a97acbcbd72f tomcat]# exit

[kaynezhang]

Please try mariadb driver 2.3.0

[James56]

Hi,

Version 2.3 of mariadb-java-client failed in exactly the same way. I changed the file in the alfresco docker build directory, ran docker-compose build, docker compose up -d, and logged into the container using docker exec -t <docker id> /bin/bash to check the version. This was correct. Still getting the "DB lock failed on scehma bootstrap" which tries 23 times before giving up, with "A long running upgrade was found to be running on another node of the cluster"

The number of potential problems was the same, as with other versions. A lot of the problems are of the form;

difference: expected primary key .act_ru_job..name="", but was .act_ru_job.PRIMARY.name="PRIMARY"
Difference: missing index from database, expected at path: .act_ru_job.PRIMARY

[kaynezhang]

Please use mariadb 2.6.0  and  set useMysqlMetadata to true in your db connection url,like following

jdbc:mariadb://localhost:3306/foo?useMysqlMetadata=true

this option force the driver to return mysql  in DatabaseMetaData and make it to use mysql dialect.