Defect #2488
closedRecalculation of a large number of automatic roles on MS SQL Server ends in error
100%
Description
We tried to run ProcessAllAutomaticRoleByAttributeTaskExecutor for about 10,000 automatic roles in IdM which uses the MS SQL Server database (MS SQL 2017, standard edition, build 14.0.3281.6) (IdM runs on Windows Server 2016 (1607)).
The task failed with this error:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:279) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:144) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$ExposeRepositoryInvocationInterceptor.invoke(CrudMethodMetadataPostProcessor.java:364) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) at com.sun.proxy.$Proxy213.findAll(Unknown Source) at eu.bcvsolutions.idm.core.api.service.AbstractReadDtoService.findEntities(AbstractReadDtoService.java:392) at eu.bcvsolutions.idm.core.api.service.AbstractReadDtoService.find(AbstractReadDtoService.java:215) at eu.bcvsolutions.idm.core.api.service.AbstractReadDtoService$$FastClassBySpringCGLIB$$120a9283.invoke(<generated>) at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) at eu.bcvsolutions.idm.core.model.service.impl.DefaultIdmRoleTreeNodeService$$EnhancerBySpringCGLIB$$e20bd6c2.find(<generated>) at eu.bcvsolutions.idm.core.scheduler.task.impl.ProcessAutomaticRoleByTreeTaskExecutor.getItemsToProcess(ProcessAutomaticRoleByTreeTaskExecutor.java:206) at eu.bcvsolutions.idm.core.scheduler.api.service.AbstractSchedulableStatefulExecutor.executeProcess(AbstractSchedulableStatefulExecutor.java:201) at eu.bcvsolutions.idm.core.scheduler.api.service.AbstractSchedulableStatefulExecutor.process(AbstractSchedulableStatefulExecutor.java:78) at eu.bcvsolutions.idm.core.scheduler.api.service.AbstractSchedulableStatefulExecutor.process(AbstractSchedulableStatefulExecutor.java:62) at eu.bcvsolutions.idm.core.scheduler.api.service.AbstractLongRunningTaskExecutor.call(AbstractLongRunningTaskExecutor.java:212) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.springframework.security.concurrent.DelegatingSecurityContextRunnable.run(DelegatingSecurityContextRunnable.java:84) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) 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.extract(ResultSetReturnImpl.java:69) at org.hibernate.loader.Loader.getResultSet(Loader.java:2167) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1930) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1892) at org.hibernate.loader.Loader.doQuery(Loader.java:937) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:340) at org.hibernate.loader.Loader.doList(Loader.java:2689) at org.hibernate.loader.Loader.doList(Loader.java:2672) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506) at org.hibernate.loader.Loader.list(Loader.java:2501) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:504) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:395) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:220) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1508) at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1537) at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505) at org.hibernate.query.Query.getResultList(Query.java:132) at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:74) at org.springframework.data.jpa.repository.support.SimpleJpaRepository.readPage(SimpleJpaRepository.java:615) at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:430) at sun.reflect.GeneratedMethodAccessor384.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:359) at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200) at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:644) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:608) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ... 32 more Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1632) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:600) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7225) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3053) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:247) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:222) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:444) at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60) ... 68 more
Related issues
Updated by Tomáš Doischer over 4 years ago
Sorry, the task we used was ProcessAllAutomaticRoleByTreeTaskExecutor, not ProcessAllAutomaticRoleByAttributeTaskExecutor.
Updated by Radek Tomiška over 4 years ago
- Assignee changed from Ondřej Kopr to Radek Tomiška
I will try to add support to recalculate this high ammount of automatic roles by tree structure (even it is not good pattern at all :)).
Updated by Radek Tomiška over 4 years ago
- Status changed from New to Needs feedback
- Assignee changed from Radek Tomiška to Vít Švanda
- Target version set to 10.6.0
- % Done changed from 0 to 90
- Affected versions 10.4.0, 10.4.1, 10.4.2, 10.4.3, 10.5.0, 10.5.1 added
I've added finding automatic roles to process in limited batch.
Commit:
https://github.com/bcvsolutions/CzechIdMng/commit/81f9e162e88892af7c2a5ea66e86a396fb238885
Could you provide me a feedback?
Updated by Alena Peterová over 4 years ago
Hi @tomiskar, the ProcessAllAutomaticRoleByTreeTaskExecutor fails in our environment shortly after start, so the same change as in ProcessAutomaticRoleByTreeTaskExecutor needs to be done there?
Updated by Radek Tomiška over 4 years ago
ProcessAllAutomaticRoleByTreeTaskExecutor reuses ProcessAutomaticRoleByTreeTaskExecutor, so this improvement will help there too.
Updated by Radek Tomiška about 4 years ago
- Related to Task #2519: Filters: throw better exception, when more than database supported count of parameters is given in filter parameter added
Updated by Alena Peterová about 4 years ago
Would you please include this fix in the 10.4.8 (together with #2524)? We can consult it next week.
Updated by Radek Tomiška about 4 years ago
Updated by Vít Švanda about 4 years ago
- Status changed from Needs feedback to Resolved
- Assignee changed from Vít Švanda to Radek Tomiška
- % Done changed from 90 to 100
I did reivew and tested pageing in the automatic role recalculation. Works nice. LGTM
Updated by Radek Tomiška about 4 years ago
- Status changed from Resolved to Closed