From d984fb01ab09dc01f4bba65c6d6b593334ff4787 Mon Sep 17 00:00:00 2001
From: Aleisha Amohia <aleishaamohia@hotmail.com>
Date: Fri, 16 Jun 2023 14:06:21 +1200
Subject: [PATCH] Bug 34027: Add functionality to cleanup_database.pl to purge
 action logs in batches

This enhancement gives libraries the option to purge action logs 'slowly', in batches. This is useful for when there are many, many rows to purge (i.e. millions).

To test, set up some sample logs (you'll need to change the timestamp in the database so they are picked up by the cronjob), run the cleanup_database.pl script with the --logs parameter and confirm it behaves as expected.

For example:

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --logs-batch 500 --verbose

Test cases:

1. logs argument, no logs-batch argument. Cleanup should run as normal, in one query.

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --verbose

2. logs argument, logs-batch argument with no ROWS specified. logs-batch should default to 1000, deletions done in batches of 1000 rows at a time.

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --logs-batch --verbose

3. logs argument, logs-batch argument with ROWS specified. Deletions should be done in batches of the specified ROWS

sudo koha-foreach --chdir --enabled /kohadevbox/koha/misc/cronjobs/cleanup_database.pl --confirm --logs 1 --logs-batch 500 --verbose

Sponsored-by: Auckland University of Technology
---
 misc/cronjobs/cleanup_database.pl | 23 ++++++++++++++++++++++-
 1 file changed, 22 insertions(+), 1 deletion(-)

diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl
index 01930181294..dd4f280355b 100755
--- a/misc/cronjobs/cleanup_database.pl
+++ b/misc/cronjobs/cleanup_database.pl
@@ -30,6 +30,7 @@ use constant DEFAULT_DEBARMENTS_PURGEDAYS         => 30;
 use constant DEFAULT_JOBS_PURGEDAYS               => 1;
 use constant DEFAULT_JOBS_PURGETYPES              => qw{ update_elastic_index };
 use constant DEFAULT_EDIFACT_MSG_PURGEDAYS        => 365;
+use constant DEFAULT_LOGS_BATCHSIZE               => 1000;
 
 use Getopt::Long qw( GetOptions );
 
@@ -87,6 +88,8 @@ Usage: $0 [-h|--help] [--confirm] [--sessions] [--sessdays DAYS] [-v|--verbose]
    --log-action       Specify which action log action entries to trim. Repeatable.
    --logs DAYS        purge entries from action_logs older than DAYS days.
                       Defaults to 180 days if no days specified.
+   --logs-batch ROWS  Delete action_logs rows in batches of ROWS. Ignored if --logs is not set.
+                      Defaults to 1000.
    --searchhistory DAYS  purge entries from search_history older than DAYS days.
                          Defaults to 30 days if no days specified
    --list-invites  DAYS  purge (unaccepted) list share invites older than DAYS days.
@@ -171,6 +174,7 @@ my $jobs_days;
 my @jobs_types;
 my $reports;
 my $edifact_msg_days;
+my $logs_batch;
 
 my $command_line_options = join( " ", @ARGV );
 
@@ -218,6 +222,7 @@ GetOptions(
     'jobs-days:i'                => \$jobs_days,
     'reports:i'                  => \$reports,
     'edifact-messages:i'         => \$edifact_msg_days,
+    'logs-batch:i'               => \$logs_batch,
 ) || usage(1);
 
 # Use default values
@@ -232,6 +237,7 @@ $pMessages        = DEFAULT_MESSAGES_PURGEDAYS      if defined($pMessages)
 $jobs_days        = DEFAULT_JOBS_PURGEDAYS          if defined($jobs_days)        && $jobs_days == 0;
 @jobs_types       = (DEFAULT_JOBS_PURGETYPES)       if $jobs_days                 && @jobs_types == 0;
 $edifact_msg_days = DEFAULT_EDIFACT_MSG_PURGEDAYS   if defined($edifact_msg_days) && $edifact_msg_days == 0;
+$logs_batch       = DEFAULT_LOGS_BATCHSIZE          if defined($logs_batch)       && $logs_batch == 0;
 
 # Choose the number of days at which to purge unaccepted list invites:
 # - DAYS defined in the list-invites parameter is prioritised first
@@ -408,9 +414,24 @@ if ($pLogs) {
         $log_query .= " AND action IN (" . join( ',', ('?') x @log_actions ) . ")";
         push @query_params, @log_actions;
     }
+    if ($logs_batch) {
+        $log_query .= " LIMIT ?";
+        push @query_params, $logs_batch;
+    }
     $sth = $dbh->prepare($log_query);
     if ($confirm) {
-        $sth->execute( $pLogs, @query_params ) or die $dbh->errstr;
+        if ($logs_batch) {
+            my $counter = 0;
+            my $rows    = 0;
+            while ( $counter <= $rows ) {
+                $sth->execute( $pLogs, @query_params ) or die $dbh->errstr;
+                sleep 3;
+                $rows    += $sth->rows;
+                $counter += $logs_batch;
+            }
+        } else {
+            $sth->execute( $pLogs, @query_params ) or die $dbh->errstr;
+        }
     }
     print "Done with purging action_logs.\n" if $verbose;
 }
-- 
2.45.1