Bug 30276 - Web-based installer failing on db upgrade for 30060
Summary: Web-based installer failing on db upgrade for 30060
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (web-based installer) (show other bugs)
Version: Main
Hardware: All All
: P5 - low blocker (vote)
Assignee: Jonathan Druart
QA Contact: Testopia
URL:
Keywords:
Depends on: 30060
Blocks:
  Show dependency treegraph
 
Reported: 2022-03-11 08:43 UTC by Magnus Enger
Modified: 2022-12-12 21:23 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.05.00


Attachments
Bug 30276: (bug 30060 follow-up) Fix 21.12.00.016 (1.75 KB, patch)
2022-03-28 12:59 UTC, Jonathan Druart
Details | Diff | Splinter Review

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