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.blouin@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.blouin@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.blouin@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
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_colu... 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.hafen@washk12.org> Sent: Monday, May 12, 2025 2:06 PM To: Philippe Blouin <philippe.blouin@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.blouin@inlibro.com<mailto:philippe.blouin@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.blouin@inLibro.com<mailto:philippe.blouin@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.blouin@inLibro.com<mailto:philippe.blouin@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-footer...] For more helpful Cybersecurity Resources, visit: https://www.montgomerycountymd.gov/cybersecurity
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.blouin@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_colu...
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.hafen@washk12.org> *Sent:* Monday, May 12, 2025 2:06 PM *To:* Philippe Blouin <philippe.blouin@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.blouin@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.blouin@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.blouin@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*
participants (3)
-
Lamancusa, Emily -
Michael Hafen -
Philippe Blouin