On 15 Mar 2017, at 11:22, Stefan Schlesinger <sts(a)ono.at&gt; wrote: Hello Folks! I tried to setup a Keycloak HA cluster with Percona XtraDB/Galera as HA database backend and it looks like its not currently supported, at least by the database schema Keycloak uses and the default Galera settings. Galera requires or recommends (performance) all table schemas to be defined with a primary key field and when I tried to add a role to a group I got the following error: ERROR [io.undertow.request] (default task-14) UT005023: Exception handling request to /auth/admin/realms/vault/groups/c2a04652-a322-1111-18ea-b2145bab2222/role-mappings/realm: org.jboss.resteasy.spi.UnhandledException: org.keycloak.models.ModelException: org.hibernate.exception.GenericJDBCException: could not execute statement ... Caused by: org.keycloak.models.ModelException: org.hibernate.exception.GenericJDBCException: could not execute statement ... Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement ... Caused by: java.sql.SQLException: Percona-XtraDB-Cluster prohibits use of DML command on a table (keycloak.ADMIN_EVENT_ENTITY) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER Looking through the database schema the following tables don’t have a primary key defined: ADMIN_EVENT_ENTITY COMPOSITE_ROLE* CREDENTIAL_ATTRIBUTE DATABASECHANGELOG FED_CREDENTIAL_ATTRIBUTE REALM_ENABLED_EVENT_TYPES* REALM_EVENTS_LISTENERS* REALM_SUPPORTED_LOCALES* REDIRECT_URIS* WEB_ORIGINS* Tables marked with an asterisk don’t even have an ID field, the rest of the tables actually got an ID field (with a UUID 'primary key'), which I think could be easily defined as primary key, and could easily be added. Looking at the Percona documentation[1][2], the limitation to only support tables with primary keys was liftet in more recent versions with the introduction of wsrep_certify_nonPK. However, it's still generally a best practice to have explicit PKs. If you don't define a PK, Galera will use an implicit hidden 6-byte PK for Innodb tables, taking up space that you can't use for querying. Innodb is very much optimized towards PK lookups. Also I’d need to set pxc_strict_mode from ENFORCING to PERMISSIVE, but that might have other side effects, as it relaxes other validations as well. Any experiences? Also, would it be possible to add primary keys in a bugfix version? Best, Stefan. [1] - https://www.percona.com/doc/percona-xtradb-cluster/5.7/features/pxc-stric... [2] - https://www.percona.com/doc/percona-xtradb-cluster/5.7/wsrep-system-index...

Any thoughts? Shall I rather create a bug for this issue? Best, Stefan. > On 15 Mar 2017, at 11:22, Stefan Schlesinger <sts(a)ono.at&gt; wrote: > > Hello Folks! > > I tried to setup a Keycloak HA cluster with Percona XtraDB/Galera as HA database backend and it looks like its not currently supported, at least by the database schema Keycloak uses and the default Galera settings. Galera requires or recommends (performance) all table schemas to be defined with a primary key field and when I tried to add a role to a group I got the following error: > > ERROR [io.undertow.request] (default task-14) UT005023: Exception handling request to /auth/admin/realms/vault/groups/c2a04652-a322-1111-18ea-b2145bab2222/role-mappings/realm: org.jboss.resteasy.spi.UnhandledException: org.keycloak.models.ModelException: org.hibernate.exception.GenericJDBCException: could not execute statement > ... > Caused by: org.keycloak.models.ModelException: org.hibernate.exception.GenericJDBCException: could not execute statement > ... > Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement > ... > Caused by: java.sql.SQLException: Percona-XtraDB-Cluster prohibits use of DML command on a table (keycloak.ADMIN_EVENT_ENTITY) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER > > Looking through the database schema the following tables don’t have a primary key defined: > > ADMIN_EVENT_ENTITY > COMPOSITE_ROLE* > CREDENTIAL_ATTRIBUTE > DATABASECHANGELOG > FED_CREDENTIAL_ATTRIBUTE > REALM_ENABLED_EVENT_TYPES* > REALM_EVENTS_LISTENERS* > REALM_SUPPORTED_LOCALES* > REDIRECT_URIS* > WEB_ORIGINS* > > Tables marked with an asterisk don’t even have an ID field, the rest of the tables actually got an ID field (with a UUID 'primary key'), which I think could be easily defined as primary key, and could easily be added. > > Looking at the Percona documentation[1][2], the limitation to only support tables with primary keys was liftet in more recent versions with the introduction of wsrep_certify_nonPK. However, it's still generally a best practice to have explicit PKs. If you don't define a PK, Galera will use an implicit hidden 6-byte PK for Innodb tables, taking up space that you can't use for querying. Innodb is very much optimized towards PK lookups. > > Also I’d need to set pxc_strict_mode from ENFORCING to PERMISSIVE, but that might have other side effects, as it relaxes other validations as well. Any experiences? > > Also, would it be possible to add primary keys in a bugfix version? > > Best, > > Stefan. > > [1] - https://www.percona.com/doc/percona-xtradb-cluster/5.7/features/pxc-stric... > [2] - https://www.percona.com/doc/percona-xtradb-cluster/5.7/wsrep-system-index... _______________________________________________ keycloak-dev mailing list keycloak-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-dev

