com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: Ofbiz generates queries with large IN clauses that result in while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?... ?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.)

Atlassian JIRA | Chung Park Chan | 3 years ago
tip
Click on the to mark the solution that helps you, Samebug will learn from it.
As a community member, you’ll be rewarded for you help.
  1. 0

    h3. Summary SQL Server jTDS driver will not permit more than 2000 literals in an IN clause and this causes problem as OfBiz will attempt to query the SQL Server with IN clauses that have >2k literals and will throw exceptions. h3. Environment SQL Server 2008 SP3. h3. Steps to Reproduce # Go to JIRA with more than 2100 projects # Go into the "User Browser" # For any user, click on the "Project Roles" link This can also be reproduced anywhere within JIRA where OfBiz queries the database in this manner, for example: * Accessing the Activity Stream. * Viewing a large change history on an issue. h3. Expected Results JIRA functions without error. h3. Actual Results This will generate the following SQL (Oracle) error: {noformat} 2014-06-16 06:06:29,718 StreamsCompletionService::thread-1 ERROR admin 366x32x5 oardx6 10.60.2.93 /plugins/servlet/streams [atlassian.streams.internal.LocalActivityProvider] Exception building feed com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: Ofbiz generates queries with large IN clauses that result in while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?... ?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:517) at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findListIteratorByCondition(WrappingOfBizDelegator.java:343) at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIds(DefaultChangeHistoryManager.java:407) at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIds(DefaultChangeHistoryManager.java:358) at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.doFindUserHistory(DefaultChangeHistoryManager.java:324) at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findUserHistory(DefaultChangeHistoryManager.java:229) <+3> at java.lang.reflect.Method.invoke(Unknown Source) at com.atlassian.plugin.osgi.hostcomponents.impl.DefaultComponentRegistrar$ContextClassLoaderSettingInvocationHandler.invoke(DefaultComponentRegistrar.java:129) at com.sun.proxy.$Proxy158.findUserHistory(Unknown Source) <+3> at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at com.sun.proxy.$Proxy2165.findUserHistory(Unknown Source) at com.atlassian.streams.jira.search.UserHistory.find(UserHistory.java:61) at com.atlassian.streams.jira.search.IssueFinder.find(IssueFinder.java:49) at com.atlassian.streams.jira.JiraStreamsActivityProvider$1.call(JiraStreamsActivityProvider.java:100) at com.atlassian.streams.jira.JiraStreamsActivityProvider$1.call(JiraStreamsActivityProvider.java:94) at com.atlassian.streams.internal.LocalActivityProvider$FeedFetcher.doInTransaction(LocalActivityProvider.java:205) at com.atlassian.streams.internal.LocalActivityProvider$FeedFetcher.doInTransaction(LocalActivityProvider.java:186) at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:25) at com.atlassian.jira.DefaultHostContextAccessor.doInTransaction(DefaultHostContextAccessor.java:34) <+2> at java.lang.reflect.Method.invoke(Unknown Source) at com.atlassian.plugin.osgi.hostcomponents.impl.DefaultComponentRegistrar$ContextClassLoaderSettingInvocationHandler.invoke(DefaultComponentRegistrar.java:129) at com.sun.proxy.$Proxy357.doInTransaction(Unknown Source) <+2> at java.lang.reflect.Method.invoke(Unknown Source) at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:154) at com.sun.proxy.$Proxy357.doInTransaction(Unknown Source) at com.atlassian.sal.core.transaction.HostContextTransactionTemplate.execute(HostContextTransactionTemplate.java:21) <+2> at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at com.sun.proxy.$Proxy2263.execute(Unknown Source) at com.atlassian.streams.internal.LocalActivityProvider$1$1.get(LocalActivityProvider.java:125) at com.atlassian.streams.internal.LocalActivityProvider$1$1.get(LocalActivityProvider.java:122) at com.atlassian.streams.jira.JiraSessionManager.withSession(JiraSessionManager.java:19) at com.atlassian.streams.common.SwitchingSessionManager.withSession(SwitchingSessionManager.java:32) <+3> at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at com.sun.proxy.$Proxy2276.withSession(Unknown Source) at com.atlassian.streams.internal.LocalActivityProvider$1.call(LocalActivityProvider.java:121) at com.atlassian.streams.internal.LocalActivityProvider$1.call(LocalActivityProvider.java:117) at com.atlassian.streams.internal.FeedBuilder$ToFeedCallable$1.call(FeedBuilder.java:109) at com.atlassian.streams.internal.FeedBuilder$ToFeedCallable$1.call(FeedBuilder.java:104) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at com.atlassian.util.concurrent.LimitedExecutor$Runner.run(LimitedExecutor.java:93) at com.atlassian.sal.core.executor.ThreadLocalDelegateRunnable.run(ThreadLocalDelegateRunnable.java:38) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?,... ?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.) at org.ofbiz.core.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:534) at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:844) at org.ofbiz.core.entity.GenericHelperDAO.findListIteratorByCondition(GenericHelperDAO.java:194) at org.ofbiz.core.entity.GenericDelegator.findListIteratorByCondition(GenericDelegator.java:1235) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:512) {noformat} h3. Notes It seems that this can also use up connections in the DBCP and not release them back into the pool, subsequently multiple interactions with the database that experience this error can cause the DBCP to time out and JIRA will cease to function properly (as it cannot obtain connections from the DB). This is not currently verified, however has been anecdotally viewed on customer instances and was corrected by working around the SQL that was causing these errors. This was caused by issues with large history records (over 2k) and deleting some of the history records or restricting the access of the issue corrected the problem. {noformat} 2014-06-16 06:06:29,718 StreamsCompletionService::thread-1 ERROR admin 366x32x5 oardx6 10.60.2.93 /plugins/servlet/streams [atlassian.streams.internal.LocalActivityProvider] Exception building feed com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: Ofbiz generates queries with large IN clauses that result in while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?, ..?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.) ... Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?, ?,..., ?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.) {noformat} {panel:bgColor=#E7F4FA} This is similar to [*JRA-19317*:Ofbiz generates queries with large IN clauses that result in ORA-01795 errors|https://jira.atlassian.com/browse/JRA-19317], which affects Oracle. However, it is fixed in JIRA 6.1.1 {panel}

    Atlassian JIRA | 3 years ago | Chung Park Chan [Atlassian]
    com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: Ofbiz generates queries with large IN clauses that result in while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?... ?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.)
  2. 0

    h3. Summary SQL Server jTDS driver will not permit more than 2000 literals in an IN clause and this causes problem as OfBiz will attempt to query the SQL Server with IN clauses that have >2k literals and will throw exceptions. h3. Environment SQL Server 2008 SP3. h3. Steps to Reproduce # Go to JIRA with more than 2100 projects # Go into the "User Browser" # For any user, click on the "Project Roles" link This can also be reproduced anywhere within JIRA where OfBiz queries the database in this manner, for example: * Accessing the Activity Stream. * Viewing a large change history on an issue. h3. Expected Results JIRA functions without error. h3. Actual Results This will generate the following SQL (Oracle) error: {noformat} 2014-06-16 06:06:29,718 StreamsCompletionService::thread-1 ERROR admin 366x32x5 oardx6 10.60.2.93 /plugins/servlet/streams [atlassian.streams.internal.LocalActivityProvider] Exception building feed com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: Ofbiz generates queries with large IN clauses that result in while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?... ?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:517) at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findListIteratorByCondition(WrappingOfBizDelegator.java:343) at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIds(DefaultChangeHistoryManager.java:407) at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIds(DefaultChangeHistoryManager.java:358) at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.doFindUserHistory(DefaultChangeHistoryManager.java:324) at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findUserHistory(DefaultChangeHistoryManager.java:229) <+3> at java.lang.reflect.Method.invoke(Unknown Source) at com.atlassian.plugin.osgi.hostcomponents.impl.DefaultComponentRegistrar$ContextClassLoaderSettingInvocationHandler.invoke(DefaultComponentRegistrar.java:129) at com.sun.proxy.$Proxy158.findUserHistory(Unknown Source) <+3> at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at com.sun.proxy.$Proxy2165.findUserHistory(Unknown Source) at com.atlassian.streams.jira.search.UserHistory.find(UserHistory.java:61) at com.atlassian.streams.jira.search.IssueFinder.find(IssueFinder.java:49) at com.atlassian.streams.jira.JiraStreamsActivityProvider$1.call(JiraStreamsActivityProvider.java:100) at com.atlassian.streams.jira.JiraStreamsActivityProvider$1.call(JiraStreamsActivityProvider.java:94) at com.atlassian.streams.internal.LocalActivityProvider$FeedFetcher.doInTransaction(LocalActivityProvider.java:205) at com.atlassian.streams.internal.LocalActivityProvider$FeedFetcher.doInTransaction(LocalActivityProvider.java:186) at com.atlassian.sal.core.transaction.HostContextTransactionTemplate$1.doInTransaction(HostContextTransactionTemplate.java:25) at com.atlassian.jira.DefaultHostContextAccessor.doInTransaction(DefaultHostContextAccessor.java:34) <+2> at java.lang.reflect.Method.invoke(Unknown Source) at com.atlassian.plugin.osgi.hostcomponents.impl.DefaultComponentRegistrar$ContextClassLoaderSettingInvocationHandler.invoke(DefaultComponentRegistrar.java:129) at com.sun.proxy.$Proxy357.doInTransaction(Unknown Source) <+2> at java.lang.reflect.Method.invoke(Unknown Source) at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:154) at com.sun.proxy.$Proxy357.doInTransaction(Unknown Source) at com.atlassian.sal.core.transaction.HostContextTransactionTemplate.execute(HostContextTransactionTemplate.java:21) <+2> at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at com.sun.proxy.$Proxy2263.execute(Unknown Source) at com.atlassian.streams.internal.LocalActivityProvider$1$1.get(LocalActivityProvider.java:125) at com.atlassian.streams.internal.LocalActivityProvider$1$1.get(LocalActivityProvider.java:122) at com.atlassian.streams.jira.JiraSessionManager.withSession(JiraSessionManager.java:19) at com.atlassian.streams.common.SwitchingSessionManager.withSession(SwitchingSessionManager.java:32) <+3> at java.lang.reflect.Method.invoke(Unknown Source) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58) at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:56) at org.springframework.osgi.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:39) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.osgi.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:59) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131) at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) at com.sun.proxy.$Proxy2276.withSession(Unknown Source) at com.atlassian.streams.internal.LocalActivityProvider$1.call(LocalActivityProvider.java:121) at com.atlassian.streams.internal.LocalActivityProvider$1.call(LocalActivityProvider.java:117) at com.atlassian.streams.internal.FeedBuilder$ToFeedCallable$1.call(FeedBuilder.java:109) at com.atlassian.streams.internal.FeedBuilder$ToFeedCallable$1.call(FeedBuilder.java:104) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source) at java.util.concurrent.FutureTask.run(Unknown Source) at com.atlassian.util.concurrent.LimitedExecutor$Runner.run(LimitedExecutor.java:93) at com.atlassian.sal.core.executor.ThreadLocalDelegateRunnable.run(ThreadLocalDelegateRunnable.java:38) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?,... ?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.) at org.ofbiz.core.entity.jdbc.SQLProcessor.prepareStatement(SQLProcessor.java:534) at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:844) at org.ofbiz.core.entity.GenericHelperDAO.findListIteratorByCondition(GenericHelperDAO.java:194) at org.ofbiz.core.entity.GenericDelegator.findListIteratorByCondition(GenericDelegator.java:1235) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:512) {noformat} h3. Notes It seems that this can also use up connections in the DBCP and not release them back into the pool, subsequently multiple interactions with the database that experience this error can cause the DBCP to time out and JIRA will cease to function properly (as it cannot obtain connections from the DB). This is not currently verified, however has been anecdotally viewed on customer instances and was corrected by working around the SQL that was causing these errors. This was caused by issues with large history records (over 2k) and deleting some of the history records or restricting the access of the issue corrected the problem. {noformat} 2014-06-16 06:06:29,718 StreamsCompletionService::thread-1 ERROR admin 366x32x5 oardx6 10.60.2.93 /plugins/servlet/streams [atlassian.streams.internal.LocalActivityProvider] Exception building feed com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: Ofbiz generates queries with large IN clauses that result in while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?, ..?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.) ... Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?, ?,..., ?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.) {noformat} {panel:bgColor=#E7F4FA} This is similar to [*JRA-19317*:Ofbiz generates queries with large IN clauses that result in ORA-01795 errors|https://jira.atlassian.com/browse/JRA-19317], which affects Oracle. However, it is fixed in JIRA 6.1.1 {panel}

    Atlassian JIRA | 3 years ago | Chung Park Chan
    com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: Ofbiz generates queries with large IN clauses that result in while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?... ?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.)

    Root Cause Analysis

    1. com.atlassian.jira.exception.DataAccessException

      org.ofbiz.core.entity.GenericDataSourceException: Ofbiz generates queries with large IN clauses that result in while executing the following:SELECT sq_.ID FROM (SELECT ID, ROW_NUMBER() OVER (ORDER BY UPDATED DESC) rnum FROM dbo.jiraissue WHERE PROJECT IN (?, ?... ?) ) sq_ WHERE sq_.rnum <= 5 (Prepared or callable statement has more than 2000 parameter markers.)

      at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition()
    2. com.atlassian.jira
      DefaultChangeHistoryManager.doFindUserHistory
      1. com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:517)
      2. com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findListIteratorByCondition(WrappingOfBizDelegator.java:343)
      3. com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIds(DefaultChangeHistoryManager.java:407)
      4. com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIds(DefaultChangeHistoryManager.java:358)
      5. com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.doFindUserHistory(DefaultChangeHistoryManager.java:324)
      5 frames