Error "ORA-01795: maximum number of expressions in a list is 1000" in SLA gadgets

Issue #421 resolved
Damien Baboulaz [NeoXam]
created an issue

SLA gadgets don't return any data and show instead the following message:

An internal server error occurred when requesting resource

The logs show the cause is that the number of elements is greater than 1,000:

2017-05-19 18:09:51,287 http-nio-8447-exec-1 ERROR Damien.Baboulaz 1089x34999x1 1c6uo3h 10.33.28.34,10.215.38.39 /rest/tts-api/1.0/piechart/generate [c.a.p.r.c.error.jersey.ThrowableExceptionMapper] Uncaught exception thrown by REST service: There was a SQL exception thrown by the Active Objects library:
    Database:
        - name:Oracle
        - version:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
        - minor version:1
        - major version:12
    Driver:
        - name:Oracle JDBC driver
        - version:12.1.0.1.0

    java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
    - name:Oracle
    - version:Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    - minor version:1
    - major version:12
Driver:
    - name:Oracle JDBC driver
    - version:12.1.0.1.0
java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000
    at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:136)
    at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.find(TenantAwareActiveObjects.java:291)
    ... 2 filtered
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
    at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
    at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy3224.find(Unknown Source)
    at com.tuncaysenturk.jira.plugins.timetosla.service.v.a(TtsDatabaseServiceImpl.java:1131)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChart.issuesCountFromDatabase(PieChart.java:239)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChart.generateChartInternal(PieChart.java:102)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChart.generateInlineChart(PieChart.java:84)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChartGeneratorImpl.generatePieChart(PieChartGeneratorImpl.java:34)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChartResource.createChart(PieChartResource.java:146)
    at com.tuncaysenturk.jira.plugins.timetosla.gadget.pie.slacounts.PieChartResource.getPieChart(PieChartResource.java:113)
    ... 3 filtered
    at java.lang.reflect.Method.invoke(Method.java:498)
    ... 19 filtered
    at com.atlassian.plugins.rest.module.RestDelegatingServletFilter$JerseyOsgiServletContainer.doFilter(RestDelegatingServletFilter.java:154)
    ... 1 filtered
    at com.atlassian.plugins.rest.module.RestDelegatingServletFilter.doFilter(RestDelegatingServletFilter.java:68)
    ... 86 filtered
    at com.atlassian.jira.security.JiraSecurityFilter.doFilter(JiraSecurityFilter.java:70)
    ... 16 filtered
    at com.atlassian.plugins.rest.module.servlet.RestSeraphFilter.doFilter(RestSeraphFilter.java:37)
    ... 73 filtered
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: ORA-01795: maximum number of expressions in a list is 1000

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:776)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:897)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1034)
    at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:4999)
    at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:11314)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3818)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3867)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1502)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
    at net.java.ao.EntityManager.find(EntityManager.java:671)
    at net.java.ao.EntityManager.find(EntityManager.java:621)
    at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:134)
    ... 235 more

Workaround: modify the filter so that fewer than 1,000 tickets are returned.

Comments (12)

  1. Damien Baboulaz [NeoXam] reporter

    Hello,

    The issue is resolved, my pie chart is displayed and the Oracle error does not occur anymore. I have a weird chart though, e.g. the 328 "breached" are shown as 1% of my 21,733 total tickets but the orange part of the chart looks like it's 90% or something. chart.png

    Anyway let's close this ticket for now. I'll look into this new issue and I'll open a new ticket if needed.

    Thanks.

  2. Tuncay Senturk repo owner

    You're right,

    %1 is according to total issues count which is 21733,
    Anyway, please raise another ticket for this problem and we will try to fix it ASAP

    Regards

  3. Log in to comment