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.
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.
(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.
Looping in PTFS-Europe folk as they would have an interest in this one I think
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.
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.
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.