You could try "SET GLOBAL foreign_key_checks = 0".
You could also try adding `$dbh->{AutoCommit} = 0;` to see if that will
keep the foreign_key_checks setting set. That shouldn't really change
anything though.
I assume you are using the `koha-upgrade-schema` script to do this; that
should be the way to go here.
I don't know what to do beyond that. Maybe someone else on the mailing
list has better ideas.
On Mon, May 12, 2025 at 11:55 AM Philippe Blouin <
philippe.blo...@inlibro.com> wrote:
> Hi Michael,
>
> Ya, the "a lot of work" is not an option, this is an hourly-rate contract.
>
> I've update tons of database over that version, and this never occured.
> And I've tried manually removing the constraint, and it failed on the next
> one, alphabetically.
>
> I've tried a few hack to put more "SET foreign_key_checks = 0" everywhere,
> but this clearly has no effect.
>
> Note: "type" is content, it's not the type of db column, but the name of
> the column account_offsets.type which is an enum.
>
>
> [image: Logo inLibro] <https://inLibro.com> Philippe Blouin
> Directeur de la technologie
>
> T 833-INLIBRO (465-4276) <833-465-4276>, poste 230
> C philippe.blo...@inlibro.com
>
> www.inLibro.com <https://inLibro.com>
> On 2025-05-12 12:40, Michael Hafen wrote:
>
> Yes, that update turns off the foreign key checks for the duration, but
> I'm not sure that that would affect the column type, I think it only
> affects column content checks. But I could be wrong.
> This update does more than add the collate I think. It does check the
> collate to see if the update has already been applied, but it also changes
> the column data/character type from utf8-mb3 to utf8-mb4 (from 3 byte
> characters to 4 byte characters) (at least I think it was utf8-mb3 before
> this).
> I'm surprised that there is only one error, but it may be that this is the
> first table being tried.
> Quick google search shows what I expected, which is a recommendation to
> drop all foreign key constraints, and then add them all back once all the
> alter table statements have executed. However, I'm surprised that it is
> needed. I've done this same operation on several mysql databases with no
> foreign key errors. The MariaDB website doesn't show any significant
> changes to how that variable operates since 10.3 (
> https://mariadb.com/docs/server/ref/mdb/system-variables/foreign_key_checks/).
> I expect it to just work. Makes me wonder if the database connection is
> getting reset after the 'SET' statement. Maybe it needs to be 'SET GLOBAL
> ...' or maybe make sure autocommit is off?
> Frankly I'm at a loss. I'd hate to say 'just drop all the foreign keys
> and re-create them after the update.' That seems like a lot of work; there
> should be an easier way to get this to go.
>
>
> On Mon, May 12, 2025 at 8:16 AM Philippe Blouin via Koha-devel <
> koha-devel@lists.koha-community.org> wrote:
>
>> Sunny day all!
>>
>> First time I have this problem: I'm trying to upgrade a 17.05 to 24.11,
>> but it keeps failing on constraints
>>
>> Upgrade to 17.12.00.015 done (Bug 20144 - Adapt DB structure to work with
>> new SQL modes)
>> {UNKNOWN}: DBI Exception: DBD::mysql::db do failed: Cannot change column
>> 'type': used in a foreign key constraint
>> 'koha_henryville/account_offsets_ibfk_t' of table
>> 'koha_henryville/account_offsets' at /usr/share/perl5/DBIx/Class/Schema.pm
>> line 1118.
>>
>> DBIx::Class::Schema::throw_exception(Koha::Schema=HASH(0x5e4975bc04e8), "DBI
>> Exception: DBD::mysql::db do failed: Cannot change column"...) called at
>> /usr/share/perl5/DBIx/Class/Storage.pm line 113
>>
>> DBIx::Class::Storage::throw_exception(DBIx::Class::Storage::DBI::mysql=HASH(0x5e497aefac50),
>> "DBI Exception: DBD::mysql::db do failed: Cannot change column"...)
>> called at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1623
>> DBIx::Class::Storage::DBI::__ANON__("DBD::mysql::db do failed:
>> Cannot change column 'type': used i"..., DBI::db=HASH(0x5e497b75b710),
>> undef) called at ./installer/data/mysql/updatedatabase.pl line 15545
>>
>> This was an install on Debian 12 / Mariadb 10.11 with the koha-common
>> packages, so I figured maybe some new constraints in Mariadb were in
>> cause. I moved the DB to a Ubuntu 22, Mariadb 10.6 with dev install but I
>> have the same problem.
>>
>> I've never encountered it in my upgrades up to 24.05, and there's no way
>> to skip it since it's a loop through ALL the tables to add the COLLATE,
>> etc...
>>
>> And the code starts with skipping the constraints, so I don't get it.
>>
>> Any suggestion?
>>
>>
>> Best regards
>> --
>> [image: Logo inLibro] <https://inLibro.com> Philippe Blouin
>> Directeur de la technologie
>>
>> T 833-INLIBRO (465-4276) <833-465-4276>, poste 230
>> C philippe.blo...@inlibro.com
>>
>> www.inLibro.com <https://inLibro.com>
>> _______________________________________________
>> Koha-devel mailing list
>> Koha-devel@lists.koha-community.org
>> https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
>> website : https://www.koha-community.org/
>> git : https://git.koha-community.org/
>> bugs : https://bugs.koha-community.org/
>>
>
>
> --
> Michael Hafen
> Washington County School District Technology Department
> Systems & Security Analyst
>
>
--
Michael Hafen
Washington County School District Technology Department
Systems & Security Analyst
_______________________________________________
Koha-devel mailing list
Koha-devel@lists.koha-community.org
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel
website : https://www.koha-community.org/
git : https://git.koha-community.org/
bugs : https://bugs.koha-community.org/
