From 184eb9f629f0d4e7ffa20d130a0d74df33790493 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Tue, 1 Aug 2017 14:14:13 -0300 Subject: [PATCH] Bug 19016: Add a script to fix corrupted data This patch add two new options to the cleanup_database.pl script: * --list-corrupted-data to list the different rows that are affected * --fix-corrupted-data to fix and reassign an id to the corrupted rows TO NOT USE IT IN PRODUCTION YET! --- misc/cronjobs/cleanup_database.pl | 129 ++++++++++++++++++++++++++++++++++++++ 1 file changed, 129 insertions(+) diff --git a/misc/cronjobs/cleanup_database.pl b/misc/cronjobs/cleanup_database.pl index 03c382d470..df3c4e99b9 100755 --- a/misc/cronjobs/cleanup_database.pl +++ b/misc/cronjobs/cleanup_database.pl @@ -84,6 +84,8 @@ Usage: $0 [-h|--help] [--sessions] [--sessdays DAYS] [-v|--verbose] [--zebraqueu --temp-uploads Delete temporary uploads. --temp-uploads-days DAYS Override the corresponding preference value. --uploads-missing FLAG Delete upload records for missing files when FLAG is true, count them otherwise + --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) + --fix-corrupted-data Fix the corrupted data by assigning a new id to the rows USAGE exit $_[0]; } @@ -109,6 +111,7 @@ my $special_holidays_days; my $temp_uploads; my $temp_uploads_days; my $uploads_missing; +my ( $list_corrupted_data, $fix_corrupted_data ); GetOptions( 'h|help' => \$help, @@ -132,6 +135,8 @@ GetOptions( 'temp-uploads' => \$temp_uploads, 'temp-uploads-days:i' => \$temp_uploads_days, 'uploads-missing:i' => \$uploads_missing, + 'list-corrupted-data' => \$list_corrupted_data, + 'fix-corrupted-data' => \$fix_corrupted_data, ) || usage(1); # Use default values @@ -165,6 +170,8 @@ unless ( $sessions || $special_holidays_days || $temp_uploads || defined $uploads_missing + || $list_corrupted_data + || $fix_corrupted_data ) { print "You did not specify any cleanup work for the script to do.\n\n"; usage(1); @@ -336,6 +343,38 @@ if( defined $uploads_missing ) { } } +if ( $list_corrupted_data or $fix_corrupted_data ) { + print "Looking for corrupted data\n" if $verbose; + + my $RaiseError = $dbh->{RaiseError}; + $dbh->{RaiseError} = 1; + my $data = GetCorruptedData(); + if (@$data) { + print "Problems found!\n" if $verbose; + TABLES: for my $data (@$data) { + my $col_name = $data->{col_name}; + my $table = $data->{tables}[0]; + my $deleted_table = $data->{tables}[1]; + print "\n * Tables $table/$deleted_table: "; + print join( ', ', map { $_->{$col_name} } @{ $data->{rows} } ) . "\n"; + if ($fix_corrupted_data) { + FixCorruptedData( + { + table => $table, + deleted_table => $deleted_table, + col_name => $col_name, + rows => $data->{rows} + } + ); + } + } + } + else { + print "Everything is clean!\n"; + } + $dbh->{RaiseError} = $RaiseError; +} + exit(0); sub RemoveOldSessions { @@ -444,3 +483,93 @@ sub DeleteSpecialHolidays { my $count = $sth->execute( $days ) + 0; print "Removed $count unique holidays\n" if $verbose; } + +sub FixCorruptedData { + my ($params) = @_; + my $table = $params->{table}; + my $deleted_table = $params->{deleted_table}; + my $col_name = $params->{col_name}; + my $rows = $params->{rows} || []; + my $schema = Koha::Database->new->schema; + $schema->storage->txn_begin; + my $query_max = qq| + SELECT GREATEST( + COALESCE( ( SELECT MAX($col_name) FROM $table ), 0 ), + COALESCE( ( SELECT MAX($col_name) FROM $deleted_table ), 0 ) + ) + 1 AS max; + |; + my ($max) = $dbh->selectrow_array($query_max); + my $query_fix = qq|UPDATE $table SET $col_name = ? WHERE $col_name = ?|; + my $sth_fix = $dbh->prepare($query_fix); + my $everything_went_fine = 1; + ROWS: for my $row ( @{$rows} ) { + my $old_id = $row->{$col_name}; + print "Updating $table.$col_name=$old_id with new id $max\n"; + eval { + $sth_fix->execute( $max, $old_id ); + }; + if ($@) { + print "Something went wrong, rolling back!\n"; + $schema->storage->txn_rollback; + $everything_went_fine = 0; + last ROWS; + } + $max++; + } + $schema->storage->txn_commit if $everything_went_fine; +} + +sub GetCorruptedData { + my $dbh = C4::Context->dbh; + my $patrons = $dbh->selectall_arrayref( + q|SELECT b.borrowernumber FROM borrowers b JOIN deletedborrowers db ON b.borrowernumber=db.borrowernumber|, + { Slice => {} } + ); + my $biblios = $dbh->selectall_arrayref( + q|SELECT b.biblionumber FROM biblio b JOIN deletedbiblio db ON b.biblionumber=db.biblionumber|, + { Slice => {} } + ); + my $items = $dbh->selectall_arrayref( + q|SELECT i.itemnumber FROM items i JOIN deleteditems di ON i.itemnumber=di.itemnumber|, + { Slice => {} } + ); + my $checkouts = $dbh->selectall_arrayref( + q|SELECT i.issue_id FROM issues i JOIN old_issues oi ON i.issue_id=oi.issue_id|, + { Slice => {} } + ); + my $holds = $dbh->selectall_arrayref( + q|SELECT r.reserve_id FROM reserves r JOIN old_reserves o ON r.reserve_id=o.reserve_id|, + { Slice => {} } + ); + return [ + ( + @$patrons + ? { + entity => 'patrons', + col_name => 'borrowernumber', + rows => $patrons, + tables => [ 'borrowers', 'deletedborrowers' ], + } + : () + ), + ( + @$biblios + ? { entity => 'biblios', col_name => 'borrowernumber', rows => $biblios, tables => [ 'biblio', 'deletedbiblio' ] } + : () + ), + ( + @$items ? { entity => 'items', col_name => 'itemnumber', rows => $items, tables => [ 'items', 'deleteditems' ] } + : () + ), + ( + @$checkouts + ? { entity => 'checkouts', col_name => 'issue_id', rows => $checkouts, tables => [ 'issues', 'old_issues' ] } + : () + ), + ( + @$holds + ? { entity => 'holds', col_name => 'reserve_id', rows => $holds, tables => [ 'reserves', 'old_reserves' ] } + : () + ), + ]; +} -- 2.11.0