Uploaded image for project: 'RH-SSO'
  1. RH-SSO
  2. RHSSO-1739

Add Primary Key Constraint into RESOURCE_URIS table

XMLWordPrintable

    • Icon: Epic Epic
    • Resolution: Done
    • Icon: Major Major
    • Archive - 21'
    • RH-SSO-7.3.0.GA
    • Server
    • None

      It appears that a PRIMARY KEY constraint is missing on the "RESOURCE_URIS" table schema, and which table has been added since Keycloak 4.2.0.

      Cf.> https://github.com/keycloak/keycloak/blob/master/model/jpa/src/main/resources/META-INF/jpa-changelog-authz-4.2.0.Final.xml

      <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.2.xsd">
    <changeSet author="mhajas@redhat.com" id="authz-4.2.0.Final">
        <createTable tableName="RESOURCE_URIS">
            <column name="RESOURCE_ID" type="VARCHAR(36)">
                <constraints nullable="false"/>
            </column>
            <column name="VALUE" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <addForeignKeyConstraint baseColumnNames="RESOURCE_ID" baseTableName="RESOURCE_URIS" constraintName="FK_RESOURCE_SERVER_URIS" referencedColumnNames="ID" referencedTableName="RESOURCE_SERVER_RESOURCE"/>

        <customChange class="org.keycloak.connections.jpa.updater.liquibase.custom.AuthzResourceUseMoreURIs"/>

        <dropColumn columnName="URI" tableName="RESOURCE_SERVER_RESOURCE"/>
    </changeSet>
</databaseChangeLog>

      As per RHSSO-1060 / KEYCLOAK-4928, it looks like that Primary Key Constraints are needed on all tables of the database.

      Not having a PK on this "RESOURCE_URIS" table can lead to following ERROR which has been reported by a customer running with RH-SSO 7.3.0.GA (see support case #02346751 ) :

      2019-03-22 11:26:42.772 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-28) SQL Error: 0, SQLState: 55000
2019-03-22 11:26:42.773 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-28) ERROR: Cannot run UPDATE or DELETE on table resource_uris because it does not have a PRIMARY KEY.
  Hint: Add a PRIMARY KEY to the table
2019-03-22 11:26:42.773 INFO  [org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl] (default task-28) HHH000010: On release of batch it still contained JDBC statements
2019-03-22 11:26:42.773 ERROR [org.keycloak.services.error.KeycloakErrorHandler] (default task-28) Uncaught server error: java.lang.RuntimeException: Error synchronizing authorization data.
	at org.keycloak.authorization.store.AuthorizationStoreFactory.lambda$registerSynchronizationListeners$1(AuthorizationStoreFactory.java:62)
	at org.keycloak.services.DefaultKeycloakSessionFactory.publish(DefaultKeycloakSessionFactory.java:69) [keycloak-services-4.8.3.Final-redhat-00001.jar:4.8.3.Final-redhat-00001]
	at org.keycloak.models.jpa.JpaRealmProvider.removeClient(JpaRealmProvider.java:572)
	at org.keycloak.models.jpa.JpaRealmProvider.removeRealm(JpaRealmProvider.java:153)
	at org.keycloak.models.cache.infinispan.RealmCacheSession.removeRealm(RealmCacheSession.java:486)
	at org.keycloak.services.managers.RealmManager.removeRealm(RealmManager.java:248) [keycloak-services-4.8.3.Final-redhat-00001.jar:4.8.3.Final-redhat-00001]
	at org.keycloak.services.resources.admin.RealmAdminResource.deleteRealm(RealmAdminResource.java:453) [keycloak-services-4.8.3.Final-redhat-00001.jar:4.8.3.Final-redhat-00001]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_162]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_162]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_162]
	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_162]
	at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:140) [resteasy-jaxrs-3.6.1.SP2-redhat-00001.jar:3.6.1.SP2-redhat-00001]
	...
	...
Caused by: org.keycloak.models.ModelException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
	at org.keycloak.connections.jpa.PersistenceExceptionConverter.convert(PersistenceExceptionConverter.java:61)
	at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke(PersistenceExceptionConverter.java:51)
	at com.sun.proxy.$Proxy92.flush(Unknown Source)
	at org.keycloak.authorization.jpa.store.JPAResourceServerStore.delete(JPAResourceServerStore.java:117)
	at org.keycloak.models.cache.infinispan.authorization.StoreFactoryCacheSession$ResourceServerCache.delete(StoreFactoryCacheSession.java:443)
	at org.keycloak.authorization.store.syncronization.ClientApplicationSynchronizer.removeFromClientPolicies(ClientApplicationSynchronizer.java:56)
	at org.keycloak.authorization.store.syncronization.ClientApplicationSynchronizer.synchronize(ClientApplicationSynchronizer.java:47)
	at org.keycloak.authorization.store.syncronization.ClientApplicationSynchronizer.synchronize(ClientApplicationSynchronizer.java:40)
	at org.keycloak.authorization.store.AuthorizationStoreFactory.lambda$null$0(AuthorizationStoreFactory.java:58)
	at java.util.HashMap.forEach(HashMap.java:1289) [rt.jar:1.8.0_162]
	at org.keycloak.authorization.store.AuthorizationStoreFactory.lambda$registerSynchronizationListeners$1(AuthorizationStoreFactory.java:56)
	... 77 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1460)
	at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1440)
	at sun.reflect.GeneratedMethodAccessor589.invoke(Unknown Source) [:1.8.0_162]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_162]
	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_162]
	at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke(PersistenceExceptionConverter.java:49)
	... 86 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:178)
	at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
	at org.hibernate.persister.collection.AbstractCollectionPersister.remove(AbstractCollectionPersister.java:1230)
	at org.hibernate.action.internal.CollectionRemoveAction.execute(CollectionRemoveAction.java:96)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:604)
	at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:478)
	at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:356)
	at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:39)
	at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1454)
	... 91 more
Caused by: org.postgresql.util.PSQLException: ERROR: Cannot run UPDATE or DELETE on table resource_uris because it does not have a PRIMARY KEY.
  Hint: Add a PRIMARY KEY to the table
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:136)
	at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:537)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
	... 99 more

              Unassigned Unassigned
              rhn-support-igueye Issa Gueye
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: