nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Nic Brough -Adaptavist-

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.

All comments

Nic Brough -Adaptavist-

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.

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!

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_j7rt8t

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:

Connect to DB when JIRA service is stopped:
```
su postgres
```
```
psql -d jiradb
```
View your issue type ID's:
```
SELECT * FROM issuetype;
```
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)
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_j7rt8t

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?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

sql

jira

issue-type

edit

database