Uploaded image for project: 'Jira Data Center'
  1. Jira Data Center
  2. JRASERVER-38790

Ofbiz generates queries with large IN clauses that result in SQL Server errors

    XMLWordPrintable

Details

    Description

      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.

      Environment

      SQL Server 2008 SP3.

      Steps to Reproduce

      1. Go to JIRA with more than 2100 projects
      2. Go into the "User Browser"
      3. 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.

      Expected Results

      JIRA functions without error.

      Actual Results

      This will generate the following SQL (Oracle) error:

      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)

      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. 

      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.)

      This is similar to JRA-19317:Ofbiz generates queries with large IN clauses that result in ORA-01795 errors, which affects Oracle. However, it is fixed in JIRA 6.1.1

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. JRASERVER-39769 JIRA leaks DB connections

          • High - High priority issues
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. JRASERVER-43248 Audit Log export is empty when exporting more than 2000 records on MSSQL

          • Low - Low priority issues
          • Closed
          is related to

          Bug - A problem which impairs or prevents the functions of the product. JRASERVER-19317 Ofbiz generates queries with large IN clauses that result in ORA-01795 errors

          • Medium - Medium priority issues
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. JRASERVER-43556 ActiveObjects generated queries with large IN clauses that result in SQL Server errors

          • Medium - Medium priority issues
          • Gathering Impact

          AO-652 Loading...

          JDEV-33313 Loading...

          mentioned in

          Page Loading...

          Page Loading...

          was cloned as

          JDEV-31097 Loading...

          Activity

            People

              ohernandez@atlassian.com Oswaldo Hernandez (Inactive)
              cchan Chung Park Chan
              Votes:
              3 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: