In 19.11 and before you can create circ rules for an itemtype, delete the itemtype, and the circ rule remains. This also applies to category codes and branches On upgrade from bug 18936 we insert from issuing into circulation rules, however, if rules exist that violate the new constraints on circulation_rules then the upgrade will fail and rules will not be inserted To test: 1 - Checkout 19.11.x 2 - Add a new itemtype 3 - Define some circ rules for that type and some others 4 - Delete that itemtype 5 - Checkout 20.05.x 6 - updatedatabase 7 - See errors: DBD::mysql::db do failed: Cannot add or update a child row: a foreign key constraint fails (`koha_kohadev`.`circulation_rules`, CONSTRAINT `circ_rules_ibfk_3` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE) [for Statement " INSERT INTO circulation_rules ( categorycode, branchcode, itemtype, rule_name, rule_value ) SELECT IF(categorycode='*', NULL, categorycode), IF(branchcode='*', NULL, branchcode), IF(itemtype='*', NULL, itemtype), 'restrictedtype', COALESCE( restrictedtype, '' ) FROM issuingrules "] at /kohadevbox/koha/installer/data/mysql/updatedatabase.pl line 20712.
Created attachment 112935 [details] [review] Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules
I did not test the patch but it should fix the issue you reported.
Created attachment 112949 [details] [review] Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules To test: 1 - Checkout 19.11.x and reset_all 2 - Add a new patron category, itemtype, and branch to your system 3 - Create issuing rules using thise 3 values, and others 4 - Delete the new category, itemtype and branch 5 - Checkout master 6 - Restart_all 7 - updatedatabase 8 - you get errors 9 - repeat 1-4 10 - apply patch 11 - restart_all 12 - updatedatabase 13 - no errors! Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
These delete statements would also delete rules where the value is *. That'd be bad.
Created attachment 112950 [details] [review] Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules UPDATE: changed queries to not touch * values To test: 1 - Checkout 19.11.x and reset_all 2 - Add a new patron category, itemtype, and branch to your system 3 - Create issuing rules using thise 3 values, and others 4 - Delete the new category, itemtype and branch 5 - Checkout master 6 - Restart_all 7 - updatedatabase 8 - you get errors 9 - repeat 1-4 10 - apply patch 11 - restart_all 12 - updatedatabase 13 - no errors! Signed-off-by: Nick Clemens <nick@bywatersolutions.com>
Created attachment 112968 [details] [review] Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules UPDATE: changed queries to not touch * values To test: 1 - Checkout 19.11.x and reset_all 2 - Add a new patron category, itemtype, and branch to your system 3 - Create issuing rules using thise 3 values, and others 4 - Delete the new category, itemtype and branch 5 - Checkout master 6 - Restart_all 7 - updatedatabase 8 - you get errors 9 - repeat 1-5 10 - apply patch 11 - restart_all 12 - updatedatabase 13 - no errors! Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Signed-off-by: David Nind <david@davidnind.com>
Created attachment 113098 [details] [review] Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules UPDATE: changed queries to not touch * values To test: 1 - Checkout 19.11.x and reset_all 2 - Add a new patron category, itemtype, and branch to your system 3 - Create issuing rules using thise 3 values, and others 4 - Delete the new category, itemtype and branch 5 - Checkout master 6 - Restart_all 7 - updatedatabase 8 - you get errors 9 - repeat 1-5 10 - apply patch 11 - restart_all 12 - updatedatabase 13 - no errors! Signed-off-by: Nick Clemens <nick@bywatersolutions.com> Signed-off-by: Andrew Fuerste-Henry <andrew@bywatersolutions.com> Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Good catch guys, logic is sound and testing it locally passed. Passing QA
Pushed to master for 20.11, thanks to everybody involved!
I was just hit by this problem on an upgrade from 19.11 to 20.05.05. So a backport to 20.05.x is definitely needed. Perhaps we could even consider an early release of 20.05.06 or issuing a warning against upgrading to 20.05.05?
backported to 20.05.x for 20.05.06
missing dependencies, not backported to 19.11.x