[Bug 35552] New: Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 741
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 Bug ID: 35552 Summary: Cannot change column 'itemnumber': used in a foreign key constraint 'tmp_holdsqueue_ibfk_1' at /usr/share/koha/lib/C4/Installer.pm line 741 Change sponsored?: --- Product: Koha Version: unspecified Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Installation and upgrade (web-based installer) Assignee: koha-bugs@lists.koha-community.org Reporter: vctrzuniga@gmail.com QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com 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) ====== -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 --- Comment #1 from Victor Zuniga <vctrzuniga@gmail.com> --- 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> ============= -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 Victor Zuniga <vctrzuniga@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Priority|P5 - low |P1 - high Severity|enhancement |major -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 --- Comment #2 from Victor Zuniga <vctrzuniga@gmail.com> --- 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 -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |RM_priority -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 --- Comment #3 from Victor Zuniga <vctrzuniga@gmail.com> --- 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 -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 Igor Baptista da Costa <igor.baptista@neki-it.com.br> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |igor.baptista@neki-it.com.b | |r --- Comment #4 from Igor Baptista da Costa <igor.baptista@neki-it.com.br> --- 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_... 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 --- Comment #5 from Victor Zuniga <vctrzuniga@gmail.com> --- Hi Igor, This did the trick. Thanks so much for your help! Victor -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 Victor Zuniga <vctrzuniga@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |hbala@ou.ac.lk --- Comment #6 from Victor Zuniga <vctrzuniga@gmail.com> --- *** Bug 35522 has been marked as a duplicate of this bug. *** -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 Mohd Hafiz Yusoff <mhby87@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mhby87@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |fridolin.somers@biblibre.co | |m --- Comment #7 from Fridolin Somers <fridolin.somers@biblibre.com> --- Duplicate of Bug 35687 ? -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=35687 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |RESOLVED See Also|https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=35687 | CC| |jonathan.druart+koha@gmail. | |com Resolution|--- |DUPLICATE --- Comment #8 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- *** This bug has been marked as a duplicate of bug 35687 *** -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|RM_priority | -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552 --- Comment #9 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Can you report on bug 35687 which version of MySQL/MariaDB you are using please? -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org