From 1e66595092beb7d1c3497986eb3fd1f478821617 Mon Sep 17 00:00:00 2001
From: Josef Moravec <josef.moravec@gmail.com>
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