-
Bug
-
Resolution: Fixed
-
Medium
-
3.13, 5.1.3, 5.2.10
-
3.13
-
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;
- details
-
JRASERVER-21972 Document the limitation upon the maximum number of projects which a JIRA instance can host.
-
- Closed
-
- duplicates
-
JRASERVER-24449 Ofbiz generates queries with large IN clauses
-
- Closed
-
- is duplicated by
-
JRASERVER-29600 JIRA with more than 1000 projects throws error ORA-01795
-
- Closed
-
- is related to
-
JSWSERVER-10778 JIRA Agile upgrade to 6.4.0.6 fails with SQLException ORA-24335
-
- Closed
-
-
JRASERVER-32840 Update 'Database limitations on number of projects' page to include details on Oracle limitation
- Closed
- relates to
-
JRASERVER-38790 Ofbiz generates queries with large IN clauses that result in SQL Server errors
-
- Closed
-
-
JRASERVER-29242 ChangeHistoryBatch.fetchAllChangeGroups() fails with large number of input issues
-
- Closed
-
-
JDEV-24806 Loading...
-
JDEV-31097 Loading...
- is cloned from
-
JDEV-24756 Loading...
- mentioned in
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Wiki Page Loading...
-
Wiki Page Loading...
-
Wiki Page Loading...
-
Wiki Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Page Loading...
-
Wiki Page Loading...