Bug 30276

Summary: Web-based installer failing on db upgrade for 30060
Product: Koha Reporter: Magnus Enger <magnus>
Component: Installation and upgrade (web-based installer)Assignee: Jonathan Druart <jonathan.druart>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: blocker    
Priority: P5 - low CC: fridolin.somers, gmcharlt, jonathan.druart, kyle, sonia.bouis
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=30060
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
22.05.00
Bug Depends on: 30060    
Bug Blocks:    
Attachments: Bug 30276: (bug 30060 follow-up) Fix 21.12.00.016

Description Magnus Enger 2022-03-11 08:43:05 UTC
Trying to upgrade a gitified test-install to current master, I get this error:

Upgrade to 21.12.00.016 [09:27:15]: Bug 30060 - Update user_permissions to add primary key and remove null option from code column
ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'code': used in a foreign key constraint 'user_permissions_ibfk_2' at /home/magnus/kohaclone-sv/C4/Installer.pm line 738
Dropped any previously created primary key
ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'code': used in a foreign key constraint 'user_permissions_ibfk_2' at /home/magnus/kohaclone-sv/C4/Installer.pm line 738
Removed any duplicate rows
ERROR: {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'code': used in a foreign key constraint 'user_permissions_ibfk_2' at /home/magnus/kohaclone-sv/C4/Installer.pm line 738

The table looks like this:

MariaDB [koha_koha]> show create table user_permissions\G
*************************** 1. row ***************************
       Table: user_permissions
Create Table: CREATE TABLE `user_permissions` (
  `borrowernumber` int(11) NOT NULL DEFAULT '0',
  `module_bit` int(11) NOT NULL DEFAULT '0',
  `code` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  KEY `user_permissions_ibfk_1` (`borrowernumber`),
  KEY `user_permissions_ibfk_2` (`module_bit`,`code`),
  CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`module_bit`, `code`) REFERENCES `permissions` (`module_bit`, `code`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

MariaDB [koha_koha]> describe user_permissions;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| borrowernumber | int(11)     | NO   | MUL | 0       |       |
| module_bit     | int(11)     | NO   | MUL | 0       |       |
| code           | varchar(64) | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

And it is currently empty:

MariaDB [koha_sverige]> select * from user_permissions;
Empty set (0.00 sec)
Comment 1 Magnus Enger 2022-03-11 08:57:08 UTC
user_permissions was empty before the upgrade too. Guess this test install only has superlibrarians, no detailed permissions.
Comment 2 Jonathan Druart 2022-03-21 10:59:15 UTC
MariaDB [koha_kohadev]> ALTER TABLE user_permissions DROP INDEX IF EXISTS `PRIMARY`;
Query OK, 0 rows affected, 1 warning (0.000 sec)
Records: 0  Duplicates: 0  Warnings: 1

MariaDB [koha_kohadev]> show warnings;
+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Note  | 1091 | Can't DROP 'PRIMARY'; check that column/key exists |
+-------+------+----------------------------------------------------+
1 row in set (0.000 sec)
Comment 3 Jonathan Druart 2022-03-28 12:59:09 UTC
Created attachment 132326 [details] [review]
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
Comment 4 Fridolin Somers 2022-03-28 14:09:35 UTC
Pushed to master for 22.05, thanks to everybody involved 🦄