Clear or Delete Issues Search Suggestion

[Hamdy Atakora]

Hello,

Thanks in advance for taking the time to look into this. So here is my issues.

I have a custom field for creating and managing fix versions. We had a version called 3.0/Bird but someone made a mistake while creating the same version in another project and name it 3.0/Bord. For a while this was used my multiple issues.

When we realized it, we renamed 3.0/Bord to the correct spelling 3.0/Bird.

Now the problem is when we perform a search in Issue search for that versions, we get suggestions for both 3.0/Bird and 3.0/Bord. But since we renamed the second one, it returns no issues.

How can i delete it from showing up in the suggestions when performing a jql search.

I tried re-indexing, clearing caches and index files , restarted and re-indexed many times but the issue remains.

Please assist. Thanks

[Andy Heinzer · Accepted solution]

Hi Hamdy,

Thanks for describing this problem so clearly.  However I am having problems trying to recreate this specific problem.  I tried to rename an existing version I had, however when I trying to search in the advanced search of the issue navigator, I'm only seeing current version names.  However I am seeing version names that exist across all projects.

So I am curious to learn if perhaps you still have at least one project that is still using that old name of 3.0/Bord.   It might be really difficult to figure this out in the Jira user interface if you have a lot of projects.  But since I know you're using Jira Server, it should be possible for you to lookup all the current version names with a little SQL query against the Jira database such as:

select * from projectversion;

The vname column there will contain all the version names that exist in Jira right now.  Each of these corresponds to a project id, which we can lookup in the project table if we need to reference this.  You could try a more detailed query such as

select p.pname, p.pkey from project p
join projectversion pv on pv.project=p.id
where pv.vname='3.0/Bord';

This will return any/all project names and project keys that have a version with that exact name right of '3.0/Bord' (without quotes) now in Jira.   My hunch is you still have at least one project with the old version name.  This method should be able to help us to identify that project.

If you don't get back any results from that SQL query, please let me know what version of Jira this is.  Perhaps this might work differently in other versions.  I tested this against my Jira 8.1.0 running a postgresql database and it appears to work, but other databases could have a different syntax for SQL.

Andy

[Hamdy Atakora]

@Andy Heinzer  you are a Genius. I am not too good with SQL so i couldn't put this together. And you were right we have so many project that i couldn't do it manually for all of them.

The query you gave returned the project where it was still existing and once i renamed it, it was gone. That was amazing. Thank you so much.

Is there a way to write a similar SQL query to return a list of all labels used in a project A and the issues associated with it? 

Essentially i want to run a similar query but instead of the version, i want to see the labels used in the label field for a specific project 

Project, Project Key, Issue ID, Issue Key, Label

I really need to learn more about querying the Jira DB with SQL

[Andy Heinzer]

Hi Hamdy,

Glad to hear that helped.  I don't mind trying to investigate your followup question, but I would ask that you post it as a separate question. 

This way other users that might search Community will likely have a better chance to find that question in the future if other users might want to so something similar.  I think your second question is different enough that to try to answer it here might make it more confusing for others in the future.

Andy

[Hamdy Atakora]

Oh yes. My apologies. I am moving it to a separate issue

https://community.atlassian.com/t5/Jira-questions/Jira-SQL-Query-to-Return-all-Labels-from-the-Label-field-from-a/qaq-p/1085496#M346207