Bug 22899: (QA follow-up) Ensure constraint is created

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
(cherry picked from commit 49a6aac8b6d8fd13999638f5a6ae4d4481dbf304)
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>

diff --git a/installer/data/mysql/atomicupdate/bug_22899_add_items_constraint_to_tmpholdsqueue.perl b/installer/data/mysql/atomicupdate/bug_22899_add_items_constraint_to_tmpholdsqueue.perl
index 1e14394..79e8f41 100644 (file)
--- a/installer/data/mysql/atomicupdate/bug_22899_add_items_constraint_to_tmpholdsqueue.perl
+++ b/installer/data/mysql/atomicupdate/bug_22899_add_items_constraint_to_tmpholdsqueue.perl
@@ -3,6 +3,11 @@ if( CheckVersion( $DBversion ) ) {
     # you can use $dbh here like:
     unless ( foreign_key_exists( 'tmp_holdsqueue', 'tmp_holdsqueue_ibfk_1' ) ) {
         $dbh->do(q{
+            DELETE t FROM tmp_holdsqueue t
+            LEFT JOIN items i ON t.itemnumber=i.itemnumber
+            WHERE i.itemnumber IS NULL
+        });
+        $dbh->do(q{
             ALTER TABLE tmp_holdsqueue
             ADD CONSTRAINT `tmp_holdsqueue_ibfk_1` FOREIGN KEY (`itemnumber`)
             REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE