Deleting projects via sql in Jira 5.05 - wise or very very foolish?

Bryan Karsh
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.
June 1, 2012

Hi,

Out of curiosity, I was tailing my mysql.log while deleting a project in Jira. I took note of the delete commands coming through the logs, and modified them a bit. What I want to do is find a way to delete all projects except a particular project (in this case 10830). I came up with this:

DELETE FROM configurationcontext WHERE PROJECT != '10830';
DELETE FROM configurationcontext WHERE PROJECT is NULL;

DELETE FROM nodeassociation WHERE SOURCE_NODE_ID != '10830';

DELETE FROM sharepermissions WHERE sharetype='project' AND PARAM1 != '10830';

DELETE FROM projectroleactor WHERE PID != '10830';
DELETE FROM projectroleactor WHERE PID is NULL;

DELETE FROM project WHERE ID != '10830';

Once those steps are complete, I run an integrity check to clean up any hanging issuelinks, etc., and re-index.

This seems to work in my test instance.

Any problems with this approach (other than a lot of room for human error?) -- seems ideal when I need to clean up an instance fast in terms of removing *many* projects.

4 answers

1 accepted

1 vote
Answer accepted
Bob Swift OSS (Bob Swift Atlassian Apps)
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.
June 1, 2012
Why worry about all this and potentially corrupt your instance? Just use a legal JIRA action - deleteProject action (https://bobswift.atlassian.net/wiki/display/JCLI/Documentation#Documentation-deleteProject) from the JCLI (https://bobswift.atlassian.net/wiki/display/JCLI)
Bryan Karsh
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.
June 1, 2012

D'oh -- thanks Bob -- I will check that out!

0 votes
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 1, 2012

You also need to delete the issues. You've spotted the change history and workflows, but you would also need to deal with versions, components, voters, watchers (nodeassociation), custom field values, worklogs, and and and

Short answer - I really wouldn't do it that way. I'd click "delete" on the unwanted projects. Or, export the xml backup, then import just the project you want to keep into a dev system.

p.s.

> DELETE FROM nodeassociation WHERE SOURCE_NODE_ID != '10830';

Arg - no - that could do all sorts of damage too! Source_node_id could be anything, not just a project...

0 votes
Bryan Karsh
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.
June 1, 2012

I'd probaby do something like this..:

delete from jiraissue where resolutiondate project != '10830'


delete from notificationinstance where SOURCE not in (select id from jiraissue);

delete from jiraaction where issueid not in (select id from jiraissue);


delete from changegroup where issueid not in (select id from jiraissue);


delete from changeitem where groupid not in (select id from changegroup);


delete from OS_CURRENTSTEP where ENTRY_ID not in (select WORKFLOW_ID from jiraissue);


delete from OS_HISTORYSTEP where ENTRY_ID not in (select WORKFLOW_ID from jiraissue);

... anyone let me know if I am missing something...

0 votes
Jobin Kuruvilla [Adaptavist]
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.
June 1, 2012

what abt issues?

Suggest an answer

Log in or Sign up to answer