From fa96485296b2d3bf9a5f2bea73b811a023bc5e6f Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Tue, 22 Oct 2019 12:42:09 +0200 Subject: [PATCH] Bug 23813: (bug 18925 follow-up) Remove invalid circ rules to avoid FK constraints to fail There is not FK constraints on the itemtype, categorycode and branchcode columns of the issuingrules table, because of the '*' value we use for default. We created the new table circulation_rules with those constraints, and when we moved the rules for maxissueqty, the INSERT failed. To avoid the FK constraints to fail we are going to delete the invalid rows before. It could lead to data lost, but this situation certainly comes from a bad data manipulation at some point of the history of the installation. Test plan: git reset --hard 1e4f442442844cfaf17f5b06f122f6259d5fee92 # before 18.12.00.020 reset_all insert into branches(branchcode, branchname) values('rmme', 'rmme'); insert into categories (categorycode, description) values ('rmme', 'rmme'); insert into itemtypes (itemtype, description) values('rmme', 'rmme'); Go to http://pro.kohadev.org/cgi-bin/koha/admin/smart-rules.pl Create a new rule for the patron category Create a new rule for the item type Copy all the default rule to the new library Create other rules for "Default checkout, hold policy by patron category" and "Default holds policy by item type" using this patron's category and itemtype. Same for patrons' categories and itemtypes you will not delete. delete from branches where branchcode="rmme"; delete from categories where categorycode="rmme"; delete from itemtypes where itemtype="rmme"; git checkout master and execute the DB process. => With this patch applied you will not get the error and the rules will be moved correctly. Signed-off-by: Martin Renvoize (cherry picked from commit ff3d7b9f8e27a1c8547012f062daccc33b12dc27) Signed-off-by: Fridolin Somers --- installer/data/mysql/updatedatabase.pl | 11 +++++++++++ 1 file changed, 11 insertions(+) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index ffee2429fb..2d3754ad86 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -17542,6 +17542,17 @@ if( CheckVersion( $DBversion ) ) { } if ( column_exists( 'issuingrules', 'maxissueqty' ) ) { + # Cleaning invalid rules before, to avoid FK contraints to fail + $dbh->do(q| + DELETE FROM issuingrules WHERE categorycode != '*' AND categorycode NOT IN (SELECT categorycode FROM categories); + |); + $dbh->do(q| + DELETE FROM issuingrules WHERE branchcode != '*' AND branchcode NOT IN (SELECT branchcode FROM branches); + |); + $dbh->do(q| + DELETE FROM issuingrules WHERE itemtype != '*' AND itemtype NOT IN (SELECT itemtype FROM itemtypes); + |); + $dbh->do(" INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value ) SELECT IF(categorycode='*', NULL, categorycode), -- 2.39.5