Introduction

Migrating an existing Jira environment to the cloud can be a complex task with its unique set of challenges. In this blog post, we will discuss a specific problem encountered during such a migration: dealing with numerous Jira issues that had null statuses within the database. These issues couldn’t be viewed through the browser, and even the API couldn’t provide a solution. To overcome this hurdle, we had to resort to database-level operations, executing queries and updates to rectify the issue statuses.

The Challenge

During the migration process, we discovered that there were several issues with null statuses in the Jira database. These issues posed a significant challenge as they couldn’t be accessed via the Jira user interface, and attempts to fix them using the API were futile. One such issue was AGILE-78, which had a null status. The error message in the pre-migration checks from JCMA (Jira Cloud Migration Assistant) pointed to the problem:

2023-09-27 15:53:57.907643 ERROR AGILE project-export We couldn't export Issue AGILE-78. Reason: java.lang.NullPointerException. [JCMA 000]

The error message clearly stated that the export of issue AGILE-78 failed due to a java.lang.NullPointerException. This meant that the issue was encountering an error because it had a null reference or value associated with its status.

Moreover, it’s important to note that this issue couldn’t be restored from the archived issues. This added an additional layer of complexity to the situation, as we couldn’t simply retrieve it from the archives.

The Solution

To resolve this issue, I decided to take matters into our own hands by directly manipulating the database. We executed SQL queries and updates to assign appropriate statuses to the problematic issues. Here’s how we did it:

Step 1: Identifying the Problematic Issues

We began by identifying the issues that needed fixing. To do this, we ran the following SQL query:

SELECT Concat(p.pkey, '-', Cast(i.issuenum AS CHAR)) AS issue_key,
       t.pname                                       AS issue_type,
       s.pname                                       AS status_name
FROM   jiraissue i
       LEFT JOIN issuestatus s
              ON i.issuestatus = s.id
       LEFT JOIN project p
              ON i.project = p.id
       LEFT JOIN issuetype t
              ON i.issuetype = t.id
WHERE  s.id IS not NULL

This query helped us identify the issues with null statuses and gather essential information about them.

Step 2: Creating a Temporary Table

Next, we created a temporary table, FirstIssue, to store the details of the first issue with a valid status for each combination of project and issue type. This would serve as a reference point for updating the problematic issues.

Step 3: Updating the Null Status Issues

With the FirstIssue table in place, we could now proceed to update the null status issues. We executed the following SQL update statement:

WITH FirstIssue AS (
		SELECT
			p.id AS project_id,
			t.id AS issuetype_id,
			MIN(i.id) AS first_issue_id
		FROM
			jiraissue i
		JOIN
			project p ON i.project = p.id
		JOIN
			issuetype t ON i.issuetype = t.id
		LEFT JOIN
			issuestatus s ON i.issuestatus = s.id
		WHERE
			s.id IS NOT NULL
			AND t.pname IN ('AGILE', 'BROKER', 'DCT', 'DTSE', 'ITSM000', 'JINT', 'JPS', 'PEN', 'VMT' )
		GROUP BY
			p.id, t.id
	)
	UPDATE jiraissue i
	SET
		issuestatus = (
			SELECT issuestatus FROM jiraissue WHERE id = (SELECT first_issue_id FROM FirstIssue WHERE project_id = i.project AND issuetype_id = i.issuetype)
		)
	WHERE
		issuestatus IS NULL

This update statement effectively assigned a valid status to each issue by referencing the FirstIssue table, ensuring that the issues were no longer plagued by null statuses.

Conclusion

Resolving the challenge of null status issues in the Jira database during a cloud migration required a deep dive into the database and careful execution of SQL queries and updates. By following the steps outlined above, we were able to ensure that all issues had valid statuses, including AGILE-78, which was initially failing to export due to a java.lang.NullPointerException. This experience serves as a testament to the importance of adaptability and creative problem-solving when faced with unexpected obstacles in the world of IT and software migrations.