Bug 31673 - DB update of bug 31086 fails: Cannot change column 'branchcode': used in a foreign key constraint
Summary: DB update of bug 31086 fails: Cannot change column 'branchcode': used in a fo...
Status: RESOLVED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (command-line installer) (show other bugs)
Version: 22.05
Hardware: All All
: P5 - low blocker (vote)
Assignee: Nick Clemens
QA Contact: Testopia
URL:
Keywords:
Depends on: 31086
Blocks:
  Show dependency treegraph
 
Reported: 2022-10-04 12:25 UTC by mohamed
Modified: 2022-10-23 09:59 UTC (History)
5 users (show)

See Also:
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


Attachments
Bug 31673: (bug 31086 follow-up) Drop and constraint to allow updating column (1.64 KB, patch)
2022-10-05 10:18 UTC, Nick Clemens
Details | Diff | Splinter Review
Bug 31673: (bug 31086 follow-up) Drop and constraint to allow updating column (1.70 KB, patch)
2022-10-07 14:45 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 31673: (QA follow-up) Check if FK exists before attempt to remove (1.11 KB, patch)
2022-10-11 12:09 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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