Bug 26911

Summary: Update for 18936 can cause data loss if constraints are violated
Product: Koha Reporter: Nick Clemens (kidclamp) <nick>
Component: Architecture, internals, and plumbingAssignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: critical    
Priority: P5 - low CC: aleisha, andrewfh, jonathan.druart, lucas, magnus, martin.renvoize
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=26910
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
20.11.00, 20.05.06
Bug Depends on: 18936    
Bug Blocks:    
Attachments: Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules
Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules
Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules
Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules
Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules

Description Nick Clemens (kidclamp) 2020-11-03 15:33:46 UTC
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.
Comment 1 Jonathan Druart 2020-11-03 15:46:30 UTC
Created attachment 112935 [details] [review]
Bug 26911: (bug 18936 follow-up) Remove invalid data before copying the circ rules
Comment 2 Jonathan Druart 2020-11-03 15:47:03 UTC
I did not test the patch but it should fix the issue you reported.
Comment 3 Nick Clemens (kidclamp) 2020-11-03 18:39:31 UTC
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>
Comment 4 Andrew Fuerste-Henry 2020-11-03 19:26:59 UTC
These delete statements would also delete rules where the value is *. That'd be bad.
Comment 5 Andrew Fuerste-Henry 2020-11-03 20:13:28 UTC
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>
Comment 6 David Nind 2020-11-04 00:25:38 UTC
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>
Comment 7 Martin Renvoize 2020-11-05 12:51:52 UTC
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>
Comment 8 Martin Renvoize 2020-11-05 12:52:26 UTC
Good catch guys, logic is sound and testing it locally passed.

Passing QA
Comment 9 Jonathan Druart 2020-11-05 14:25:41 UTC
Pushed to master for 20.11, thanks to everybody involved!
Comment 10 Magnus Enger 2020-11-06 08:33:13 UTC
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?
Comment 11 Lucas Gass 2020-11-16 21:51:40 UTC
backported to 20.05.x for 20.05.06
Comment 12 Aleisha Amohia 2020-11-17 05:31:53 UTC
missing dependencies, not backported to 19.11.x