com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID FROM (SELECT jiraissue.ID FROM jiraissue jiraissue INNER JOIN changegroup cg ON jiraissue.ID = cg.issueid WHERE (jiraissue.PROJECT IN (?, ?, ?) AND (cg.AUTHOR IN (?) ) ORDER BY cg.CREATED DESC) WHERE ROWNUM <= 10 (ORA-01795: maximum number of expressions in a list is 1000 )

Atlassian JIRA | Peter White [Atlassian] | 8 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 Oracle will not permit more than 1000 literals in an IN clause. It does, however, permit SELECT statements in the IN clause which can return an unlimited number of elements. This causes problem as OfBiz will attempt to query an Oracle DB with IN clauses that have >1k literals and will throw exceptions. h3. Steps to Reproduce 1. Go to a JIRA instance with more than 1000 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. h3. Expected Results JIRA functions without error. h3. Actual Results This will generate the following SQL (Oracle) error: {noformat} Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT DISTINCT PID FROM projectroleactor WHERE (PROJECTROLEID=? AND ROLETYPE=? AND ROLETYPEPARAMETER=?) AND (PID IN (?, ?, ?, ?,... ) ) (ORA-01795: maximum number of expressions in a list is 1000 {noformat} In SQL Server it will display a {{Prepared or callable statement has more than 2000 parameter markers}} error. 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 1k) and deleting some of the history records or restricting the access of the issue corrected the problem. This presents with the following exception: {noformat} 2013-04-10 08:25:22,407 StreamsCompletionService::thread-19910 ERROR cemerine 500x1935299x29 i2att9 3.185.141.134,3.239.244.206 /plugins/servlet/streams [atlassian.streams.internal.LocalActivityProvider] Error building feed com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID FROM (SELECT jiraissue.ID FROM jiraissue jiraissue INNER JOIN changegroup cg ON jiraissue.ID = cg.issueid WHERE (jiraissue.PROJECT IN (?, ?, ?) AND (cg.AUTHOR IN (?) ) ORDER BY cg.CREATED DESC) WHERE ROWNUM <= 10 (ORA-01795: maximum number of expressions in a list is 1000 ) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:489) at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIdsByUsers(DefaultChangeHistoryManager.java:390) ... Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID FROM (SELECT jiraissue.ID FROM jiraissue jiraissue INNER JOIN changegroup cg ON jiraissue.ID = cg.issueid WHERE (jiraissue.PROJECT IN (?, ?, ?) ) AND (cg.AUTHOR IN (?) ) ORDER BY cg.CREATED DESC) WHERE ROWNUM <= 10 (ORA-01795: maximum number of expressions in a list is 1000 ) at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:605) at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:1059) {noformat} And can be verified with the below SQL: {code:SQL} SELECT count(*) AS ttl_records, p.pkey, ji.issuenum FROM changeitem ci JOIN changegroup cg ON cg.id = ci.groupid JOIN jiraissue ji ON ji.id = cg.issueid JOIN project p ON ji.project = p.id GROUP BY p.pkey, ji.issuenum HAVING count(*) >= 1000 ORDER BY ttl_records DESC; {code}

    Atlassian JIRA | 8 years ago | Peter White [Atlassian]
    com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID FROM (SELECT jiraissue.ID FROM jiraissue jiraissue INNER JOIN changegroup cg ON jiraissue.ID = cg.issueid WHERE (jiraissue.PROJECT IN (?, ?, ?) AND (cg.AUTHOR IN (?) ) ORDER BY cg.CREATED DESC) WHERE ROWNUM <= 10 (ORA-01795: maximum number of expressions in a list is 1000 )
  2. 0

    h3. Summary Oracle will not permit more than 1000 literals in an IN clause. It does, however, permit SELECT statements in the IN clause which can return an unlimited number of elements. This causes problem as OfBiz will attempt to query an Oracle DB with IN clauses that have >1k literals and will throw exceptions. h3. Steps to Reproduce 1. Go to a JIRA instance with more than 1000 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. h3. Expected Results JIRA functions without error. h3. Actual Results This will generate the following SQL (Oracle) error: {noformat} Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT DISTINCT PID FROM projectroleactor WHERE (PROJECTROLEID=? AND ROLETYPE=? AND ROLETYPEPARAMETER=?) AND (PID IN (?, ?, ?, ?,... ) ) (ORA-01795: maximum number of expressions in a list is 1000 {noformat} In SQL Server it will display a {{Prepared or callable statement has more than 2000 parameter markers}} error. 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 1k) and deleting some of the history records or restricting the access of the issue corrected the problem. This presents with the following exception: {noformat} 2013-04-10 08:25:22,407 StreamsCompletionService::thread-19910 ERROR cemerine 500x1935299x29 i2att9 3.185.141.134,3.239.244.206 /plugins/servlet/streams [atlassian.streams.internal.LocalActivityProvider] Error building feed com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID FROM (SELECT jiraissue.ID FROM jiraissue jiraissue INNER JOIN changegroup cg ON jiraissue.ID = cg.issueid WHERE (jiraissue.PROJECT IN (?, ?, ?) AND (cg.AUTHOR IN (?) ) ORDER BY cg.CREATED DESC) WHERE ROWNUM <= 10 (ORA-01795: maximum number of expressions in a list is 1000 ) at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:489) at com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIdsByUsers(DefaultChangeHistoryManager.java:390) ... Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID FROM (SELECT jiraissue.ID FROM jiraissue jiraissue INNER JOIN changegroup cg ON jiraissue.ID = cg.issueid WHERE (jiraissue.PROJECT IN (?, ?, ?) ) AND (cg.AUTHOR IN (?) ) ORDER BY cg.CREATED DESC) WHERE ROWNUM <= 10 (ORA-01795: maximum number of expressions in a list is 1000 ) at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:605) at org.ofbiz.core.entity.GenericDAO.selectListIteratorByCondition(GenericDAO.java:1059) {noformat} And can be verified with the below SQL: {code:SQL} SELECT count(*) AS ttl_records, p.pkey, ji.issuenum FROM changeitem ci JOIN changegroup cg ON cg.id = ci.groupid JOIN jiraissue ji ON ji.id = cg.issueid JOIN project p ON ji.project = p.id GROUP BY p.pkey, ji.issuenum HAVING count(*) >= 1000 ORDER BY ttl_records DESC; {code}

    Atlassian JIRA | 8 years ago | Peter White [Atlassian]
    com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID FROM (SELECT jiraissue.ID FROM jiraissue jiraissue INNER JOIN changegroup cg ON jiraissue.ID = cg.issueid WHERE (jiraissue.PROJECT IN (?, ?, ?) AND (cg.AUTHOR IN (?) ) ORDER BY cg.CREATED DESC) WHERE ROWNUM <= 10 (ORA-01795: maximum number of expressions in a list is 1000 )

    Root Cause Analysis

    1. com.atlassian.jira.exception.DataAccessException

      org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID FROM (SELECT jiraissue.ID FROM jiraissue jiraissue INNER JOIN changegroup cg ON jiraissue.ID = cg.issueid WHERE (jiraissue.PROJECT IN (?, ?, ?) AND (cg.AUTHOR IN (?) ) ORDER BY cg.CREATED DESC) WHERE ROWNUM <= 10 (ORA-01795: maximum number of expressions in a list is 1000 )

      at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition()
    2. com.atlassian.jira
      DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIdsByUsers
      1. com.atlassian.jira.ofbiz.DefaultOfBizDelegator.findListIteratorByCondition(DefaultOfBizDelegator.java:489)
      2. com.atlassian.jira.issue.changehistory.DefaultChangeHistoryManager.findMostRecentlyUpdatedIssueIdsByUsers(DefaultChangeHistoryManager.java:390)
      2 frames