On Mar 23, 2017, at 2:27 PM, Bill Burke <bburke(a)redhat.com&gt; wrote: These are join tables. Don't most RDBMS schemas have join tables? I don't see us putting effort into this. Community will have to do it On 3/23/17 11:39 AM, Stefan Schlesinger wrote: > Any thoughts? Shall I rather create a bug for this issue? > > Best, Stefan. > > >> On 15 Mar 2017, at 11:22, Stefan Schlesinger <sts(a)ono.at&gt; wrote: >> >> Hello Folks! >> >> I tried to setup a Keycloak HA cluster with Percona XtraDB/Galera as HA database backend and it looks like its not currently supported, at least by the database schema Keycloak uses and the default Galera settings. Galera requires or recommends (performance) all table schemas to be defined with a primary key field and when I tried to add a role to a group I got the following error: >> >> ERROR [io.undertow.request] (default task-14) UT005023: Exception handling request to /auth/admin/realms/vault/groups/c2a04652-a322-1111-18ea-b2145bab2222/role-mappings/realm: org.jboss.resteasy.spi.UnhandledException: org.keycloak.models.ModelException: org.hibernate.exception.GenericJDBCException: could not execute statement >> ... >> Caused by: org.keycloak.models.ModelException: org.hibernate.exception.GenericJDBCException: could not execute statement >> ... >> Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement >> ... >> Caused by: java.sql.SQLException: Percona-XtraDB-Cluster prohibits use of DML command on a table (keycloak.ADMIN_EVENT_ENTITY) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER >> >> Looking through the database schema the following tables don’t have a primary key defined: >> >> ADMIN_EVENT_ENTITY >> COMPOSITE_ROLE* >> CREDENTIAL_ATTRIBUTE >> DATABASECHANGELOG >> FED_CREDENTIAL_ATTRIBUTE >> REALM_ENABLED_EVENT_TYPES* >> REALM_EVENTS_LISTENERS* >> REALM_SUPPORTED_LOCALES* >> REDIRECT_URIS* >> WEB_ORIGINS* >> >> Tables marked with an asterisk don’t even have an ID field, the rest of the tables actually got an ID field (with a UUID 'primary key'), which I think could be easily defined as primary key, and could easily be added. >> >> Looking at the Percona documentation[1][2], the limitation to only support tables with primary keys was liftet in more recent versions with the introduction of wsrep_certify_nonPK. However, it's still generally a best practice to have explicit PKs. If you don't define a PK, Galera will use an implicit hidden 6-byte PK for Innodb tables, taking up space that you can't use for querying. Innodb is very much optimized towards PK lookups. >> >> Also I’d need to set pxc_strict_mode from ENFORCING to PERMISSIVE, but that might have other side effects, as it relaxes other validations as well. Any experiences? >> >> Also, would it be possible to add primary keys in a bugfix version? >> >> Best, >> >> Stefan. >> >> [1] - https://www.percona.com/doc/percona-xtradb-cluster/5.7/features/pxc-stric... >> [2] - https://www.percona.com/doc/percona-xtradb-cluster/5.7/wsrep-system-index... > > _______________________________________________ > keycloak-dev mailing list > keycloak-dev(a)lists.jboss.org > https://lists.jboss.org/mailman/listinfo/keycloak-dev _______________________________________________ keycloak-dev mailing list keycloak-dev(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-dev