topic Re: More than one Alfresco schema was found when querying the database metadata in Alfresco Forum

More than one Alfresco schema was found when querying the database metadata

cajova_houba — Wed, 15 Mar 2023 17:42:33 GMT

Hello,

I'm tryting to connect Alfresco Community 5.2.e to existing MySQL instance (the instance already has tables and data). However, Alfresco fails to start with following error:

org.alfresco.error.AlfrescoRuntimeException: 02150000 
More than one Alfresco schema was found when querying the database metadata.
   Limit the database user's permissions or set the 'hibernate.default_schema' property in 'custom-hibernate-dialect.properties'.

The mysql instance really contains multiple DBs, each of them used by other applications. However, name of the database Alfresco is supposed to connect to is given in alfresco-global.properties.

What I've tried so far:

add hibernate.default_schema to tomcat/shared/classes/alfresco-global.properties
add hibernate.default_schema to tomcat/shared/classes/custom-hibernate-dialect.properties
add hibernate.default_schema to tomcat/webapps/alfresco/WEB-INF/classes/custom-hibernate-dialect.properties
connect to empty DB

Attempts 1. to 3. resulted in same error. Attempt 4. worked fine, but I need to connect to existing DB with data.

My questions are:

has anyone any idea how to fix this?
why does Alfresco thinks there are multiple schemas, when being explicitly told to use one particular database in db.name and db.url properties?

Any help is appreciated, thank you very much.

Re: More than one Alfresco schema was found when querying the database metadata

ash_saxena10 — Fri, 17 Mar 2023 05:21:53 GMT

I faced the same issue while upgrading Alfresco 5.0 instance with MS SQL server . Well , I was able to resolve this issue by turning on the snapshot isolation mode by below command .

ALTER DATABASE <DBNAME> SET ALLOW_SNAPSHOT_ISOLATION ON;

Please check your MYSQL DB config as per the below Url :

https://docs.alfresco.com/content-services/5.2/config/databases/#configuring-the-mysql-database

( Ensure user must have specified permissions and might be there is some Tables name uppercase issue as per the below Link :

https://docs.alfresco.com/content-services/5.2/config/databases/#configuring-the-mysql-database

Meanwhile please help in sharing the mysql DB config in global.properties file and your schema Details please .

Re: More than one Alfresco schema was found when querying the database metadata

cajova_houba — Mon, 20 Mar 2023 15:48:13 GMT

Hello,

thanks for your reply. Here is the db config from alfresco-global.properties, sensitive data are omitted:

db.driver=com.mysql.cj.jdbc.Driver
db.username=<db_username>
db.password=<db_password>
db.name=<db_name>
db.url=jdbc:mysql://<db_server_host>/<db_name>?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
db.pool.max=275
db.pool.validate.query=SELECT 1
db.schema.update=true
hibernate.default_schema=<db_name>

Not sure if it helps, but Alfresco currently runs in read-only mode (server.allowWrite=false)

As for the schema details, the database is MySQL 5.6.20, it contains multiple schemas. User configured in alfresco-global.properties has access to two of them, one of them is <db_name> and it's the one I'm trying to connect to. The <db_name> already contains tables following tables filled with data:

+-----------------------------+
| Tables_in_<db_name>      |
+-----------------------------+
| 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_auth_status             |
| alf_authority               |
| alf_authority_alias         |
| 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             |
+-----------------------------+

All of the tables have lower-case names. When I tried to run Alfresco against empty DB, it created tables with upper-case names. Not sure if that's is related to my issue or not.

Database user <db_username> has all privileges for <db_name>:

GRANT ALL PRIVILEGES ON `<db_name>`.* TO '<db_username>'@'%'

Hope this helps.

Re: More than one Alfresco schema was found when querying the database metadata

ash_saxena10 — Tue, 21 Mar 2023 07:59:02 GMT

Hi ,

Below are my observations that you may check :

a.) Alfresco 5.2.e version trying to connect with compatible MYSQL database , more important correct version of mysql connector needs to be placed at alfresco/tomcat/lib location . Refer below link :

https://docs.alfresco.com/content-services/5.2/support/

b.) If your schema is at lower version and you are upgrading the schema to 5.2.e then you have to remove that read-only property and set the repository to write mode .

Hope this resolves your problem , if not post your findings .

Re: More than one Alfresco schema was found when querying the database metadata

cajova_houba — Wed, 22 Mar 2023 11:22:02 GMT

The problem was fixed by downgrading the mysql connector from " mysql-connector-j-8.0.32" to "mysql-connector-java-5.1.39". Thank you so much for your assistance!