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: CLOSED 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
Assignee: Nick Clemens (kidclamp)
QA Contact: Testopia
URL:
Keywords:
Depends on: 31086
Blocks:
  Show dependency tree / graph
 
Reported: 2022-10-04 12:25 UTC by mohamed
Modified: 2023-06-08 22:26 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
Circulation function:

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 (kidclamp) 		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 (ashimema) 		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 (tcohen) 		Details | Diff | Splinter Review
Show Obsolete (1) View All Add an attachment (proposed patch, testcase, etc.)

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 (kidclamp) 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 (ashimema) 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 (ashimema) 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 (tcohen) 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 (tcohen) 2022-10-11 12:10:17 UTC 
Pushed to master for 22.11.

Nice work everyone, thanks!
Comment 7 Lucas Gass (lukeg) 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