Re: [keycloak-user] Exception on realm import

Hello, I tried to use the import feature to import preconfigured client & roles from dev environment to production, but I get an exception during the import. I got to the realm -> import, select file, realm to import, check import client and check import client roles, set to overwrite. I get an error "*Error!* javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement" Any workaround / suggestion? It seems related to a client role named "authenticated" but not sure it's not just failing on first client role of file. Here is server stacktrace: 2017-01-26 15:29:29,718 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-31) SQL Error: 23505, SQLState: 23505 2017-01-26 15:29:29,718 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-31) Unique index or primary key violation: "UK_J3RWUVD56ONTGSUHOGM184WW2-2_INDEX_A ON PUBLIC.KEYCLOAK_ROLE(NAME, CLIENT_REALM_CONSTRAINT) VALUES ( /* key:280 */ null, '36da85fb-076c-4403-aafc-b2226cf69bcb', null, null, 'authenticated', null, null, null, null)"; SQL statement: insert into KEYCLOAK_ROLE (CLIENT, CLIENT_REALM_CONSTRAINT, CLIENT_ROLE, DESCRIPTION, NAME, REALM, REALM_ID, SCOPE_PARAM_REQUIRED, ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-173] 2017-01-26 15:29:29,719 INFO [org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl] (default task-31) HHH000010: On release of batch it still contained JDBC statements 2017-01-26 15:29:29,719 ERROR [org.keycloak.services] (default task-31) KC-SERVICES0038: Error importing roles: org.keycloak.models.ModelDuplicateException: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement at org.keycloak.connections.jpa.PersistenceExceptionConverter.convert( PersistenceExceptionConverter.java:57) at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke( PersistenceExceptionConverter.java:51) at com.sun.proxy.$Proxy61.flush(Unknown Source) at org.keycloak.models.jpa.JpaRealmProvider.addClientRole( JpaRealmProvider.java:231) at org.keycloak.models.cache.infinispan.RealmCacheSession.addClientRole( RealmCacheSession.java:703) at org.keycloak.models.jpa.ClientAdapter.addRole(ClientAdapter.java:636) at org.keycloak.models.utils.RepresentationToModel.importRoles( RepresentationToModel.java:437) at org.keycloak.partialimport.RolesPartialImport.doImport( RolesPartialImport.java:98) at org.keycloak.partialimport.PartialImportManager.saveResources( PartialImportManager.java:77) at org.keycloak.services.resources.admin.RealmAdminResource.partialImport( RealmAdminResource.java:855) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java: 62) at sun.reflect.DelegatingMethodAccessorImpl.invoke( DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.jboss.resteasy.core.MethodInjectorImpl.invoke( MethodInjectorImpl.java:139) at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget( ResourceMethodInvoker.java:295) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke( ResourceMethodInvoker.java:249) at org.jboss.resteasy.core.ResourceLocatorInvoker.invokeOnTargetObject( ResourceLocatorInvoker.java:138) at org.jboss.resteasy.core.ResourceLocatorInvoker.invoke( ResourceLocatorInvoker.java:107) at org.jboss.resteasy.core.ResourceLocatorInvoker.invokeOnTargetObject( ResourceLocatorInvoker.java:133) at org.jboss.resteasy.core.ResourceLocatorInvoker.invoke( ResourceLocatorInvoker.java:101) at org.jboss.resteasy.core.SynchronousDispatcher.invoke( SynchronousDispatcher.java:395) at org.jboss.resteasy.core.SynchronousDispatcher.invoke( SynchronousDispatcher.java:202) at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher. service(ServletContainerDispatcher.java:221) at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service( HttpServletDispatcher.java:56) at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service( HttpServletDispatcher.java:51) at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) at io.undertow.servlet.handlers.ServletHandler.handleRequest( ServletHandler.java:85) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl. doFilter(FilterHandler.java:129) at org.keycloak.services.filters.KeycloakSessionServletFilter.doFilter( KeycloakSessionServletFilter.java:90) at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60) at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl. doFilter(FilterHandler.java:131) at io.undertow.servlet.handlers.FilterHandler.handleRequest( FilterHandler.java:84) at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler. handleRequest(ServletSecurityRoleHandler.java:62) at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest( ServletDispatchingHandler.java:36) at org.wildfly.extension.undertow.security.SecurityContextAssociationHand ler.handleRequest(SecurityContextAssociationHandler.java:78) at io.undertow.server.handlers.PredicateHandler.handleRequest( PredicateHandler.java:43) at io.undertow.servlet.handlers.security.SSLInformationAssociationHandl er.handleRequest(SSLInformationAssociationHandler.java:131) at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandl er.handleRequest(ServletAuthenticationCallHandler.java:57) at io.undertow.server.handlers.PredicateHandler.handleRequest( PredicateHandler.java:43) at io.undertow.security.handlers.AbstractConfidentialityHandler .handleRequest(AbstractConfidentialityHandler.java:46) at io.undertow.servlet.handlers.security.ServletConfidentialityConstrai ntHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64) at io.undertow.security.handlers.AuthenticationMechanismsHandle r.handleRequest(AuthenticationMechanismsHandler.java:60) at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHand ler.handleRequest(CachedAuthenticatedSessionHandler.java:77) at io.undertow.security.handlers.NotificationReceiverHandler.handleRequest( NotificationReceiverHandler.java:50) at io.undertow.security.handlers.AbstractSecurityContextAssocia tionHandler.handleRequest(AbstractSecurityContextAssocia tionHandler.java:43) at io.undertow.server.handlers.PredicateHandler.handleRequest( PredicateHandler.java:43) at org.wildfly.extension.undertow.security.jacc.JACCContextIdHandler. handleRequest(JACCContextIdHandler.java:61) at io.undertow.server.handlers.PredicateHandler.handleRequest( PredicateHandler.java:43) at io.undertow.server.handlers.PredicateHandler.handleRequest( PredicateHandler.java:43) at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest( ServletInitialHandler.java:284) at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest( ServletInitialHandler.java:263) at io.undertow.servlet.handlers.ServletInitialHandler.access$ 000(ServletInitialHandler.java:81) at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest( ServletInitialHandler.java:174) at io.undertow.server.Connectors.executeRootHandler(Connectors.java:202) at io.undertow.server.HttpServerExchange$1.run( HttpServerExchange.java:793) at java.util.concurrent.ThreadPoolExecutor.runWorker( ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run( ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert( AbstractEntityManagerImpl.java:1692) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert( AbstractEntityManagerImpl.java:1602) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert( AbstractEntityManagerImpl.java:1608) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.flush( AbstractEntityManagerImpl.java:1303) at sun.reflect.GeneratedMethodAccessor342.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke( DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.keycloak.connections.jpa.PersistenceExceptionConverter.invoke( PersistenceExceptionConverter.java:49) ... 57 more Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement at org.hibernate.exception.internal.SQLStateConversionDelegate.convert( SQLStateConversionDelegate.java:112) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert( StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert( SqlExceptionHelper.java:109) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert( SqlExceptionHelper.java:95) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate( ResultSetReturnImpl.java:207) at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch( NonBatchingBatch.java:45) at org.hibernate.persister.entity.AbstractEntityPersister.insert( AbstractEntityPersister.java:2886) at org.hibernate.persister.entity.AbstractEntityPersister.insert( AbstractEntityPersister.java:3386) at org.hibernate.action.internal.EntityInsertAction.execute( EntityInsertAction.java:89) at org.hibernate.engine.spi.ActionQueue.executeActions( ActionQueue.java:560) at org.hibernate.engine.spi.ActionQueue.executeActions( ActionQueue.java:434) at org.hibernate.event.internal.AbstractFlushingEventListener. performExecutions(AbstractFlushingEventListener.java:337) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush( DefaultFlushEventListener.java:39) at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1282) at org.hibernate.jpa.spi.AbstractEntityManagerImpl.flush( AbstractEntityManagerImpl.java:1300) ... 61 more Caused by: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "UK_J3RWUVD56ONTGSUHOGM184WW2-2_INDEX_A ON PUBLIC.KEYCLOAK_ROLE(NAME, CLIENT_REALM_CONSTRAINT) VALUES ( /* key:280 */ null, '36da85fb-076c-4403-aafc-b2226cf69bcb', null, null, 'authenticated', null, null, null, null)"; SQL statement: insert into KEYCLOAK_ROLE (CLIENT, CLIENT_REALM_CONSTRAINT, CLIENT_ROLE, DESCRIPTION, NAME, REALM, REALM_ID, SCOPE_PARAM_REQUIRED, ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?) [23505-173] at org.h2.message.DbException.getJdbcSQLException(DbException.java:331) at org.h2.message.DbException.get(DbException.java:171) at org.h2.message.DbException.get(DbException.java:148) at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:101) at org.h2.index.PageBtree.find(PageBtree.java:121) at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:148) at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:101) at org.h2.index.PageBtreeNode.addRowTry(PageBtreeNode.java:201) at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:95) at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:86) at org.h2.table.RegularTable.addRow(RegularTable.java:125) at org.h2.command.dml.Insert.insertRows(Insert.java:127) at org.h2.command.dml.Insert.update(Insert.java:86) at org.h2.command.CommandContainer.update(CommandContainer.java:79) at org.h2.command.Command.executeUpdate(Command.java:235) at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal( JdbcPreparedStatement.java:154) at org.h2.jdbc.JdbcPreparedStatement.executeUpdate( JdbcPreparedStatement.java:140) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate( WrappedPreparedStatement.java:537) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate( ResultSetReturnImpl.java:204) ... 71 more -- <http://www.trimble.com/> David Delbecq Software engineer, Transport & Logistics Geldenaaksebaan 329, 1st floor | 3001 Leuven +32 16 391 121 <+32%2016%20391%20121> Direct david.delbecq(a)trimbletl.com <http://www.trimbletl.com/> _______________________________________________ keycloak-user mailing list keycloak-user(a)lists.jboss.org https://lists.jboss.org/mailman/listinfo/keycloak-user