Details
-
Bug
-
Resolution: Fixed
-
Medium
-
3.13, 5.1.3, 5.2.10
-
3.13
-
Description
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.
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.
Expected Results
JIRA functions without error.
Actual Results
This will generate the following SQL (Oracle) error:
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
In SQL Server it will display a Prepared or callable statement has more than 2000 parameter markers error.
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:
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)
And can be verified with the below 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;
Attachments
Issue Links
- details
-
-
- Closed
-
- duplicates
-
-
- Closed
-
- is duplicated by
-
-
- Closed
-
- is related to
-
-
- Closed
-
-
JRASERVER-32840 Update 'Database limitations on number of projects' page to include details on Oracle limitation
- Closed
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
JDEV-24806 Loading...
-
- is cloned from
-
- mentioned in
-
-
-
-
-
-
-
-
-
-
-
-
-
-
