[Koha] upgrade error running koha-upgrade-schema

[Paulo Neto]

Hello Jonathan,

Thank you for the quick reply.

The problem was the table engine used, that was diferent on the two tables. For the table authorised_values i had myisam and for illrequests i had InnoDB

In a situation like that you cant create foreign keys between columns of one table, referencing another table.

Changing the database engine of the authorised_values to InnoDB did the trick and now i’ve upgraded the database schema sucessfully.

This can help other people in similar situations because its not na obvious data integrity situation.

Best regards,

Paulo Neto

De: Jonathan Druart <jonathan.druart at bugs.koha-community.org<mailto:jonathan.druart at bugs.koha-community.org>>
Enviada: 6 de junho de 2023 11:06
Para: Paulo Neto <pneto at dgadr.pt<mailto:pneto at dgadr.pt>>
Cc: koha <koha at lists.katipo.co.nz<mailto:koha at lists.katipo.co.nz>>
Assunto: Re: [Koha] upgrade error running koha-upgrade-schema

Hi Paulo,
You have values in illrequests.illrequests_safk that are not defined in the authorised_values values.
Regards,
Jonathan

Le mar. 6 juin 2023 à 11:20, Paulo Neto <pneto at dgadr.pt<mailto:pneto at dgadr.pt>> a écrit :
Hello all,

I've been trying to upgrade my koha 18.11 to version 22.11.

When i run the comand koha-upgrade-schema to upgrade the database schema i am getting this error:

" UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Can't create table `koha_dgadr`.`illrequests` (errno: 150 "Foreign key constraint is incorrectly formed")  at /usr/share/perl5/DBIx/Class/Schema.pm line 1118 "

This is happening on 18.12.00.016 step list in da script updatedatabase.pl<http://updatedatabase.pl> (line17406) Corresponding to this line of code, in my case the  illrequests_safk doesnt existe before running the script:

if ( !foreign_key_exists( 'illrequests', 'illrequests_safk' ) ) {
        $dbh->do( "ALTER TABLE illrequests ADD CONSTRAINT illrequests_safk FOREIGN KEY (status_alias) REFERENCES authorised_values(authorised_value) ON UPDATE CASCADE ON DELETE SET NULL" );
    }

I do have some equal values for the column authorised_value on the table authorised_value and i think its probably the reason it cant create the foreign key on the table illrequests,

Is there a way to correct this?

Yours sincerely,

Paulo Neto
_______________________________________________

Koha mailing list  http://koha-community.org
Koha at lists.katipo.co.nz<mailto:Koha at lists.katipo.co.nz>
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha