Automatically delete closed issues older than 365 days

Added by Krzysztof K over 1 year ago

Hi, I need to automatically delete all closed issues from all projects older than 365 days from redmine. I need help with sql query becouse I'm not familiar with sql.

Environment:
Redmine version 4.0.5.stable
Ruby version 2.6.5-p114 (2019-10-01) [x86_64-linux]
Rails version 5.2.3
Environment production
Database adapter Mysql2
Mailer queue ActiveJob::QueueAdapters::AsyncAdapter
Mailer delivery smtp

Replies (8)

RE: Automatically delete closed issues older than 365 days - Added by Krzysztof K 12 months ago

cd /opt/bitnami/redmine/bin
./rails c production
Issue.where("closed_on < '#{365.days.ago}'").delete

RE: Automatically delete closed issues older than 365 days - Added by Jim Lee 11 months ago

One modification to the above suggestion...

You can use #Destroy to make sure that all callbacks for your issues are applied (things like clearing up issue relations, links and removing attachments from the file system). Delete is thousands of times faster, and does not use as many resources to perform, but you can leave your Projects and server in a very unhappy state. My suggestion is to do the following:

# RAILS_ENV=production bin/rails console
> p = Project.find("$project_name")
> p.issues.where("closed_on < '#{365.days.ago}'").each(&:destroy)
# To perform this actions across all projects...
> Issue.where("closed_on < '#{365.days.ago}'").each(&:destroy)

Just be warned, this is a resource intensive process and will bog down your server, but it properly clears things up and is the same action as the "Delete" action that is performed via the Redmine web interface

RE: Automatically delete closed issues older than 365 days - Added by Krzysztof K 6 months ago

Jim Lee wrote:

One modification to the above suggestion...

You can use #Destroy to make sure that all callbacks for your issues are applied (things like clearing up issue relations, links and removing attachments from the file system). Delete is thousands of times faster, and does not use as many resources to perform, but you can leave your Projects and server in a very unhappy state. My suggestion is to do the following:

[...]

Just be warned, this is a resource intensive process and will bog down your server, but it properly clears things up and is the same action as the "Delete" action that is performed via the Redmine web interface

Thanks, how I can run this as a script using cron?

RE: Automatically delete closed issues older than 365 days - Added by Luis Blasco 6 months ago

Also interested here. Thanks in advance!

RE: Automatically delete closed issues older than 365 days - Added by Krzysztof K 6 months ago

It was not easy but it seems to work...

the command to put in the cron:

/opt/bitnami/redmine/bin/rails runner -e production /usr/scripts/delete_old.rb > /dev/null 2>&1

contents of the /usr/scripts/delete_old.rb :

puts Issue.where("closed_on < '#{365.days.ago}'").each(&:destroy)

This seems to work even though the command outputs help of the rails command as if it was something wrong with the syntax.
I've checked that behavior is the same with any other command. It looks like in the rails' strange word this means a command was successfully executed.
If something was wrong an error would appear instead of the command manual.

RE: Automatically delete closed issues older than 365 days - Added by Luis Blasco 6 months ago

Thanks a lot for your help, Krzysztof. Sorry to bother you again... what would be the command for similar query but only for project id=37 and tracker id=2?
Thank you in advance.

RE: Automatically delete closed issues older than 365 days - Added by Krzysztof K 6 months ago

Luis, my knowlege of RoR is very limited, so please test it in rails console without each(&:destroy) action to be sure it's okay.

Issue.where("closed_on < '#{365.days.ago}'",tracker_id:'2',project_id:'37').each(&:destroy)

If you'll need any modification of the above you can base on this: https://api.rubyonrails.org/v7.0.2.2/classes/ActiveRecord/QueryMethods.html#method-i-where

RE: Automatically delete closed issues older than 365 days - Added by Luis Blasco 6 months ago

Hi,Krzysztof.

I tried your command but it didn't seem to work properly. I managed to solve this with this other command:

Issue.where("created_on < '#{365.days.ago}' AND project_id = 37 AND tracker_id = 4").each(&:destroy)

Thanks a lot for your help!

(1-8/8)