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

[Bryan Karsh]

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.

[Bob Swift · Accepted solution]

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]

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

[Nic Brough -Adaptavist-]

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

[Bryan Karsh]

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

[Jobin Kuruvilla [Adaptavist]]

what abt issues?