Uploaded image for project: 'Jira Platform Cloud'
  1. Jira Platform Cloud
  2. JRACLOUD-22351

Deleting a version that has associated issues makes those issues fail to match fixVersion is EMPTY JQL queries

XMLWordPrintable

      NOTE: This bug report is for JIRA Cloud. Using JIRA Server? See the corresponding bug report.

      Issue Description

      Deleting a version from the project version which has issue still associated with them does not remove the entries from nodeassociation table. This causes the JQL query fixVersion is EMPTY not able to return those issues despite there is no Version associated.

      Steps to replicate

      1. Create a project version
      2. Associate to an issue's fixVersion
      3. Delete the project version and select remove version
      4. Verify the fixVersion is now shown as none
      5. Run JQL query for fixVersion is EMPTY
      6. Notice that the issue on step 2 does not list as result

      Expected Result

      Returning the issue since the version is no longer valid

      Workaround

      Delete the entries from the nodeassociation table manually for those issues that have no reference to the projectversion table:

      Important: Always take a full backup from your database before doing any direct changes on database level - Please refer to the Backing Up Data for more details.

      1. Verify how many affected entry using: 
        select (p.pkey || '-' || i.issuenum) as issuekey, n.sink_node_entity, n.sink_node_id 
from jiraissue i, project p, nodeassociation n 
where i.id = n.source_node_id
and i.project = p.id
and source_node_entity = 'Issue'
and sink_node_entity = 'Version' 
and sink_node_id not in (select id from projectversion);

        The above query does not work prior to JIRA 6.1

      2. Stop JIRA.
      3. Deleting the entries from the database: 
        delete from nodeassociation where source_node_entity = 'Issue' and sink_node_entity = 'Version' and sink_node_id not in (select id from projectversion);
      4. Start JIRA.
      5. Re-index JIRA.

      For JIRA Cloud customers, please raise an issue at support and select JIRA Cloud project for assistance.

      Previous Description is located below:

      I've worked around the problem described below by doing:

DELETE a.* FROM nodeassociation a
LEFT JOIN projectversion v ON a.SINK_NODE_ID = v.ID
WHERE a.ASSOCIATION_TYPE = 'IssueFixVersion'
AND v.ID IS NULL;

------ Forwarded Message
From: Justin de Vesine
Date: Thu, 23 Sep 2010 14:16:37 -0500
To: Matt McClure
Subject: Re: JIRA fixVersion appears as None, but doesn't match "fixVersion is EMPTY" query

This SQL query demonstrates the two healthy and one unhealthy case: MMF-3602, which has a fixversion; MMF-82, which doesn't have a fix version (and shows up in a JQL query like "fixVersion IS EMPTY and key = MMF-82"); MMF-92 (which has a fixversion which got deleted).

SELECT `jiraissue`.`pkey` AS `issue`, `nodeassociation`.`SOURCE_NODE_ID` AS `version_id`, `projectversion`.`vname` AS `version_name` FROM `jiraissue` LEFT JOIN `nodeassociation` ON `nodeassociation`.`SOURCE_NODE_ENTITY` = "Issue" AND `nodeassociation`.`SOURCE_NODE_ID` = `jiraissue`.`ID` AND `nodeassociation`.`SINK_NODE_ENTITY` = "Version" LEFT JOIN `projectversion` ON `projectversion`.`ID` = `nodeassociation`.`SINK_NODE_ID` WHERE `jiraissue`.`pkey` IN ('MMF-92', 'MMF-82', "MMF-3602") 

version_id and version_name both set or both null is good; only version_id set indicates the ticket has no valid fixversion but fixversion is not empty.

It appears that we have 990 such invalid fixversions set on various tickets:

SELECT `jiraissue`.`pkey` AS `issue`, `nodeassociation`.`SOURCE_NODE_ID` AS `version_id`, `projectversion`.`vname` AS `version_name` FROM `jiraissue` LEFT JOIN `nodeassociation` ON `nodeassociation`.`SOURCE_NODE_ENTITY` = "Issue" AND `nodeassociation`.`SOURCE_NODE_ID` = `jiraissue`.`ID` AND `nodeassociation`.`SINK_NODE_ENTITY` = "Version" LEFT JOIN `projectversion` ON `projectversion`.`ID` = `nodeassociation`.`SINK_NODE_ID` WHERE `nodeassociation`.`SINK_NODE_ID` IS NOT NULL AND `projectversion`.`vname` IS NULL

and cleaning them up (once you have satisfied your investigations) appears to be simple (DELETE FROM nodeassociation WHERE {stuff}).

It does not appear that the integrity tools built into JIRA or GreenHopper detect this issue.

-Justin

On Sep 23, 2010, at 10:41 AM, Matt McClure wrote:

This query finds the issue:

https://jira/secure/IssueNavigator.jspa?reset=true&jqlQuery=key+%3D+MMF-92+AND+(fixVersion+not+in+releasedVersions()+OR+fixVersion+is+EMPTY+OR+fixVersion+in+unreleasedVersions())+AND+issuetype+in+(%22Theme:+Tier+1%22,+%22Theme:+Tier+2%22)+AND+NOT+status+in+(Closed,+Resolved)+ORDER+BY+Rank+ASC,+key+ASC

This query does not:

https://jira/secure/IssueNavigator.jspa?reset=true&jqlQuery=key+%3D+MMF-92+AND+(fixVersion+is+EMPTY+OR+fixVersion+in+unreleasedVersions())+AND+issuetype+in+(%22Theme:+Tier+1%22,+%22Theme:+Tier+2%22)+AND+NOT+status+in+(Closed,+Resolved)+ORDER+BY+Rank+ASC,+key+ASC

Hypothesis 1: The issue has a fixVersion that no longer exists.

Hypothesis 2: There is a bug in JIRA or Greenhopper that corrupts fixVersion data on some issues.

Another observation: The query below matches 61 issues:

https://jira/secure/IssueNavigator.jspa?reset=true&jqlQuery=fixVersion+not+in+releasedVersions()+and+fixVersion+not+in+unreleasedVersions()+and+fixVersion+is+not+EMPTY

              ohernandez@atlassian.com Oswaldo Hernandez (Inactive)
              matthewlmcclure Matt McClure (Inactive)
              Votes:
              11 Vote for this issue
              Watchers:
              18 Start watching this issue

                Created:
                Updated:
                Resolved: