Modify issue type via SQL

[Andrew Thorne]

We have recently merged two JIRA systems in to one. Everything seems to have gone well and we are now looking at tidying our merged environment. This includes issues types.

As you can imagine, we have a large number of issue type now with similar names for the same type of issue but were using on the different systems.

JIRA will not let us edit the issue type of closed issues, I was wondering if using an SQL UPDATE command directly on the database would allow us to change the issue types so that the duplicates could be removed. Of course, I would make sure the "new" issue type was valid for the project before doing the change.

Does anybody have any experience or comments about this? Is there a better way to tidy up the issue types?

Thanks in advance,

Andrew

[Nic Brough -Adaptavist- · Accepted solution]

Yes, do NOT even think about doing this with SQL.  For very simple configurations it can work, but as soon as you have more than one workflow or use different fields, you can easily and rapidly corrupt your data beyond repair.

Edit the issue type schemes for the projects, this will take you through a safe migration that will ignore the "do not edit" flags on the issues.

[Andrew Thorne]

Hi, Nic,

So are you saying: If I edit the issue type schemes and remove the "old" issue type and add the "new" issue type, JIRA will allow me to migrate the "old" issues to the new "new" issue type without saying "No, you can't because this issue is read-only"?

Andrew

[Nic Brough -Adaptavist-]

Yes, it ignores the read-only flag on re-configuration.  It's not an edit, it's a structural change.

[Ilya Sapunov]

Hi, Andrew!

You can update your database and change Issue types of closed issues.

For example, I have 200 000 closed issues. And I need to change Issue types. DB Server is PostgreSQL.

Steps:

1. Connect to DB when JIRA service is stopped: 

   su postgres

   psql -d jiradb

2. View your issue type ID's: 

   SELECT * FROM issuetype;

3. Update the types in issue table: 

   UPDATE jiraissue SET issuetype = '10200' WHERE issuetype = '10100' AND project = 10100;

   (issuetype ID is a string in table jiraissue; issuetype = 10200 - new issuetype, issuetype = 10100 - old issuetype; project is a numeric type)
4. Start JIRA service and re-index the database

Regards, Ilya

[Nic Brough -Adaptavist-]

NO NO NO.  NEVER DO THIS.You will break your Jira if you do this (except in one very very specific case, which we would still very strongly recommend you never do as well).

[Ilya Sapunov]

Nic, please write what exactly is broken in the Jira?

In the previous comment I fixed Step 3 (added project condition).

I did this operation with more than 600,000 issues.

You wrote:

Edit the issue type schemes for the projects, this will take you through a safe migration that will ignore the "do not edit" flags on the issues.

It does not work for me.

[Nic Brough -Adaptavist-]

Every single one of the 600,000 issues you've mangled the data on.

Your SQL ignores all the configuration checks and data consistency that is required.   Where are the checks on field consistency?  Workflow settings?

[Ilya Sapunov]

First I change workflow settings and only after that I execute SQL. 

All checks are passed.

[Nic Brough -Adaptavist-]

Change workflow settings?  Could you explain what you've done in full to get this to the one case where there's a possibility it might be a safe thing to do?

(And the fields?)

[Ilya Sapunov]

Ok, I will write my problem and my solution.

I had a project in Jira Service Desk. In the project were created:

• Custom fields
• Custom issue types and schemes
• New workflows, transitions, post-functions and conditions (and new schemes)
• New screens and permission schemes

But after a while I was need to split project.

I created new projects and set up new workflows and more. I needed to transfer some issue types from old project to new project. I had associating issue types on new project. I had more than 650000 closed issues.

I created new issue type, and I linked it with the old workflow.

To remove issue types from old project, I ran SQL (changed issue types) and deleted issue types from old project.

[Nic Brough -Adaptavist-]

Ok.  So you have missed a whole batch of stuff, and you have corrupted your Jira.

That's all I needed to know.  My advice stands - DO NOT DO THIS.

[Ilya Sapunov]

Give an example, please.

[Nic Brough -Adaptavist-]

If you don't understand, then you should never have done this.  There's no point me explaining the other things you have damaged or may have damaged, the fact is you've done the wrong thing. 

I've already given away one of the other two main areas you've messed up, and I don't want to give anyone else any idea that this might work by telling them.  If you can't work out what I'm worrying about, you should not do this.

Your system might be alright, if you're lucky.  But please, admit that your approach is absolutely the wrong thing to do and should never be attempted unless you know you've thought of everything.

[Ilya Sapunov]

I did this because the built-in functionality did not work.
My jira is working.
But I understand your reasons. This method is not recommended for most users.
At the same time, I hope that there are reasonable people here and will not blindly execute the commands. ))

[Nic Brough -Adaptavist-]

It's not recommended for anyone. 

[Joe Zangara]

I had to consolidate multiple issue types to one and did the following:

TOOLS-->BULK CHANGE--MOVE   

In my case we moved multiple issue types from multiple projects into a single project while consolidating issue type too.  And other times the move was within the same project and just issue type changes.

My lesson learned  and recommendation would be to take all the data with you in the move because like a gun, it is better to have it and not need it than need it and not have it.  With that in mind....

JQL:  issuetype = <MigrateFromIssueType> OR issuetype =<MigrateToIssueType> ORDER BY issuetype

Export  the result to excel with ALL FIELDS selected.

Look at the spreadsheet to see if there is any data you don't care to see any more  from the MigrateFrom issue types but still want to maintain for historical reference.

Then in the MigrateToIssueType Screen scheme, add a Tab called "Legacy Data" and put the retired MigrateFromIssue fields in there. and ensure the field schemes allow those fields in the new issue types. 

This is important because if you don't do it prior to the move, then Jira will drop the field for that issue.  If you add the field after the move, the data will not be there.   You will see which fields wont make it during the BULK CHANGE steps.  Then just go back and add more fields to screens and schemes.  Once you make it through the move in BULK Change, you can always hide the legacy data tab, on the screen, but the data will still be there if you ever need to query the field.

You may want to ensure your status types are congruent, but that isn't too painful.  Again, the BULK change move was easy enough and  made it obvious if I had a FROM status that would not make sense in the NEW STATUS.

This saved my bacon when people came looking for old data.  DISCLAIMER, it was on Jira 6.x.  Maybe 7.x is a little different regarding the dropping of data from fields not in your field config.

Good Luck!