How can i remove items from the changeitem table.

BroadcomS June 1, 2014

My changeitem table now consumes over 50 percent of my database and it is growing, currently it is 19g on a 30g database with 500 issues. Most of the entries are due builds and interaction with SCM systems. What can i do

1) remove items periodically from change table.

2) limit the number of changeitems kept per issue.

1 answer

1 accepted

1 vote
Answer accepted
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, 2014

Removing the data destroys the information about changes, so you need to think very carefully before you do this.

The only way to do it without coding is to use SQL directly to kill the lines you decide you don't want. As you specify builds and SCM interactions, I'd attack them first. Identify the patterns, work out a decent rule for them, and remove the lines in SQL. However, you will also need to remove lines from changeGROUP for consistency - removing any changegroup lines that no longer contain any changeitems.

When you run the SQL for the two tables, you absolutely MUST backup the database, have Jira offline, and reindex as soon as you restart Jira.

As for point 2 - you can't. There's no limitations in Jira, it just keeps tracking changes.

BroadcomS June 6, 2014

Thanks, you answered my question.

Serhii Riabovil September 21, 2017

 

you will also need to remove lines from changeGROUP for consistency - removing any changegroup lines that no longer contain any changeitems

those who interested, PostgreSQL snippet to delete change groups that don't have change items

-- WARNING! DO NOT APPLY FOR PROD INSTANCE!

DELETE
FROM changegroup
WHERE ID = any (
  ARRAY(
    SELECT G.id
    FROM changegroup G
    LEFT JOIN changeitem I ON I.groupid = G.id
    WHERE I.groupid IS NULL
  )
);

 

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.
September 21, 2017

DO NOT DO THAT.

There are items in changegroup that have no items for very good reasons.  You are damaging your data.

Please, restore your data from the backup you took before doing this and don't ever do it again.

Serhii Riabovil September 21, 2017

There are items in changegroup that have no items for very good reasons. 

Nic Brough, any example of such entries? Checked on a large enough prod JIRA instance and didn't find any Change groups without Change items.

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.
September 21, 2017

Yes.

Why are you removing them?

Serhii Riabovil September 21, 2017

Why are you removing them?

In my case it was for partly anonymized staging (copy of prod) instance.

But generally I added the snippet for those who trying to do this:

you will also need to remove lines from changeGROUP for consistency - removing any changegroup lines that no longer contain any changeitems

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.
September 21, 2017

Ok, you're going about "anonymising" it in a broken way.  You should never delete this stuff, just change it so it's not identifiable.

Please, step away from the database, you could be doing all sorts of damage by doing things you don't understand.

For anonymisation, do it the correct way - see https://confluence.atlassian.com/jira/anonymising-jira-data-139008.html

Like Phil Evans likes this

Suggest an answer

Log in or Sign up to answer