[Koha-bugs] [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

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=35552

Igor Baptista da Costa <igor.baptista at neki-it.com.br> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |igor.baptista at neki-it.com.b
                   |                            |r

--- Comment #4 from Igor Baptista da Costa <igor.baptista at 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_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.

-- 
You are receiving this mail because:
You are watching all bug changes.
You are the assignee for the bug.