Hello Koha,
Thanks for all the help. I'm just writing in to explain how I did
manage to fix it:
1. Opened the SQL dump, overwrote all the COLLATE and CHARACTER SET
manually, as well as changing 255 to 191.
2. Modified updatedatabase to skip or alter all the steps between 17.05
and 18... that created tables or altered foreign keys
3. Load and fix on the fly.
4. After 18.xxx it just flew through
Basically, the ugly way. Foreign keys can't be done on columns with
different COLLATE, and dropping the constraints doesn't work for that check.
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
www.inLibro.com <https://inLibro.com>
On 2025-05-12 15:16, Lamancusa, Emily wrote:
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> 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
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
<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
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
*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/
