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

This widget could not be displayed.
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!

This widget could not be displayed.

what abt issues?

This widget could not be displayed.

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

This widget could not be displayed.

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
Community showcase
Posted yesterday in Jira

What modern development practices are at the heart of how your team delivers software?

Hey Community mates! Claire here from the Software Product Marketing team. We all know software development changes rapidly, and it's often tough to keep up. But from our research, we've found the h...

67 views 0 1
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