[Bug 39107] New: kohastructure.sql doesn't load on new MySQL versions
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Bug ID: 39107 Summary: kohastructure.sql doesn't load on new MySQL versions Change sponsored?: --- Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: major Priority: P5 - low Component: Database Assignee: koha-bugs@lists.koha-community.org Reporter: tomascohen@gmail.com QA Contact: testopia@bugs.koha-community.org I just read the MySQL 9.2 GA announcement so felt tempted to try it with `KTD`. It doesn't work, so I also tested other versions: # TL;DR * 8.0 -> OK * 8.4 (LTS) -> FAIL * 9.0 -> FAIL * 9.1 -> FAIL * 9.2 -> FAIL Based on the error, it should be solved easily by adding a UNIQUE constraint. To reproduce: 1. Run: $ DB_IMAGE=mysql:9.2 ktd up => FAIL: "DBD::mysql::st execute failed: Failed to add the foreign key constraint. Missing unique key for constraint 'illrequests_safk' in the referenced table 'authorised_values' at /usr/share/perl5/DBIx/RunSQL.pm line 290, <$args{...}> line 1. Something went wrong loading file /kohadevbox/koha/installer/data/mysql/kohastructure.sql ([SQL ERROR]: CREATE TABLE `" 2. Run: $ DB_IMAGE=mysql:9.1 ktd up => FAIL: "DBD::mysql::st execute failed: Failed to add the foreign key constraint. Missing unique key for constraint 'illrequests_safk' in the referenced table 'authorised_values' at /usr/share/perl5/DBIx/RunSQL.pm line 290, <$args{...}> line 1. Something went wrong loading file /kohadevbox/koha/installer/data/mysql/kohastructure.sql ([SQL ERROR]: CREATE TABLE `" 3. Run: $ DB_IMAGE=mysql:9.0 ktd up => FAIL: "DBD::mysql::st execute failed: Failed to add the foreign key constraint. Missing unique key for constraint 'illrequests_safk' in the referenced table 'authorised_values' at /usr/share/perl5/DBIx/RunSQL.pm line 290, <$args{...}> line 1. Something went wrong loading file /kohadevbox/koha/installer/data/mysql/kohastructure.sql ([SQL ERROR]: CREATE TABLE `" 4. Run: $ DB_IMAGE=mysql:8.4 ktd up => FAIL: "DBD::mysql::st execute failed: Failed to add the foreign key constraint. Missing unique key for constraint 'illrequests_safk' in the referenced table 'authorised_values' at /usr/share/perl5/DBIx/RunSQL.pm line 290, <$args{...}> line 1. Something went wrong loading file /kohadevbox/koha/installer/data/mysql/kohastructure.sql ([SQL ERROR]: CREATE TABLE `" 5. Run: $ DB_IMAGE=mysql:8.0 ktd up => SUCCESS: It starts correctly. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au, | |jonathan.druart@gmail.com, | |tomascohen@gmail.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |20581 --- Comment #1 from David Cook <dcook@prosentient.com.au> --- Interesting discussion on https://bugs.mysql.com/bug.php?id=114838 (In reply to Tomás Cohen Arazi (tcohen) from comment #0)
Based on the error, it should be solved easily by adding a UNIQUE constraint.
Unfortunately, we can't do that in this case. illrequests_safk is a foreign key to authorised_values.authorised_value which cannot be unique. There's lots of values in that column like 0 or 1 which apply to multiple different authorised value categories. But... then we have to think how can illrequests.status_alias possibly have a foreign key on authorised_values.authorised_value? It looks like status_alias was added by bug 20581 and originally status_alias referenced authorised_values.id but on that same bug there was a patch that changed it to authorised_values.authorised_value It looks like there was some talk about using composite keys, but DBIx::Class might not have been able to handle that? One of the downsides of the authorised_values table... -- I think the fix is to remove the illrequests_safk foreign key and fix any resulting breakages. Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=20581 [Bug 20581] Allow manual selection of custom ILL request statuses -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #2 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- (In reply to David Cook from comment #1)
I think the fix is to remove the illrequests_safk foreign key and fix any resulting breakages.
Agreed, and it seems to be the only occurrence. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |martin.renvoize@ptfs-europe | |.com, | |matt.blenkinsop@ptfs-europe | |.com, | |pedro.amorim@ptfs-europe.co | |m --- Comment #3 from David Cook <dcook@prosentient.com.au> --- Looping in PTFS-Europe folk as they would have an interest in this one I think -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |RM_priority -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |saiful@semanticconsulting.c | |om --- Comment #4 from Saiful Amin <saiful@semanticconsulting.com> --- Just adding more context on why this issue is becoming urgent. 1. MySQL 8.4 is not a fringe version, it is the new LTS Oracle has moved MySQL to an "Innovation vs. LTS" model. - MySQL 8.4 LTS (released April 2024) is supported until ~April 2032. - MySQL 8.0 LTS support ends April 2026. 2. Ubuntu 25.10 already ships MySQL 8.4.7 Given Ubuntu's release cycle, the next LTS (Ubuntu 26.04) will almost certainly ship MySQL 8.4 LTS by default. This means that fresh Koha installs and upgrades on the next LTS will fail out-of-the-box for anyone using the distro-provided MySQL packages. Koha depending on MySQL 8.0 behavior is going to become a blocker very soon, because distro vendors, cloud providers, and MySQL itself are pushing everyone toward the new LTS line. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Priority|P5 - low |P1 - high -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #5 from Saiful Amin <saiful@semanticconsulting.com> --- Using `DB_IMAGE=mysql:8.4 ktd up`, the Koha installation only proceeds after enforcing uniqueness on `authorised_values.authorised_value` in `kohastructure.sql`: - KEY `auth_value_idx` (`authorised_value`), + UNIQUE KEY `auth_value_idx` (`authorised_value`), Once the unique index is added, the installer stops because the mandatory seed data contains duplicates. I counted 27 duplicate authorised_value entries, mostly coming from ERM, in: installer/data/mysql/en/mandatory/auth_values.yml Those ERM authorised values need to be reviewed and deduplicated. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #6 from Saiful Amin <saiful@semanticconsulting.com> --- I experimented with an alternative approach that avoids enforcing uniqueness on authorised_value. Instead of making the column unique, I temporarily removed the problematic foreign key: +++ b/installer/data/mysql/kohastructure.sql @@ -3814,8 +3814,8 @@ CREATE TABLE `illrequests` ( ... - CONSTRAINT `illrequests_ibfk` FOREIGN KEY (`batch_id`) REFERENCES `illbatches` (`ill_batch_id`) ON DELETE SET NULL ON UPDATE CASCADE, - CONSTRAINT `illrequests_safk` FOREIGN KEY (`status_alias`) REFERENCES `authorised_values` (`authorised_value`) ON DELETE SET NULL ON UPDATE CASCADE + CONSTRAINT `illrequests_ibfk` FOREIGN KEY (`batch_id`) REFERENCES `illbatches` (`ill_batch_id`) ON DELETE SET NULL ON UPDATE CASCADE + -- CONSTRAINT `illrequests_safk` FOREIGN KEY (`status_alias`) REFERENCES `authorised_values` (`authorised_value`) ON DELETE SET NULL ON UPDATE CASCADE ... With this constraint disabled, Koha installs successfully on MySQL 8.4. This isn't a proposed solution, just a data point: removing the FK does allow the installer to complete, which confirms this is the only direct blocker for MySQL 8.4 compatibility. Obviously, the implications need to be discussed: - status_alias becomes unenforced metadata; inconsistent values could creep in. - ERM currently introduces many duplicate authorised_value entries, so even if we switched the FK to use av_uniq (category,authorised_value), it still wouldn't guarantee correctness. - If the FK is dropped permanently, any code relying on referential integrity for ILL status values would lose database-level protection. Posting this only to help frame the decisions: - Do we enforce uniqueness and require ERM values to be deduplicated? - Do we change the FK to point at a different key? - Or do we drop the FK entirely and enforce integrity in application logic? At least this confirms the MySQL 8.4 blocker is isolated to this single FK constraint. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |andrew@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #7 from Saiful Amin <saiful@semanticconsulting.com> --- Created attachment 197701 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=197701&action=edit Bug 39107: Remove illrequests_safk FK constraint on illrequests.status_alias as required by MySQL 8.4+ MySQL 8.4 requires foreign keys to reference PRIMARY KEY or UNIQUE keys. Since authorised_values.authorised_value is only unique in combination with category (composite UNIQUE key av_uniq), the FK constraint is not compatible with MySQL 8.4+. Data integrity for illrequests.status_alias is maintained at the application layer: - Koha::ILL::Request->status_alias() validates values against authorised_values (category='ILL_STATUS_ALIAS') before storing - All ILL request operations in Koha use this object layer - CASCADE behaviors (ON DELETE SET NULL, ON UPDATE CASCADE) are already handled by the application layer when modifying/deleting authorised values. Test Plan, using KTD: 1. Prepare KTD for MySQL 8.4 - In koha-testing-docker, edit 'compose/db-mysql.yml' - Comment out the line 5: `# command: --default-authentication-plugin=mysql_native_password` Note: This is required because MySQL 8.4 doesn't support the native password plugin. 2. Start a new instance with MySQL 8.4 - `DB_IMAGE=mysql:8.4 ktd up` - Observe that it fails to start with the errors such as 'DBD::mysql::st execute failed' 3. Apply the patch - `git bz apply 39107` 4. Start the instance again - `DB_IMAGE=mysql:8.4 ktd up` - Observe that it starts successfully 5. Update the database (Optional, only for existing instances) - `perl installer/data/mysql/updatedatabase.pl` 6. Access Koha Staff and OPAC interfaces and verify that everything works as expected 7. Sign off -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Saiful Amin <saiful@semanticconsulting.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #197701|0 |1 is obsolete| | -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #8 from David Nind <david@davidnind.com> --- Created attachment 197730 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=197730&action=edit Bug 39107: Remove illrequests_safk FK constraint on illrequests.status_alias as required by MySQL 8.4+ MySQL 8.4 requires foreign keys to reference PRIMARY KEY or UNIQUE keys. Since authorised_values.authorised_value is only unique in combination with category (composite UNIQUE key av_uniq), the FK constraint is not compatible with MySQL 8.4+. Data integrity for illrequests.status_alias is maintained at the application layer: - Koha::ILL::Request->status_alias() validates values against authorised_values (category='ILL_STATUS_ALIAS') before storing - All ILL request operations in Koha use this object layer - CASCADE behaviors (ON DELETE SET NULL, ON UPDATE CASCADE) are already handled by the application layer when modifying/deleting authorised values. Test Plan, using KTD: 1. Prepare KTD for MySQL 8.4 - In koha-testing-docker, edit 'compose/db-mysql.yml' - Comment out the line 5: `# command: --default-authentication-plugin=mysql_native_password` Note: This is required because MySQL 8.4 doesn't support the native password plugin. 2. Start a new instance with MySQL 8.4 - `DB_IMAGE=mysql:8.4 ktd up` - Observe that it fails to start with the errors such as 'DBD::mysql::st execute failed' 3. Apply the patch - `git bz apply 39107` 4. Start the instance again - `DB_IMAGE=mysql:8.4 ktd up` - Observe that it starts successfully 5. Update the database (Optional, only for existing instances) - `perl installer/data/mysql/updatedatabase.pl` 6. Access Koha Staff and OPAC interfaces and verify that everything works as expected 7. Sign off Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |release-notes-needed Assignee|koha-bugs@lists.koha-commun |saiful@semanticconsulting.c |ity.org |om CC| |david@davidnind.com --- Comment #9 from David Nind <david@davidnind.com> --- Added assignee. Testing notes (using KTD): 1. Step 3: Need to start KTD up "normally", apply the patch, run the database update (?), and then shutdown KTD (can't apply the patch using git bz unless you're in a KTD shell with KTD running). 2. Then Step 1 and Step 2 (no failures in starting up KTD after the patch). 3. Can log into the staff interface and use OPAC. 4. Have not "verified that everything works as expected". Only some basic things, like search, place a hold, edit a patron record. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #10 from Saiful Amin <saiful@semanticconsulting.com> --- (In reply to David Nind from comment #9)
Testing notes (using KTD): 1. Step 3: Need to start KTD up "normally", apply the patch, run the database update (?), and then shutdown KTD (can't apply the patch using git bz unless you're in a KTD shell with KTD running). 2. Then Step 1 and Step 2 (no failures in starting up KTD after the patch). 3. Can log into the staff interface and use OPAC.
Thanks David for the Signoff. I should have mentioned setting up Perl Git-bz globally, so that patches like these can be applied outside KTD shell as well. Perl Git-bz has a nice documentation to set it up: https://gitlab.com/koha-community/perl-git-bz
4. Have not "verified that everything works as expected". Only some basic things, like search, place a hold, edit a patron record.
I remember bib record deletion was a problem without this patch, as mentioned here: https://chat.koha-community.org/koha-community/pl/59s6ooc4jb8wxgbfkiasgt55re Now everything I tried (including record delete) worked fine. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |emily.lamancusa@montgomeryc |y.org |ountymd.gov CC| |emily.lamancusa@montgomeryc | |ountymd.gov -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #197730|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #11 from Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> --- Created attachment 198155 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198155&action=edit Bug 39107: Remove illrequests_safk FK constraint on illrequests.status_alias as required by MySQL 8.4+ MySQL 8.4 requires foreign keys to reference PRIMARY KEY or UNIQUE keys. Since authorised_values.authorised_value is only unique in combination with category (composite UNIQUE key av_uniq), the FK constraint is not compatible with MySQL 8.4+. Data integrity for illrequests.status_alias is maintained at the application layer: - Koha::ILL::Request->status_alias() validates values against authorised_values (category='ILL_STATUS_ALIAS') before storing - All ILL request operations in Koha use this object layer - CASCADE behaviors (ON DELETE SET NULL, ON UPDATE CASCADE) are already handled by the application layer when modifying/deleting authorised values. Test Plan, using KTD: 1. Prepare KTD for MySQL 8.4 - In koha-testing-docker, edit 'compose/db-mysql.yml' - Comment out the line 5: `# command: --default-authentication-plugin=mysql_native_password` Note: This is required because MySQL 8.4 doesn't support the native password plugin. 2. Start a new instance with MySQL 8.4 - `DB_IMAGE=mysql:8.4 ktd up` - Observe that it fails to start with the errors such as 'DBD::mysql::st execute failed' 3. Apply the patch - `git bz apply 39107` 4. Start the instance again - `DB_IMAGE=mysql:8.4 ktd up` - Observe that it starts successfully 5. Update the database (Optional, only for existing instances) - `perl installer/data/mysql/updatedatabase.pl` 6. Access Koha Staff and OPAC interfaces and verify that everything works as expected 7. Sign off Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #12 from Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> --- Created attachment 198156 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198156&action=edit Bug 39107: (QA follow-up) Use existing foreign_key_exists function Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA --- Comment #13 from Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> --- Thanks, Saiful! We should really refactor the way we handle authorized values to allow for foreign key constraints... But that's far out of scope for this bug. In the meantime, this fixes the major issue, and the behavior of the ILL status alias is in line with the behavior of other authorized values. Thanks all! Passing QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the| |This fixes a database error release notes| |that occurred when starting | |up a new Koha instance with | |MySQL version 8.4 or | |higher. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com Status|Passed QA |Failed QA --- Comment #14 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- What about schema changes? When in run DBIC changes are introduced but not mentioned here. Please advise. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|release-notes-needed | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #15 from Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> --- Created attachment 198549 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198549&action=edit Bug 39107: [DO NOT PUSH] DBIC update DBIC update -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #198155|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #198156|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #198549|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #16 from Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> --- Created attachment 198550 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198550&action=edit Bug 39107: Remove illrequests_safk FK constraint on illrequests.status_alias as required by MySQL 8.4+ MySQL 8.4 requires foreign keys to reference PRIMARY KEY or UNIQUE keys. Since authorised_values.authorised_value is only unique in combination with category (composite UNIQUE key av_uniq), the FK constraint is not compatible with MySQL 8.4+. Data integrity for illrequests.status_alias is maintained at the application layer: - Koha::ILL::Request->status_alias() validates values against authorised_values (category='ILL_STATUS_ALIAS') before storing - All ILL request operations in Koha use this object layer - CASCADE behaviors (ON DELETE SET NULL, ON UPDATE CASCADE) are already handled by the application layer when modifying/deleting authorised values. Test Plan, using KTD: 1. Prepare KTD for MySQL 8.4 - In koha-testing-docker, edit 'compose/db-mysql.yml' - Comment out the line 5: `# command: --default-authentication-plugin=mysql_native_password` Note: This is required because MySQL 8.4 doesn't support the native password plugin. 2. Start a new instance with MySQL 8.4 - `DB_IMAGE=mysql:8.4 ktd up` - Observe that it fails to start with the errors such as 'DBD::mysql::st execute failed' 3. Apply the patch - `git bz apply 39107` 4. Start the instance again - `DB_IMAGE=mysql:8.4 ktd up` - Observe that it starts successfully 5. Update the database (Optional, only for existing instances) - `perl installer/data/mysql/updatedatabase.pl` 6. Access Koha Staff and OPAC interfaces and verify that everything works as expected 7. Sign off Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #17 from Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> --- Created attachment 198551 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198551&action=edit Bug 39107: (QA follow-up) Use existing foreign_key_exists function Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #18 from Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> --- Created attachment 198552 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=198552&action=edit Bug 39107: [DO NOT PUSH] DBIC update DBIC update -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Emily Lamancusa (emlam) <emily.lamancusa@montgomerycountymd.gov> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |26.05.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #19 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to main for 26.05 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Jacob O'Mara <jacob.omara@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|26.05.00 |26.05.00,25.11.05 released in| | Status|Pushed to main |Pushed to stable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 --- Comment #20 from Jacob O'Mara <jacob.omara@openfifth.co.uk> --- Thanks all, pushed to 25.11.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39107 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |wainuiwitikapark@catalyst.n | |et.nz --- Comment #21 from Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> --- Hmm I don't seem to be able to reproduce the error. When I do: 2. Start a new instance with MySQL 8.4 - `DB_IMAGE=mysql:8.4 ktd up` It starts successfully. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org