Deleting a board with more than 2100 card colors fails with SQL exception

Issue Summary

When trying to delete a board with more than 2100 card colors will fail throwing the below SQL exception.

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:356)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
	at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
	at net.java.ao.EntityManager.delete(EntityManager.java:462)
	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.delete(EntityManagedActiveObjects.java:108)

The issue is caused by the SQL statement prepared by the Jira application and passed to the JDBC driver with a number of parameters higher than its limit of 2100.

A board can be configured in many different ways and one of them is customizing the board's card colors.
The card colors for a board can be organized by issue types, priorities, assignees, or JQL queries as specified in Customizing cards.

If the board's card colors are organized by assignee, a default color will be allocated to each user who is or has been an assignee and there is not limit for it. Hence, when having a board with a large number of issues (at least 2101), each of those issues having a different assignee and the board configured with Card color strategy assignee the board will have the same number of card colors as assignees.

The detailed issue above is considered a bug as the SQL statement issued by the application when trying to delete the card colors for the respective board can be done in a more efficient way (avoiding to pass a high number of parameters to the JDBC driver.

Steps to Reproduce

1. Install a vanilla instance of Jira Software with a SQL Server database.
   • This was re-validated on a JSW DC 9.6.0 instance with SQL Server 2017.
2. Create more than 2,100 new unique users.

   JIRA_BASE_URL=http://127.0.0.1:8080
   JIRA_ADMIN_USRNAME=admin001
   JIRA_ADMIN_PWD=admin001
   
   USERNAME_PREFIX=testuser
   
   for i in $(seq 1 2500); do
     new_username=${USERNAME_PREFIX}${i}
     new_password=${USERNAME_PREFIX}${i}
     new_email="${USERNAME_PREFIX}${i}@user.com"
     new_displayname=${USERNAME_PREFIX}${i}
   
     curl -v -k -L -XPOST \
      -H 'Content-Type: application/json' \
      -u ${JIRA_ADMIN_USRNAME}:${JIRA_ADMIN_PWD} \
      ${JIRA_BASE_URL}'/rest/api/latest/user' \
      -d '{"name": "'${new_username}'","password": "'${new_password}'","emailAddress": "'${new_email}'","displayName": "'${new_displayname}'","applicationKeys": ["jira-software"]}'
   done
   

3. Create a sample Kanban project.
4. Create more than 2,100 new issues with a unique assignee for each issue.

   JIRA_BASE_URL=http://127.0.0.1:8080
   JIRA_ADMIN_USERNAME=admin001
   JIRA_ADMIN_PASSWORD=admin001
   JIRA_PROJECT_KEY=KAN
   NUM_ISSUES_PER_PROJECT=2200
   ASSIGNEE_USERNAME_PREFIX=testuser
   
   for i in $(seq 1 ${NUM_ISSUES_PER_PROJECT}); do
     ASSIGNEE_USERNAME=${ASSIGNEE_USERNAME_PREFIX}${i}
     curl -v -u ${JIRA_ADMIN_USERNAME}:${JIRA_ADMIN_PASSWORD} -X POST \
          -H "Content-Type: application/json" \
          ${JIRA_BASE_URL}'/rest/api/2/issue/' \
          -d '{"fields": {"project":{"key": "'${JIRA_PROJECT_KEY}'"},"summary": "Mussum Ipsum, cacilds vidis litro abertis","description": "Mé faiz elementum girarzis, nisi eros vermeio.In elementis mé pra quem é amistosis quis leo. Paisis, filhis, espiritis santis.Nec orci ornare consequat. Praesent lacinia ultrices consectetur. Sed non ipsum felis.","assignee": {"name": "'${ASSIGNEE_USERNAME}'"},"issuetype": {"name": "Bug"}}}'
   done
   

5. Create a new Kanban board from the existing project.
6. Go to board configuration, select Card colors and choose colors based on Assignees.
   • There might be a timeout error while the board is configured with this option.
7. Give it some time for Jira to create the color configuration on the board – it may take several minutes to complete.
   • You may monitor it through the following REST API call or the following SQL query.

     <Jira-Base-URL>/rest/greenhopper/1.0/cardcolors/<board-rapidview-id>/strategy/assignee
     

     select RAPID_VIEW_ID, count(*)
     from dbo.AO_60DB71_CARDCOLOR
     group by RAPID_VIEW_ID;
     

8. Try to delete the board from Board > Delete.

Expected Results

To be able to delete a board regardless of the number of card colors or other configuration.

Actual Results

The board deletion fails with message on the UI similar to the below.

There was a SQL exception thrown by the Active Objects library: Database: - name:Microsoft SQL Server - version:14.00.3460 - minor version:0 - major version:14 Driver: - name:Microsoft JDBC Driver 9.2 for SQL Server - version:9.2.1.0 com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

There would be an entry in the Jira log similar to the below:

2019-11-12 14:37:36,205 http-nio-8080-exec-15 url:/rest/greenhopp....0/rapidview/10997 username:[SANITIZED] ERROR [SANITIZED] 877x181168x1 1myb450 [IP-SANITIZED],[IP-SANITIZED] /rest/greenhopper/1.0/rapidview/10997 [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:Microsoft SQL Server
    	- version:12.00.6205
    	- minor version:0
    	- major version:12
    Driver:
    	- name:Microsoft JDBC Driver 6.2 for SQL Server
    	- version:6.2.1.0
    
    com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:Microsoft SQL Server
	- version:12.00.6205
	- minor version:0
	- major version:12
Driver:
	- name:Microsoft JDBC Driver 6.2 for SQL Server
	- version:6.2.1.0
com.microsoft.sqlserver.jdbc.SQLServerException: The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

Workaround

The workaround below requires data manipulation directly in the database.
Make sure to validate it in a lower environment before moving to production.

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.

1. Configure board card color strategy to none using the REST API.

   JIRA_BASE_URL=http://127.0.0.1:8080
   JIRA_ADMIN_USERNAME=admin
   JIRA_ADMIN_PASSWORD=admin
   BOARD_RAPIDVIEW_ID=123456
   
   curl -v -k -L -X PUT \
     -u ${JIRA_ADMIN_USERNAME}:${JIRA_ADMIN_PASSWORD} \
     ${JIRA_BASE_URL}'/rest/greenhopper/1.0/cardcolors/'${BOARD_RAPIDVIEW_ID}'/strategy' \
     -H 'Accept: application/json' \
     -H 'Content-Type: application/json' \
     --data-raw '{"id":'${BOARD_RAPIDVIEW_ID}',"cardColorStrategyId":"none"}'
   

2. Delete all card colors related to the board directly from the database.

   delete from dbo.AO_60DB71_CARDCOLOR where RAPID_VIEW_ID=123456;
   

3. Delete the board using the REST API.

   JIRA_BASE_URL=http://127.0.0.1:8080
   JIRA_ADMIN_USERNAME=admin
   JIRA_ADMIN_PASSWORD=admin
   BOARD_RAPIDVIEW_ID=123456
   
   curl -v -k -L -X DELETE \
     -u ${JIRA_ADMIN_USERNAME}:${JIRA_ADMIN_PASSWORD} \
     ${JIRA_BASE_URL}'/rest/greenhopper/1.0/rapidview/'${BOARD_RAPIDVIEW_ID} \
     -H 'Accept: application/json' \
     -H 'Content-Type: application/json'
   

4. Confirm the board was deleted trying to access it from the browser.

   <Jira-Base-URL>/secure/RapidBoard.jspa?rapidView=123456
   

Change the Base URL and the admin credentials when needed.
The RapidViewID can be gathered from the browser of from the error message in the application log.