Loading...

Details

Type: Bug
Resolution: Fixed
Priority: High
Component/s: Directories
Labels:
- affects-cloud
- affects-server

Bug Fix Policy:
View Atlassian Cloud bug fix policy

Description

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

Create a project version
Associate to an issue's fixVersion
Delete the project version and select remove version
Verify the fixVersion is now shown as none
Run JQL query for fixVersion is EMPTY
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.

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

Stop JIRA.

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);

Start JIRA.
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

causes

JSWCLOUD-12090 Deleting a Version does not update issue history

Closed

is duplicated by

JRACLOUD-45149 Incorrect JQL return on fix/affected version due to deletion of version leaving link to 'Fix Versions' or 'Affected Versions in the database

Closed

is related to

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

Closed

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