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 |
} |