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
_______________________________________________
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/
