Uploaded image for project: 'Jira Server and Data Center'
  1. Jira Server and Data Center
  2. JRASERVER-22351

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

    XMLWordPrintable

    Details

      Description

      NOTE: This bug report is for JIRA Server. Using JIRA Cloud? 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
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: