Bug 39107

Summary: kohastructure.sql doesn't load on new MySQL versions
Product: Koha Reporter: Tomás Cohen Arazi (tcohen) <tomascohen>
Component: DatabaseAssignee: Bugs List <koha-bugs>
Status: NEW --- QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: dcook, jonathan.druart, martin.renvoize, matt.blenkinsop, pedro.amorim, tomascohen
Version: MainKeywords: RM_priority
Hardware: All   
OS: All   
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
Circulation function:
Bug Depends on: 20581    
Bug Blocks:    

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