Hi Philippe,
This sounds like the same error that is explained in the SQL13 coding guideline
-
https://wiki.koha-community.org/wiki/Coding_Guidelines#SQL13:_Modifying_columns_with_existing_foreign_key_constraints
This is the first I've heard of the error being seen on a MariaDB server rather
than a mySQL server, but it's similar enough that it might be a helpful lead.
In mySQL, the error only occurs if mySQL is running with strict_mode disabled,
so enabling strict_mode temporarily would allow it to complete. May be worth a
try?
Emily Lamancusa (she/her)
IT Specialist III
Montgomery County Public Libraries
(240) 777-0052
________________________________
From: Michael Hafen <michael.ha...@washk12.org>
Sent: Monday, May 12, 2025 2:06 PM
To: Philippe Blouin <philippe.blo...@inlibro.com>; koha-devel
<koha-devel@lists.koha-community.org>
Subject: Re: [Koha-devel] Upgrading 17.12.00.016 failed constraint
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<mailto: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.
[Logo inLibro] <https://inLibro.com> Philippe Blouin
Directeur de la technologie
T 833-INLIBRO (465-4276)<tel:833-465-4276>, poste 230
C philippe.blo...@inlibro.com<mailto: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<mailto: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<http://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
--
[Logo inLibro] <https://inLibro.com> Philippe Blouin
Directeur de la technologie
T 833-INLIBRO (465-4276)<tel:833-465-4276>, poste 230
C philippe.blo...@inlibro.com<mailto:philippe.blo...@inlibro.com>
www.inLibro.com<https://inLibro.com>
_______________________________________________
Koha-devel mailing list
Koha-devel@lists.koha-community.org<mailto: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
[https://www.montgomerycountymd.gov/mcg/Resources/Images/Cybersecurity-footer1.png]
For more helpful Cybersecurity Resources, visit:
https://www.montgomerycountymd.gov/cybersecurity
_______________________________________________
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/
