From 1e66595092beb7d1c3497986eb3fd1f478821617 Mon Sep 17 00:00:00 2001 From: Josef Moravec Date: Tue, 13 Mar 2018 20:50:03 +0000 Subject: [PATCH] Bug 18606: (Follow-up) Remove lines from collections_tracking referencing non existant objects --- installer/data/mysql/atomicupdate/rotatingCollections.perl | 9 +++++++-- 1 file changed, 7 insertions(+), 2 deletions(-) diff --git a/installer/data/mysql/atomicupdate/rotatingCollections.perl b/installer/data/mysql/atomicupdate/rotatingCollections.perl index 24c2324..b42b027 100644 --- a/installer/data/mysql/atomicupdate/rotatingCollections.perl +++ b/installer/data/mysql/atomicupdate/rotatingCollections.perl @@ -1,7 +1,12 @@ $DBversion = 'XXX'; # will be replaced by RM if( CheckVersion( $DBversion ) ) { - $dbh->do( "ALTER TABLE collections_tracking ADD CONSTRAINT collections_tracking_ibfk_1 FOREIGN KEY (colId) REFERENCES collections (colId) ON DELETE CASCADE ON UPDATE CASCADE" ); $dbh->do( "ALTER TABLE collections_tracking ADD CONSTRAINT collections_tracking_ibfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE" ); + # Remove lines referencing non existing collections + $dbh->do( "DELETE FROM collections_tracking WHERE colId NOT IN (SELECT colId FROM collections)" ); + # Remove lines referencing non existing items + $dbh->do( "DELETE FROM collections_tracking WHERE itemnumber NOT IN (SELECT itemnumber FROM items)" ); + $dbh->do( "ALTER TABLE collections_tracking ADD CONSTRAINT collections_tracking_ibfk_1 FOREIGN KEY (colId) REFERENCES collections (colId) ON DELETE CASCADE ON UPDATE CASCADE" ); + $dbh->do( "ALTER TABLE collections_tracking ADD CONSTRAINT collections_tracking_ibfk_2 FOREIGN KEY (itemnumber) REFERENCES items (itemnumber) ON DELETE CASCADE ON UPDATE CASCADE" ); SetVersion( $DBversion ); - print "Upgrade to $DBversion done (Bug XXXXX - Rotating collections objects)\n"; + print "Upgrade to $DBversion done (Bug 18606 - Rotating collections objects)\n"; } -- 2.1.4