From 563836cce11f616b3053ca75bf7820b166a6be45 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 28 Mar 2022 14:01:26 +0200 Subject: [PATCH] Bug 30276: (bug 30060 follow-up) Fix 21.12.00.016 ERROR 1832 (HY000): Cannot change column 'code': used in a foreign key constraint 'user_permissions_ibfk_2' Test plan: Checkout a commit prior to 30060 (c023f63abcce53d71c9492aea4bc7aa30fdc5d73) reset_all checkout master+this patch updatedatabase Signed-off-by: Fridolin Somers --- installer/data/mysql/db_revs/211200016.pl | 9 +++++++++ 1 file changed, 9 insertions(+) diff --git a/installer/data/mysql/db_revs/211200016.pl b/installer/data/mysql/db_revs/211200016.pl index bd350b89be..7f2c173d05 100755 --- a/installer/data/mysql/db_revs/211200016.pl +++ b/installer/data/mysql/db_revs/211200016.pl @@ -21,9 +21,18 @@ return { $dbh->do(q{ALTER TABLE user_permissions DROP COLUMN temp}); say $out "Removed any duplicate rows"; + if ( foreign_key_exists('user_permissions', 'user_permissions_ibfk_2') ) { + $dbh->do(q{ + ALTER TABLE user_permissions DROP FOREIGN KEY user_permissions_ibfk_2 + }); + } $dbh->do(q{ ALTER TABLE user_permissions ADD CONSTRAINT PK_borrowernumber_module_code PRIMARY KEY (borrowernumber,module_bit,code) }); + $dbh->do(q{ + ALTER TABLE user_permissions + ADD CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`) ON DELETE CASCADE ON UPDATE CASCADE; + }); say $out "Added a primary key on user_permissions on borrowernumber, module_bit, code"; } }, -- 2.20.1