View | Details | Raw Unified | Return to bug 19016
Collapse All | Expand All

(-)a/misc/cronjobs/cleanup_database.pl (-1 / +129 lines)
Lines 84-89 Usage: $0 [-h|--help] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueu Link Here
84
   --temp-uploads     Delete temporary uploads.
84
   --temp-uploads     Delete temporary uploads.
85
   --temp-uploads-days DAYS Override the corresponding preference value.
85
   --temp-uploads-days DAYS Override the corresponding preference value.
86
   --uploads-missing FLAG Delete upload records for missing files when FLAG is true, count them otherwise
86
   --uploads-missing FLAG Delete upload records for missing files when FLAG is true, count them otherwise
87
   --list-corrupted-data List the corrupted data. A data is considered corrupted if the move of a row from one table to another can lead to data loss (See https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix for more informations)
88
   --fix-corrupted-data Fix the corrupted data by assigning a new id to the rows
87
USAGE
89
USAGE
88
    exit $_[0];
90
    exit $_[0];
89
}
91
}
Lines 109-114 my $special_holidays_days; Link Here
109
my $temp_uploads;
111
my $temp_uploads;
110
my $temp_uploads_days;
112
my $temp_uploads_days;
111
my $uploads_missing;
113
my $uploads_missing;
114
my ( $list_corrupted_data, $fix_corrupted_data );
112
115
113
GetOptions(
116
GetOptions(
114
    'h|help'            => \$help,
117
    'h|help'            => \$help,
Lines 132-137 GetOptions( Link Here
132
    'temp-uploads'      => \$temp_uploads,
135
    'temp-uploads'      => \$temp_uploads,
133
    'temp-uploads-days:i' => \$temp_uploads_days,
136
    'temp-uploads-days:i' => \$temp_uploads_days,
134
    'uploads-missing:i' => \$uploads_missing,
137
    'uploads-missing:i' => \$uploads_missing,
138
    'list-corrupted-data' => \$list_corrupted_data,
139
    'fix-corrupted-data'  => \$fix_corrupted_data,
135
) || usage(1);
140
) || usage(1);
136
141
137
# Use default values
142
# Use default values
Lines 165-170 unless ( $sessions Link Here
165
    || $special_holidays_days
170
    || $special_holidays_days
166
    || $temp_uploads
171
    || $temp_uploads
167
    || defined $uploads_missing
172
    || defined $uploads_missing
173
    || $list_corrupted_data
174
    || $fix_corrupted_data
168
) {
175
) {
169
    print "You did not specify any cleanup work for the script to do.\n\n";
176
    print "You did not specify any cleanup work for the script to do.\n\n";
170
    usage(1);
177
    usage(1);
Lines 336-341 if( defined $uploads_missing ) { Link Here
336
    }
343
    }
337
}
344
}
338
345
346
if ( $list_corrupted_data or $fix_corrupted_data ) {
347
    print "Looking for corrupted data\n" if $verbose;
348
349
    my $RaiseError = $dbh->{RaiseError};
350
    $dbh->{RaiseError} = 1;
351
    my $data = GetCorruptedData();
352
    if (@$data) {
353
        print "Problems found!\n" if $verbose;
354
        TABLES: for my $data (@$data) {
355
            my $col_name      = $data->{col_name};
356
            my $table         = $data->{tables}[0];
357
            my $deleted_table = $data->{tables}[1];
358
            print "\n * Tables $table/$deleted_table: ";
359
            print join( ', ', map { $_->{$col_name} } @{ $data->{rows} } ) . "\n";
360
            if ($fix_corrupted_data) {
361
                FixCorruptedData(
362
                    {
363
                        table         => $table,
364
                        deleted_table => $deleted_table,
365
                        col_name      => $col_name,
366
                        rows          => $data->{rows}
367
                    }
368
                );
369
            }
370
        }
371
    }
372
    else {
373
        print "Everything is clean!\n";
374
    }
375
    $dbh->{RaiseError} = $RaiseError;
376
}
377
339
exit(0);
378
exit(0);
340
379
341
sub RemoveOldSessions {
380
sub RemoveOldSessions {
Lines 444-446 sub DeleteSpecialHolidays { Link Here
444
    my $count = $sth->execute( $days ) + 0;
483
    my $count = $sth->execute( $days ) + 0;
445
    print "Removed $count unique holidays\n" if $verbose;
484
    print "Removed $count unique holidays\n" if $verbose;
446
}
485
}
447
- 
486
487
sub FixCorruptedData {
488
    my ($params)      = @_;
489
    my $table         = $params->{table};
490
    my $deleted_table = $params->{deleted_table};
491
    my $col_name      = $params->{col_name};
492
    my $rows = $params->{rows} || [];
493
    my $schema = Koha::Database->new->schema;
494
    $schema->storage->txn_begin;
495
    my $query_max = qq|
496
        SELECT GREATEST(
497
            COALESCE( ( SELECT MAX($col_name) FROM $table         ), 0 ),
498
            COALESCE( ( SELECT MAX($col_name) FROM $deleted_table ), 0 )
499
        ) + 1 AS max;
500
    |;
501
    my ($max)     = $dbh->selectrow_array($query_max);
502
    my $query_fix = qq|UPDATE $table SET $col_name = ? WHERE $col_name = ?|;
503
    my $sth_fix   = $dbh->prepare($query_fix);
504
    my $everything_went_fine = 1;
505
    ROWS: for my $row ( @{$rows} ) {
506
        my $old_id = $row->{$col_name};
507
        print "Updating $table.$col_name=$old_id with new id $max\n";
508
        eval {
509
            $sth_fix->execute( $max, $old_id );
510
        };
511
        if ($@) {
512
            print "Something went wrong, rolling back!\n";
513
            $schema->storage->txn_rollback;
514
            $everything_went_fine = 0;
515
            last ROWS;
516
        }
517
        $max++;
518
    }
519
    $schema->storage->txn_commit if $everything_went_fine;
520
}
521
522
sub GetCorruptedData {
523
    my $dbh     = C4::Context->dbh;
524
    my $patrons = $dbh->selectall_arrayref(
525
        q|SELECT b.borrowernumber FROM borrowers b JOIN deletedborrowers db ON b.borrowernumber=db.borrowernumber|,
526
        { Slice => {} }
527
    );
528
    my $biblios = $dbh->selectall_arrayref(
529
        q|SELECT b.biblionumber FROM biblio b JOIN deletedbiblio db ON b.biblionumber=db.biblionumber|,
530
        { Slice => {} }
531
    );
532
    my $items = $dbh->selectall_arrayref(
533
        q|SELECT i.itemnumber FROM items i JOIN deleteditems di ON i.itemnumber=di.itemnumber|,
534
        { Slice => {} }
535
    );
536
    my $checkouts = $dbh->selectall_arrayref(
537
        q|SELECT i.issue_id FROM issues i JOIN old_issues oi ON i.issue_id=oi.issue_id|,
538
        { Slice => {} }
539
    );
540
    my $holds = $dbh->selectall_arrayref(
541
        q|SELECT r.reserve_id FROM reserves r JOIN old_reserves o ON r.reserve_id=o.reserve_id|,
542
        { Slice => {} }
543
    );
544
    return [
545
        (
546
            @$patrons
547
            ? {
548
                entity => 'patrons',
549
                col_name => 'borrowernumber',
550
                rows => $patrons,
551
                tables  => [ 'borrowers', 'deletedborrowers' ],
552
              }
553
            : ()
554
        ),
555
        (
556
            @$biblios
557
            ? { entity => 'biblios', col_name => 'borrowernumber', rows => $biblios, tables => [ 'biblio', 'deletedbiblio' ] }
558
            : ()
559
        ),
560
        (
561
            @$items ? { entity => 'items', col_name => 'itemnumber', rows => $items, tables => [ 'items', 'deleteditems' ] }
562
            : ()
563
        ),
564
        (
565
            @$checkouts
566
            ? { entity => 'checkouts', col_name => 'issue_id', rows => $checkouts, tables => [ 'issues', 'old_issues' ] }
567
            : ()
568
        ),
569
        (
570
            @$holds
571
            ? { entity => 'holds', col_name => 'reserve_id', rows => $holds, tables => [ 'reserves', 'old_reserves' ] }
572
            : ()
573
        ),
574
    ];
575
}

Return to bug 19016