Support MariaDB Galera cluster

[fchiacchiaretta]

Hi, I set up an installation of sysPass (BTW, great software!) and found that some tables in the database do not have a primary key. As per MariaDB galera documentation - known limitation:

All tables should have a primary key (multi-column primary keys are supported). DELETE operations are unsupported on tables without a primary key. Also, rows in tables without a primary key may appear in a different order on different nodes.

As far as I can see, tables without a PK are

• accFavorites
• accGroups
• accTags
• accUsers
• config
• usrToGroups

Would it be possible to change the database schema to support galera replication?

Best regards, Federico Chiacchiaretta

[fchiacchiaretta]

Hi @nuxsmin as a case study I can point out this Jira issue from Keycloak.

I had the same problem using Keycloak with Galera (I was using Percona cluster at that time, now switched to MariaDB Galera cluster), and followed that discussion about supporting Galera replication.

Hope it helps :)

[LeDistordu]

+1

[nuxsmin]

Hi @fchiacchiaretta , I know that those tables don't have a PK and that is so because a performance tweak, since some of them are only used to hold n:m relations and if a PK is built an index will need to be maintained, so every update or delete action (there are some of these operations during normal account actions) will cause an index update thus a performance penalty.

I'll need to analyze the performance impact that a PK would issue over these tables.

Regards

[fchiacchiaretta]

Hi @nuxsmin Thanks for your answer, I understand the issue now. Let me know if I can help with testing/benchmarking, I'd be glad to help.

Regards

[emersonarredondo]

Hi @nuxsmin @fchiacchiaretta I want to perform a zero installation with mariadb/galera but I have the same error

2022-01-05 15:56:27] syspass.EXCEPTION: logger {"message":"SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails

bitnami/mariadb-galera:10.6.5-debian-10-r35 Sypass:3.2

some problem mysql-operator 8.0

how did they solve

Greetings