How to remove unused Issue Keys without database manipulation

[Fabio Kathriner]

I had the same Issue as described here: https://community.atlassian.com/t5/Jira-questions/Ticket-numbers-jumping/qaq-p/129987

It might not be entirely the same but the consequences certainly are. After some problems with one of the Mail handlers the issue keys jumped from 2000 up to around 15000.

Is there some way to reuse/delete all the unused Keys? I've seen some solutions that could work by manipulating the database. Unfortunately I don't have access to the server myself. That's why I consider this as the last possible option only.

Thanks in advance for any hints.

[Nic Brough -Adaptavist- · Accepted solution]

To ne clear, by "unused keys" do you mean you have issues numbered 2001 to 15,000 that you don't want?  Or do you just mean that the next issue created is 15,001 and there are no extra issues?

[Fabio Kathriner]

The second case. The next issue that was created is 15001 and there's just a gap in between as if the issues were deleted. So there are no extra issues there.

[Nic Brough -Adaptavist-]

OK, there's nothing you can do here.  There are no "unused keys", there's nothing actually there to remove.

It's worth explaining the data behind this, rather than just say "nothing you can do", because that's not entirely true.

Jira has a (simple-ish) table called "Project", which holds one line per project and lists some fundamental project data - key, lead, and the one we're interested in - counter.

When you create a new issue,  Jira looks at the counter and takes the next free number (warning - I can never remember if the counter holds the last created issue number or the next available number to use.  I think it's the last created, so an empty project has a pcounter of 0, and a project on ABC-123 would have 123, but if you're going to mess with it, you should check this carefully)

So, it sounds like there is an easy fix - stop Jira, nip into the database, and set the pcounter to the value of the last number used (or that + 1 as per warning above) and restart Jira.  You don't need to re-index or do an integrity check, unlike most other SQL changes.

But, that only works if you have not created any more issues in the project.

In your case, you now have ABC-15001, which means that if you changed the counter to something lower, Jira could get to a point where it is trying to create ABC-15001 again - and that fails, horribly.  You lose all the new issue data and corrupt the old one.  I am not sure if the core of Jira has any other problem working when there's a bad number in the project table like this, but I've seen it cause problems in (mostly reporting) plugins.   Plus your humans don't tend to understand how ABC-2001 can have a created date later than ABC-15001)

So, if you are going to set the counter in a project where there have been issues created, you're going to have to delete those new issues.  You can't just move them out of the project and back in after resetting the counter - you'll leave broken records of issue moves if you do that (although that won't cause you massive problems, it can make it look like you've got duplicate issues with totally different data, and hide the "correct" issue with an irrelevant one in some places)

[Fabio Kathriner]

Alright, that makes sense. Thanks for the detailed explanation. It won't be a problem to create the new issues again by hand.

In that regard: Deleting the new Issues with numbers from 15000+, setting the counter to the appropriate value and creating them again by hand shouldn't cause any issues, right?

[Nic Brough -Adaptavist-]

Yes, that's spot on.  Glad my rambling explanation made sense!