Bug 31673

Summary: DB update of bug 31086 fails: Cannot change column 'branchcode': used in a foreign key constraint
Product: Koha Reporter: mohamed <mohamednofal909>
Component: Installation and upgrade (command-line installer)Assignee: Nick Clemens <nick>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: blocker    
Priority: P5 - low CC: keith, lucas, martin.renvoize, nick, victor
Version: 22.05   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
22.11.00,22.05.06,21.11.12
Bug Depends on: 31086    
Bug Blocks:    
Attachments: Bug 31673: (bug 31086 follow-up) Drop and constraint to allow updating column
Bug 31673: (bug 31086 follow-up) Drop and constraint to allow updating column
Bug 31673: (QA follow-up) Check if FK exists before attempt to remove

Description mohamed 2022-10-04 12:25:36 UTC
Upgrade to 22.05.04.002 [14:07:01]: Bug 31086 - Do not allow null values in branchcodes for reserves
ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'branchcode': used in a foreign key constraint 'reserves_ibfk_4' at /usr/share/koha/lib/C4/Installer.pm line 739
Comment 1 Nick Clemens 2022-10-05 10:18:05 UTC
Created attachment 141336 [details] [review]
Bug 31673: (bug 31086 follow-up) Drop and constraint to allow updating column

It seems that we cannot edit the column while it has a constraint.
If we remove it, update the oclumn, then re-add it, the update succeeds
Comment 2 Keith Sorbo 2022-10-05 14:19:26 UTC
To help those who don't use diff, I have extracted the relevant sql to the following:

ALTER TABLE reserves DROP FOREIGN KEY reserves_ibfk_4; 
ALTER TABLE reserves MODIFY COLUMN `branchcode` VARCHAR(10) COLLATE utf8mb4_unicode_ci NOT NULL;
ALTER TABLE reserves ADD CONSTRAINT reserves_ibfk_4 FOREIGN KEY (branchcode) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE;
Comment 3 Martin Renvoize 2022-10-07 14:44:38 UTC
This works.. I'm just marking it PQA as people don't appear to want to put their names to it.

SO + QA Done
Comment 4 Martin Renvoize 2022-10-07 14:45:37 UTC
Created attachment 141512 [details] [review]
Bug 31673: (bug 31086 follow-up) Drop and constraint to allow updating column

It seems that we cannot edit the column while it has a constraint.
If we remove it, update the oclumn, then re-add it, the update succeeds

Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 5 Tomás Cohen Arazi 2022-10-11 12:09:40 UTC
Created attachment 141599 [details] [review]
Bug 31673: (QA follow-up) Check if FK exists before attempt to remove

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 6 Tomás Cohen Arazi 2022-10-11 12:10:17 UTC
Pushed to master for 22.11.

Nice work everyone, thanks!
Comment 7 Lucas Gass 2022-10-14 21:47:49 UTC
Backported to 22.05.x for upcoming 22.05.06 release
Comment 8 Victor Grousset/tuxayo 2022-10-21 22:27:15 UTC
Missing dependencies for 21.05.x, it shouldn't be affected, no backport.

Dodged a bullet again :P
Comment 9 Arthur Suzuki 2022-10-22 22:10:49 UTC
Thanks!

Pushed to 21.11 for 21.11.12