Project

General

Profile

Automatically delete closed issues older than 365 days

Added by Krzysztof K over 3 years 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 (9)

RE: Automatically delete closed issues older than 365 days - Added by Krzysztof K almost 3 years 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 over 2 years 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 over 2 years 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 over 2 years ago

Also interested here. Thanks in advance!

RE: Automatically delete closed issues older than 365 days - Added by Krzysztof K over 2 years 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 over 2 years 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 over 2 years 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 over 2 years 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!

RE: Automatically delete closed issues older than 365 days - Added by Mohamed El Habchi over 1 year ago

in my case which im deleting the old issues older than 6 months
I have created a file as delete_old_issues.rake and added the below script

namespace :redmine do
  desc "Deletes old issues in batches of 10k" 
  task delete_old_issues: :environment do
    Issue.where("created_on < '#{6.months.ago}' AND project_id = 574").find_each(batch_size: 10000) do |issue|
      issue.destroy
    end
  end
end

And I add the below cron which runs daily at 1AM

0 1 * * * cd /home/redmine/redmine && source /home/redmine/.rvm/environments/ruby-2.4.6; bundle exec rake redmine:delete_old_issues RAILS_ENV=production

you can edit the script as per the requirement

    (1-9/9)