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

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

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)

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

0 vote

what abt issues?

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 vote

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...

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Julia Dillon
Posted Tuesday in Jira

Tell us how your team runs on Jira!

Hey Atlassian Community! Today we are launching a bunch of customer stories about the amazing work teams, like Dropbox and Twilio, are doing with Jira. You can check out the stories here. The thi...

242 views 1 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you