Is it possible to reset the issue key number?

Sameera Shaakunthala [inactive]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 20, 2013

I have created few test issues in a new project. Now I want to delete them and make the issue numbering to start from the begining, rather than the last issue's number + 1.

Is this possible with either database manipulation or just from frontend?

We use Oracle 11.2.

5 answers

1 accepted

8 votes
Answer accepted
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 20, 2013

Write down the project's settings (schemes etc), then delete the project (which will kill all the issues as well) and then create it again with the same short code.

For database manipulation, delete all the issues in the UI first, stop Jira, get a backup (although this is unlikely to cause a fault), open up the "project" table and change the pcounter to 1 (That's from memory - it might be 0, you can check this by creating a new empty project and reading the pcounter for that from the database to confirm it)

Sameera Shaakunthala [inactive]
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
February 20, 2013

Thanks!

It's just a simple SQL then!! :)

update project
set pcounter = 0
where pkey = 'PROJ';

commit;

DB manipulation seems to be the easiest option, however it's better to test it on a test environment first.

Like # people like this
Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
February 20, 2013

Definitely test it :-) I prefer the db change as well myself, because I'm lazy and don't want to have to put all the schemes back.

Just make sure you have Jira offline when you do it. Project counter keys are one of the things it almost always caches, so if it's running when you make the change, you could see it wiped out by the cache.

Oleksii Gnatkevych January 9, 2015

You may do it without restarting JIRA instance by using script runner plugin and this piece of groovy:

 

import com.atlassian.jira.ComponentManager
ComponentManager cm = ComponentManager.getInstance()
def pm = cm.getProjectManager()
pm.refresh()

 

Like Radek Janata likes this
Federico De Luca March 12, 2015

Hi, I'm creating a new Project and I need it's starts the counter on 31000 and no 0, how can I modify this? I'm using Jira server and SQL Server 2012 Regards

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 12, 2015

See above - Sameera has given you the SQL you need for that hack.

Like opysarenko likes this
Federico De Luca March 12, 2015

@Nic br, thanks for your quick response Where should I execute that query and with values should I change to apply it on my db?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
March 12, 2015

Use whatever tool you are most comfortable with when editing data in a SQL Server database. Set the pcounter to the number you want to start at -1

Federico De Luca March 12, 2015

@Nic Brough [Adaptavist] , sorry. I figured out myself how to fit it to my scenario Regards for your help

3 votes
Daniel Santos
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
August 13, 2015

Hello,

    This thread is a bit old but this KB is new and may help someone.
    https://confluence.atlassian.com/display/JIRAKB/How+to+reset+the+project+counter+after+moving+issues

    When there are moved issues another table needs to be changed, the moved_issue_key.

 

Joel Chuah
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 8, 2015

Thanks for this

0 votes
Jan Szczepanski
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 19, 2021

Hi all,

actually you can reset the project key when using scriptrunner. Here is the snippet, but be careful when using it ;)

https://bitbucket.org/janszczepanski/workspace/snippets/oBkRLy

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 19, 2021

Um, no, that could easily fail, as you've not taken account of the caches.  I don't think SR can even clear the cache you would need to hit. 

Your script could only work safely if you stopped Jira, restarted it, run the script before anyone starts using Jira projects, then stop and restart it again.

Srikanth Ganipisetty June 9, 2021

@Nic Brough -Adaptavist- Is that mean we can accomplish this by following the 'SR' if we stopped Jira and perform the SR actions? 

Thanks! 

@Jan Szczepanski 

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
June 12, 2021

No.  If you stop Jira, you can't run scriptrunner scripts.

Srikanth Ganipisetty June 16, 2021

Sorry, I completely forgot the logic here :) 

Like Nic Brough -Adaptavist- likes this
0 votes
Hugo December 31, 2017

Hello guys, just to give more information about the procedure given by Sameera Shaakunthala [inactive] Feb 20, 2013:

update project
set pcounter = 0
where pkey = 'PROJ';

commit;

Speccialy for who doesn't know so much about Linux commands and has an enviroment hosted on Linux machine:

1-First you have to stop jira service on Linux machine:

~$ sudo service jira stop

2-Confirm if the service is really inactive/stopped:

~$ service jira status

3-A line like this should be appear:

Active: inactive (dead) since...

4-Then, let's logon into mysql (here the user for mysql is 'root'): 

mysql -u root -p

5-On command line should appear 'mysql>', where you'll type the following command, just attention that 'jiraservicedesk' is the name for the Jira Service Desk Database:

USE jiraservicedesk

6-Finishing you'll type the procedure given by Sameera:

update project
set pcounter = 0
where pkey = 'PROJ';

7-This message will appear on screen:

Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

8-This last command is just to start jira service again:

~$ sudo service jira start

For Linux commands, these websites helped me so much:

Thanks Sameera and all!

0 votes
TheKingArthas September 13, 2016

Hi, I have tried to perform this proceedure again on another JIRA instance but I get  that the object name "project" is invalid. Any idea?

Nic Brough -Adaptavist-
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
September 13, 2016

Check the syntax guide for your database.

TheKingArthas September 14, 2016

 

For those who are not SQL experts (like me) here is the proceedure I followed:

O.S.: Windows Server 2012 R2

SQL Server version: 11.0.60.20 (2012)

JIRA schema: "jiraschema"

 

IMPORTANT: As Nic told before, you must stop JIRA and perform a backup before peforming these changes

 

  1. Go to MSSQL Server Management Studio and expand your JIRA database ("jiradb" on my case)
  2. There navigate to "jiradb\Tables\FileTables\jiraschema.project"
  3. Then do left click on "jiraschema.project" and select "Edit firsts 200 rows"
  4. On the new table that is shonw, navigate to the "pccounter" row and modify there the number with your desired starting key number (In my case I change 0 to 9989).

20160914_JIRA_ChangeKeyNumber.PNG

(i) It´s was translated by me, so some names can be different

 

Suggest an answer

Log in or Sign up to answer