Bug 35552 - Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 741
Summary: Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_hold...
Status: RESOLVED DUPLICATE of bug 35687
Alias: None
Product: Koha
Classification: Unclassified
Component: Installation and upgrade (web-based installer) (show other bugs)
Version: unspecified
Hardware: All All
: P1 - high major (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
: 35522 (view as bug list)
Depends on:
Blocks:
 
Reported: 2023-12-12 18:52 UTC by Victor Zuniga
Modified: 2024-01-11 14:29 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Victor Zuniga 2023-12-12 18:52:43 UTC
Hello,

This issue started last night during the final steps of upgrading from 23.06 to 23.11. Upon following the advice from Emmi Takkinen (See ticket #35543 for reference) I ran into this issue dealing with the foreign key constraint 'tmp_holdsqueue_ibfk_1'.

I ran again the update and upgrade commands this morning. Here is the output I am getting:


======


Setting up koha-common (23.11.00-1) ...
Installing new version of config file /etc/koha/apache-shared-intranet-plack.conf ...
Installing new version of config file /etc/koha/apache-shared-intranet.conf ...
Installing new version of config file /etc/koha/apache-site-https.conf.in ...
Installing new version of config file /etc/koha/koha-conf-site.xml.in ...
Installing new version of config file /etc/koha/zebradb/marc_defs/marc21/biblios/biblio-koha-indexdefs.xml ...
Installing new version of config file /etc/koha/zebradb/marc_defs/marc21/biblios/biblio-zebra-indexdefs.xsl ...
Upgrading database schema for catalog
Upgrade to 23.06.00.013  [10:26:04]: Bug 28966 - Holds queue view too slow to load for large numbers of holds
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 741

dpkg: error processing package koha-common (--configure):
 installed koha-common package post-installation script subprocess returned error exit status 1


E: Sub-process /usr/bin/dpkg returned an error code (1)

======
Comment 1 Victor Zuniga 2023-12-12 18:53:41 UTC
When I run the "show create table tmp_holdsqueue", this is what I get:


============



mysql> show create table tmp_holdsqueue;
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tmp_holdsqueue | CREATE TABLE `tmp_holdsqueue` (
  `biblionumber` int DEFAULT NULL,
  `itemnumber` int DEFAULT NULL,
  `barcode` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `surname` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `firstname` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `phone` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `borrowernumber` int NOT NULL,
  `cardnumber` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reservedate` date DEFAULT NULL,
  `title` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `itemcallnumber` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `holdingbranch` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `pickbranch` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `item_level_request` tinyint NOT NULL DEFAULT '0',
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY `tmp_holdsqueue_ibfk_1` (`itemnumber`),
  KEY `tmp_holdsqueue_ibfk_2` (`biblionumber`),
  KEY `tmp_holdsqueue_ibfk_3` (`borrowernumber`),
  CONSTRAINT `tmp_holdsqueue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tmp_holdsqueue_ibfk_2` FOREIGN KEY (`biblionumber`) REFERENCES `biblio` (`biblionumber`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tmp_holdsqueue_ibfk_3` FOREIGN KEY (`borrowernumber`) REFERENCES `borrowers` (`borrowernumber`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>



=============
Comment 2 Victor Zuniga 2023-12-13 19:13:50 UTC
Just wanted to check in on this ticket.

Any help on solving this issue would be greatly appreciated. This is preventing us from completing the upgrade and accessing the public/staff sections of the system.

Thanks,

Victor
Comment 3 Victor Zuniga 2023-12-14 22:44:04 UTC
Hello,

 Checking on this again. Is there a way to fix this issue from the command line?

I ran into a previous issue (Bugzilla ticket #35543) and was able to fix it after getting some pointers from Emmi Takkinen.

Is there a way to modify the foreign key (tmp_holdsqueue_ibfk_1) so it can allow the upgrade to be completed?

Thanks,

Victor
Comment 4 Igor Baptista da Costa 2023-12-23 01:27:12 UTC
Hello Victor,

I was facing the same error when updating koha from version 20.11 to 23.11 and was able to solve it.

The error:
ERROR - {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 741

happens because the file of database patch that fix the "Bug 28966 - Holds queue view too slow to load for large numbers of holds", of the koha version 23.06.00.013, tries to run an alter statment that change the column "itemnumber" to primary key, but this column already is a foreign key, so this alter statment fails. 

(the file that im talking about: 
https://github.com/Koha-Community/Koha/blob/23.11.x/installer/data/mysql/db_revs/230600013.pl

in your server if the koha was installed from the package manager using koha-common the path for this file is: /usr/share/koha/intranet/cgi-bin/installer/data/mysql/db_revs/230600013.pl)

So, I changed this file to before run the alter statment that turns the column "itemnumber" into primary key, I added some code to drop the foreign key "tmp_holdsqueue_ibfk_1" (that refers to the "itemnumber" column) and it index, and after that run the alter statment, and after the alter statment it recreates the foreign key. This is what the code looks like:

use Modern::Perl;

return {
    bug_number  => "28966",
    description => "Holds queue view too slow to load for large numbers of holds",
    up          => sub {
        my ($args) = @_;
        my ( $dbh, $out ) = @$args{qw(dbh out)};

        unless ( primary_key_exists( 'tmp_holdsqueue', 'itemnumber' ) ) {

            if (foreign_key_exists('tmp_holdsqueue', 'tmp_holdsqueue_ibfk_1')) {
                    $dbh->do(
                        q{ALTER TABLE tmp_holdsqueue DROP FOREIGN KEY tmp_holdsqueue_ibfk_1}
                    );
                    $dbh->do(
                        q{ALTER TABLE tmp_holdsqueue DROP INDEX tmp_holdsqueue_ibfk_1}
                    );
            }

            $dbh->do(
                q{ALTER TABLE tmp_holdsqueue ADD PRIMARY KEY (itemnumber)}
            );

            $dbh->do(
                q{ALTER TABLE tmp_holdsqueue ADD KEY `tmp_holdsqueue_ibfk_1` (`itemnumber`), ADD CONSTRAINT `tmp_holdsqueue_ibfk_1` FOREIGN KEY (`itemnumber`) REFERENCES `items` (`itemnumber`) ON DELETE CASCADE ON UPDATE CASCADE}
            );
        }

        say $out "Set primary key for table 'tmp_holdsqueue' to 'itemnumber'";
    },
};

Regards,
Igor Baptista from Neki-it.
Comment 5 Victor Zuniga 2023-12-26 19:49:46 UTC
Hi Igor,

This did the trick. Thanks so much for your help!

Victor
Comment 6 Victor Zuniga 2023-12-26 19:51:06 UTC
*** Bug 35522 has been marked as a duplicate of this bug. ***
Comment 7 Fridolin Somers 2024-01-08 14:42:58 UTC
Duplicate of Bug 35687 ?
Comment 8 Jonathan Druart 2024-01-11 14:21:19 UTC

*** This bug has been marked as a duplicate of bug 35687 ***
Comment 9 Jonathan Druart 2024-01-11 14:29:21 UTC
Can you report on bug 35687 which version of MySQL/MariaDB you are using please?