com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, groupid, FIELDTYPE, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING FROM dbo.changeitem WHERE (groupid IN (?, ?, ?, ?, ?, ...) (Prepared or callable statement has more than 2000 parameter markers.)

Atlassian JIRA | Theo Tzidamis [Atlassian] | 4 years ago
  1. 0

    h3. Symptoms JIRA will generate an error when trying to fetch a large number of change groups. This can happen when: # Fetching change history for a large number of issues. # Fetching change history for issues with a lot of change groups. The following can be found within the {{atlassian-jira.log}}: {noformat:title=SQL Server} com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, groupid, FIELDTYPE, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING FROM dbo.changeitem WHERE (groupid IN (?, ?, ?, ?, ?, ...) (Prepared or callable statement has more than 2000 parameter markers.) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findByAnd(DefaultOfBizDelegator.java:120) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch.fetchAllChangeItems(ChangeHistoryBatch.java:160) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch.access$400(ChangeHistoryBatch.java:39) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch$BatchingChangeHistory.getChangeItems(ChangeHistoryBatch.java:272) {noformat} {noformat:title=Oracle} com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, groupid, FIELDTYPE, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING FROM changeitem WHERE (groupid IN (?, ?, ?, ?, ?, ...) (ORA-01795: maximum number of expressions in a list is 1000) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findByAnd(DefaultOfBizDelegator.java:120) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch.fetchAllChangeItems(ChangeHistoryBatch.java:160) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch.access$400(ChangeHistoryBatch.java:39) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch$BatchingChangeHistory.getChangeItems(ChangeHistoryBatch.java:272) {noformat} h3. Cause The {{ChangeHistoryBatch}} can build SELECT statements with excessively large IN clauses in JIRA 5.1, leading to errors in Activity Streams, GreenHopper, and other plugins that use the {{ChangeHistoryManager}}. h3. Workaround There is no current workaround at this stage.

    Atlassian JIRA | 4 years ago | Theo Tzidamis [Atlassian]
    com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, groupid, FIELDTYPE, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING FROM dbo.changeitem WHERE (groupid IN (?, ?, ?, ?, ?, ...) (Prepared or callable statement has more than 2000 parameter markers.)
  2. 0

    h3. Symptoms JIRA will generate an error when trying to fetch a large number of change groups. This can happen when: # Fetching change history for a large number of issues. # Fetching change history for issues with a lot of change groups. The following can be found within the {{atlassian-jira.log}}: {noformat:title=SQL Server} com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, groupid, FIELDTYPE, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING FROM dbo.changeitem WHERE (groupid IN (?, ?, ?, ?, ?, ...) (Prepared or callable statement has more than 2000 parameter markers.) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findByAnd(DefaultOfBizDelegator.java:120) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch.fetchAllChangeItems(ChangeHistoryBatch.java:160) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch.access$400(ChangeHistoryBatch.java:39) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch$BatchingChangeHistory.getChangeItems(ChangeHistoryBatch.java:272) {noformat} {noformat:title=Oracle} com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, groupid, FIELDTYPE, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING FROM changeitem WHERE (groupid IN (?, ?, ?, ?, ?, ...) (ORA-01795: maximum number of expressions in a list is 1000) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findByAnd(DefaultOfBizDelegator.java:120) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch.fetchAllChangeItems(ChangeHistoryBatch.java:160) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch.access$400(ChangeHistoryBatch.java:39) at com.atlassian.jira.issue.changehistory.ChangeHistoryBatch$BatchingChangeHistory.getChangeItems(ChangeHistoryBatch.java:272) {noformat} h3. Cause The {{ChangeHistoryBatch}} can build SELECT statements with excessively large IN clauses in JIRA 5.1, leading to errors in Activity Streams, GreenHopper, and other plugins that use the {{ChangeHistoryManager}}. h3. Workaround There is no current workaround at this stage.

    Atlassian JIRA | 4 years ago | Theo Tzidamis [Atlassian]
    com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, groupid, FIELDTYPE, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING FROM dbo.changeitem WHERE (groupid IN (?, ?, ?, ?, ?, ...) (Prepared or callable statement has more than 2000 parameter markers.)
  3. 0

    When exporting the audit log, the export file is empty. The prompt will give you a number of entries that will be exported (truncated to the 100,000 newest events), yet when you open the export file, it is empty. I have attached the audit log zip file. *Edit with root cause:* In tomcat logs there are following exceptions: {code} SEVERE: Servlet.service() for servlet default threw exception com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, LOG_ID, OBJECT_TYPE, OBJECT_ID, OBJECT_NAME, OBJECT_PARENT_ID, OBJECT_PARENT_NAME FROM jiraschema.audit_item WHERE (LOG_ID IN [...] (Prepared or callable statement has more than 2000 parameter markers.) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:534) at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findListIteratorByCondition(WrappingOfBizDelegator.java:349) at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.consumeWith(SelectQueryImpl.java:234) at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.visitWith(SelectQueryImpl.java:253) at com.atlassian.jira.auditing.AuditingStoreImpl.getItems(AuditingStoreImpl.java:293) at com.atlassian.jira.auditing.AuditingStoreImpl.getAuditRecords(AuditingStoreImpl.java:249) at com.atlassian.jira.auditing.AuditingStoreImpl.getRecords(AuditingStoreImpl.java:195) at com.atlassian.jira.auditing.AuditingManagerImpl.getRecords(AuditingManagerImpl.java:108) at com.atlassian.jira.auditing.AuditingManagerImpl.getRecords(AuditingManagerImpl.java:82) at sun.reflect.GeneratedMethodAccessor2802.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 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.$Proxy296.getRecords(Unknown Source) at sun.reflect.GeneratedMethodAccessor2802.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) [...] {code} This is reproducible only with MSSQL when exporting more than 2000 records, because MSSQL allows only for 2000 placeholder values in prepared statements. Also it affects Audit Log only in JIRA 6.3.5 and above since from that point there is an optimization introduced that results in prepare statements with long lists of place holders.

    Atlassian JIRA | 2 years ago | Chris Solgat
    com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, LOG_ID, OBJECT_TYPE, OBJECT_ID, OBJECT_NAME, OBJECT_PARENT_ID, OBJECT_PARENT_NAME FROM jiraschema.audit_item WHERE (LOG_ID IN [...] (Prepared or callable statement has more than 2000 parameter markers.)
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    When exporting the audit log, the export file is empty. The prompt will give you a number of entries that will be exported (truncated to the 100,000 newest events), yet when you open the export file, it is empty. I have attached the audit log zip file. *Edit with root cause:* In tomcat logs there are following exceptions: {code} SEVERE: Servlet.service() for servlet default threw exception com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, LOG_ID, OBJECT_TYPE, OBJECT_ID, OBJECT_NAME, OBJECT_PARENT_ID, OBJECT_PARENT_NAME FROM jiraschema.audit_item WHERE (LOG_ID IN [...] (Prepared or callable statement has more than 2000 parameter markers.) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:534) at com.atlassian.jira.ofbiz.WrappingOfBizDelegator.findListIteratorByCondition(WrappingOfBizDelegator.java:349) at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.consumeWith(SelectQueryImpl.java:234) at com.atlassian.jira.entity.SelectQueryImpl$ExecutionContextImpl.visitWith(SelectQueryImpl.java:253) at com.atlassian.jira.auditing.AuditingStoreImpl.getItems(AuditingStoreImpl.java:293) at com.atlassian.jira.auditing.AuditingStoreImpl.getAuditRecords(AuditingStoreImpl.java:249) at com.atlassian.jira.auditing.AuditingStoreImpl.getRecords(AuditingStoreImpl.java:195) at com.atlassian.jira.auditing.AuditingManagerImpl.getRecords(AuditingManagerImpl.java:108) at com.atlassian.jira.auditing.AuditingManagerImpl.getRecords(AuditingManagerImpl.java:82) at sun.reflect.GeneratedMethodAccessor2802.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 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.$Proxy296.getRecords(Unknown Source) at sun.reflect.GeneratedMethodAccessor2802.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) [...] {code} This is reproducible only with MSSQL when exporting more than 2000 records, because MSSQL allows only for 2000 placeholder values in prepared statements. Also it affects Audit Log only in JIRA 6.3.5 and above since from that point there is an optimization introduced that results in prepare statements with long lists of place holders.

    Atlassian JIRA | 2 years ago | Chris Solgat
    com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, LOG_ID, OBJECT_TYPE, OBJECT_ID, OBJECT_NAME, OBJECT_PARENT_ID, OBJECT_PARENT_NAME FROM jiraschema.audit_item WHERE (LOG_ID IN [...] (Prepared or callable statement has more than 2000 parameter markers.)
  6. 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 | 2 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.)

    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. com.atlassian.jira.exception.DataAccessException

      org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, groupid, FIELDTYPE, FIELD, OLDVALUE, OLDSTRING, NEWVALUE, NEWSTRING FROM dbo.changeitem WHERE (groupid IN (?, ?, ?, ?, ?, ...) (Prepared or callable statement has more than 2000 parameter markers.)

      at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findByAnd()
    2. com.atlassian.jira
      ChangeHistoryBatch$BatchingChangeHistory.getChangeItems
      1. com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findByAnd(DefaultOfBizDelegator.java:120)
      2. com.atlassian.jira.issue.changehistory.ChangeHistoryBatch.fetchAllChangeItems(ChangeHistoryBatch.java:160)
      3. com.atlassian.jira.issue.changehistory.ChangeHistoryBatch.access$400(ChangeHistoryBatch.java:39)
      4. com.atlassian.jira.issue.changehistory.ChangeHistoryBatch$BatchingChangeHistory.getChangeItems(ChangeHistoryBatch.java:272)
      4 frames