From 6d6bfaad30924fc4f8e3287266e5ef996eb9d66b Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Wed, 5 Oct 2022 11:49:56 +0000 Subject: [PATCH] Bug 30571: (follow-up) Additional check at upgrade Content-Type: text/plain; charset=utf-8 Bad data occurs. We are removing null hosts here and update empty syntax or encoding, informing the user. Test plan: Remove the NOT NULL condition if needed. Copy db_revs/220600056.pl to atomicupdate and test dbrev. You might see something like: Upgrade to 22.06.00.056 [11:48:22]: Bug 30571 - Table z3950servers: three cols NOT NULL Found bad data in table z3950servers: removed 1 records with host undefined Corrected empty syntax or encoding for the following hosts. Please check after upgrade. Updated hosts: lx2.loc.gov,lx2.loc.gov Signed-off-by: Marcel de Rooy --- installer/data/mysql/db_revs/220600056.pl | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) diff --git a/installer/data/mysql/db_revs/220600056.pl b/installer/data/mysql/db_revs/220600056.pl index c383322461..d47f2459c2 100755 --- a/installer/data/mysql/db_revs/220600056.pl +++ b/installer/data/mysql/db_revs/220600056.pl @@ -6,6 +6,24 @@ return { up => sub { my ($args) = @_; my ($dbh, $out) = @$args{qw(dbh out)}; + + # Preliminary data checks + my $sql = "SELECT COUNT(*) FROM z3950servers WHERE host IS NULL"; + my ($cnt) = $dbh->selectrow_array($sql); + if( $cnt ) { # No host is really bad data! Remove it. + $dbh->do( "DELETE FROM z3950servers WHERE host IS NULL" ); + say $out "Found bad data in table z3950servers: removed $cnt records with host undefined"; + } + $sql = "SELECT host FROM z3950servers WHERE syntax IS NULL OR encoding IS NULL"; + my $hosts = $dbh->selectcol_arrayref($sql); + if( @$hosts ) { # This is bad data too. We choose a default here. + $dbh->do( q|UPDATE z3950servers SET syntax = COALESCE(syntax, 'USMARC'), encoding = COALESCE(encoding, 'utf8') + WHERE syntax IS NULL OR encoding IS NULL| ); + say $out "Corrected empty syntax or encoding for the following hosts. Please check after upgrade."; + say $out "Updated hosts: ". (join ',', @$hosts); + } + + # Actual dbrev $dbh->do(q{ alter table z3950servers change column `host` `host` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'target''s host name', -- 2.30.2