-
Bug
-
Resolution: Unresolved
-
Medium (View bug fix roadmap)
-
None
-
7.2.9, 7.2.10
-
7.02
-
25
-
Severity 3 - Minor
-
1
-
Summary
If an Agile board has more than 1000 card colors, you will receive an ORA-01795 DB error when attempting to delete the board and deletion will fail.
Environment
Observed with Oracle 12C using 12.1.102 driver
Steps to Reproduce
- Configure JIRA to use an Oracle 12 database
- Create 1,000+ users
- Create an Agile Board
- Assign issues to each of the 1,000+ users.
- Set card coloring rules: Board > Configure > Card colors > Colors based on assignees
- Attempt to delete board
Expected Results
Board should be deleted without error.
Actual Results
Deletion fails, logging shows a DELETE SQL statement with more than 1,000 parameters for the IN clause:
/rest/greenhopper/1.0/rapidview/9501 [n.java.ao.sql] DELETE FROM "AO_60DB71_CARDCOLOR" WHERE "ID" IN (?,?,<snip>,?,?)
Resulting in stack:
/rest/greenhopper/1.0/rapidview/9501 [c.a.g.w.rapid.view.RapidViewResource] Unable to complete GreenHopper REST method 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, Real Application Clusters, Automatic Storage Management, 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
Workaround
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Manually delete card color entries in this board from the DB before removing board in UI:
DELETE FROM AO_60DB71_CARDCOLOR WHERE rapid_view_id = XXXX;
Where XXXX is the rapid board id as seen in the above stack trace, '9501' in this example.
Switching card coloring to a different type (Issue Types, Priorities, Queries, None) will not resolve the issue.
- relates to
-
-
- Gathering Impact
-
We came across this bug, but removing entries from DB is somewhat complicated for our team - we have Read-Only access.
Looking further into this, it seems that Deleting card colour entries from the UI is still working.
We used browser developer tools to fetch the deletion request from Network Tab and got cardcolor entries from the DB for that board.
Then we used Postman to replicate this Delete request and looped through cardcolor entries to delete all entries.
/rest/greenhopper/1.0/cardcolors/{{board_id}}/color/{{card_id}}?undefinedMight take a while, but at least an option for not making changes directly in DB
Jira DC 7.6.6