How to manually add Workflows and Issue Types to Projects through the database in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Updating Workflow Schemes in use by Projects in Jira can take a long time, depending on how many Projects are using this Workflow and how many Issues there are in such Projects.
We have this issue tracked in JRASERVER-71742 - Modifying workflow schemes that applies to hundreds or thousands of projects can take up to 2 weeks to complete
However, if we're adding a new Workflow and new Issue Type to the Project, we may workaround the issue above through the database instead and then each node restarted (rolling restart in Data Center) to refresh the cache.
Note this workaround only applies when adding a new Workflow that's mapped to a new Issue Type in the Project.
Don't perform database updates when changing Workflow mappings that are already in use and may have Issues already associated with them.
Environment
Any version of Jira Data Center, both Jira Software and Jira Service Management.
Solution
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
The steps listed below are for adding a single Workflow to a single new Issue Type and a single Workflow Scheme. If you need to add more than one Workflow, Issue Type or Workflow Scheme, repeat steps 2–5 for each, changing the names and Ids accordingly each time.
1. Create a PAT
Through Jira's UI, create a PAT from an Admin account to later be used to log the changes to the audit log.
See Using Personal Access Tokens.
2. Identify the Projects Workflow Schemes
From the SQL query below, take note of the Ids of the Workflow Schemes in use by the Project. Change the Workflow Scheme names for those in use by the Project (you can see them in the Project Admin Summary screen):
1
2
3
4
5
select id, name
from workflowscheme
where name in (
'Example Workflow Scheme'
);Example output:
1
15300 Example Workflow SchemeIf you need to change multiple Workflow Schemes, you may run the query above for each of them and take note of their respective Ids.
3. Add the new Issue Type to the Projects
Through Jira's admin UI, add the new Issue Type to the Project. It'll be mapped to the default Workflow in the Project's Workflow Scheme.
Also, don't select this new Issue Type as the Default for the Scheme. If you want it to be the Default, make that configuration change after the last step outlined in this article.
Steps 3 and 4 need to be executed as closely as possible — preferably during a maintenance window with no end-user activity in Jira.
This is to prevent a user inadvertently creating a new Issue on the Project with the just newly available Issue Type.
4. Insert record into DB
Run the below insert into the DB:
1
2
3
4
5
6
7
insert into workflowschemeentity (id, scheme, workflow, issuetype)
values (
(select max(id)+1 as "id" from workflowschemeentity),
(select id from workflowscheme where name = 'Example Workflow Scheme'),
'Example Workflow',
(select id from issuetype where pname = 'Example Issue Type')
);Replacing:
'Example Workflow Scheme' by the Workflow Scheme's name
'Example Workflow' by the Workflow's name
'Example Issue Type' by the Issue Type's name
5. Record the change into Jira's audit log
As we're making a change directly on the DB, it's important to record an audit entry for consistency.
You can make a POST request to <jira-base-url>/rest/api/2/auditing/record with the following JSON body:
1
2
3
4
5
6
7
8
9
{
"summary": "Workflow scheme updated through the Database",
"category": "workflows",
"objectItem": {
"id": "15300",
"name": "Example Workflow Scheme",
"typeName": "SCHEME"
}
}Or use this curl example:
1
$ curl -k -H "Authorization: Bearer XYZ" -H "Content-Type: application/json" -X POST "https://jira-base-url/rest/api/2/auditing/record" -d '{"summary": "Workflow scheme updated through the Database", "category": "workflows", "objectItem": {"id": "15300", "name": "Example Workflow Scheme", "typeName": "SCHEME"}}';This is where you can use the PAT from step 1, replacing the token ZYX for it.
Also replace "Example Workflow Scheme" and 15300 for the respective Workflow Scheme name and Id (from step #2) accordingly.
If you have a ticket or request Id and you want to make the linking clearer, you may add it to the "summary" of the Audit log entry, like "Workflow scheme updated through the Database (SREQ-12345)".
6. Restart Jira
You'll need to restart Jira for the changes to take effect. Do this as soon as possible to avoid data inconsistencies. If it's a Data Center cluster, you can perform a rolling restart one node at a time.
After the restarts you may validate the changes by creating a new Issue on the Project with the new Issue Type and confirm it was created with the expected Workflow.
If you want the new Issue Type to be the Default for the Project, you may now update the Issue Type Scheme through the Jira Admin UI.
Was this helpful?