Bug 39107 - kohastructure.sql doesn't load on new MySQL versions
Summary: kohastructure.sql doesn't load on new MySQL versions
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low major
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords: RM_priority
Depends on: 20581
Blocks:
  Show dependency treegraph
 
Reported: 2025-02-12 13:24 UTC by Tomás Cohen Arazi (tcohen)
Modified: 2025-02-14 16:28 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Tomás Cohen Arazi (tcohen) 2025-02-12 13:24:31 UTC
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.
Comment 1 David Cook 2025-02-12 23:53:09 UTC
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.
Comment 2 Tomás Cohen Arazi (tcohen) 2025-02-13 12:12:27 UTC
(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.
Comment 3 David Cook 2025-02-14 01:01:50 UTC
Looping in PTFS-Europe folk as they would have an interest in this one I think