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

[cajova_houba]

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:

1. add hibernate.default_schema to tomcat/shared/classes/alfresco-global.properties
2. add hibernate.default_schema to tomcat/shared/classes/custom-hibernate-dialect.properties
3. add hibernate.default_schema to tomcat/webapps/alfresco/WEB-INF/classes/custom-hibernate-dialect.properties
4. 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.

[ash_saxena10]

Hi

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 .

[cajova_houba]

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.

[ash_saxena10 · Accepted solution]

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 .

[cajova_houba]

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!