Bug 37476 - RANK is a reserved word in MySQL 8.0.2+
Summary: RANK is a reserved word in MySQL 8.0.2+
Status: Needs documenting
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low normal
Assignee: Tomás Cohen Arazi
QA Contact: Martin Renvoize
URL:
Keywords:
Depends on: 26567
Blocks: 37480
  Show dependency treegraph
 
Reported: 2024-07-25 14:21 UTC by Tomás Cohen Arazi
Modified: 2024-09-16 13:59 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
This fixes adding a patron to a routing list after receiving a serial - the patron was not being added to the routing list. This issue was only happing where MySQL 8.0.2 or later was used as the database for Koha. This was because the SQL syntax in the SQL used RANK, which become a reserved word in MySQL 8.0.2.
Version(s) released in:
24.11.00,24.05.04
Circulation function:


Attachments
Bug 37476: Unit tests (3.41 KB, patch)
2024-07-25 17:40 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8 (1.67 KB, patch)
2024-07-25 17:41 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 37476: Unit tests (3.48 KB, patch)
2024-07-26 13:19 UTC, Emily Lamancusa
Details | Diff | Splinter Review
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8 (1.74 KB, patch)
2024-07-26 13:19 UTC, Emily Lamancusa
Details | Diff | Splinter Review
Bug 37476: Unit tests (3.55 KB, patch)
2024-07-26 13:47 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8 (1.81 KB, patch)
2024-07-26 13:47 UTC, Martin Renvoize
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Tomás Cohen Arazi 2024-07-25 14:21:00 UTC
As of MySQL 8.0.2 [1], `rank` is a reserved word and is breaking some code [2]:

```
C4::Serials::addroutingmember(): DBI Exception: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank FROM subscriptionroutinglist WHERE subscriptionid = '59'' at line 1 at t/db_dependent/Serials.t line 227
```

[1] https://dev.mysql.com/doc/refman/8.0/en/keywords.html
[2] https://jenkins.koha-community.org/job/Koha_Main_My8/1322/consoleFull
Comment 1 Tomás Cohen Arazi 2024-07-25 17:40:49 UTC
Created attachment 169609 [details] [review]
Bug 37476: Unit tests
Comment 2 Tomás Cohen Arazi 2024-07-25 17:41:17 UTC
Created attachment 169610 [details] [review]
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8

This patch fixes the fact `RANK` become a reserved word in MySQL 8.0.2
[1]

To test:
1. Launch KTD with MySQL 8:
   $ ktd down
   $ DB_IMAGE=mysql:8 ktd up -d
2. Open the logs
   $ ktd --shell
  k$ tail -f /var/log/koha/kohadev/*.log
3. Create a serial, receive an issue and try to create a routing list
4. Click on `+ Add recipients` and look for Henry
5. Click `Add` and then `Close`
=> FAIL: Henry not added
=> FAIL: The logs show an error about wrong SQL syntax
6. Run:
  k$ prove t/db_dependent/Serials.t
=> FAIL: Tests explode with the same kind of error!
6. Apply this patch
7. Restart plack
8. Repeat 3 through 6
=> SUCCESS: Henry added!
=> SUCCESS: No explosion about the SQL syntax in the logs
=> SUCCESS: Tests pass!
9. Sign off :-D

[1] https://dev.mysql.com/doc/refman/8.0/en/keywords.html
Comment 3 Emily Lamancusa 2024-07-26 13:19:40 UTC
Created attachment 169702 [details] [review]
Bug 37476: Unit tests

Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Comment 4 Emily Lamancusa 2024-07-26 13:19:42 UTC
Created attachment 169703 [details] [review]
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8

This patch fixes the fact `RANK` become a reserved word in MySQL 8.0.2
[1]

To test:
1. Launch KTD with MySQL 8:
   $ ktd down
   $ DB_IMAGE=mysql:8 ktd up -d
2. Open the logs
   $ ktd --shell
  k$ tail -f /var/log/koha/kohadev/*.log
3. Create a serial, receive an issue and try to create a routing list
4. Click on `+ Add recipients` and look for Henry
5. Click `Add` and then `Close`
=> FAIL: Henry not added
=> FAIL: The logs show an error about wrong SQL syntax
6. Run:
  k$ prove t/db_dependent/Serials.t
=> FAIL: Tests explode with the same kind of error!
6. Apply this patch
7. Restart plack
8. Repeat 3 through 6
=> SUCCESS: Henry added!
=> SUCCESS: No explosion about the SQL syntax in the logs
=> SUCCESS: Tests pass!
9. Sign off :-D

[1] https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Comment 5 Emily Lamancusa 2024-07-26 13:21:35 UTC
Testing notes: DB_IMAGE=mysql:8 didn't work for me, but DB_IMAGE=mysql:8.0 did. Otherwise all works as it should!
Comment 6 Tomás Cohen Arazi 2024-07-26 13:23:00 UTC
(In reply to Emily Lamancusa from comment #5)
> Testing notes: DB_IMAGE=mysql:8 didn't work for me, but DB_IMAGE=mysql:8.0
> did. Otherwise all works as it should!

Thanks! I should've copied and pasted my command instead of typing it :-D
Comment 7 Martin Renvoize 2024-07-26 13:47:55 UTC
Created attachment 169722 [details] [review]
Bug 37476: Unit tests

Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 8 Martin Renvoize 2024-07-26 13:47:58 UTC
Created attachment 169723 [details] [review]
Bug 37476: Fix reserved word error on Serials.pm on MySQL 8

This patch fixes the fact `RANK` become a reserved word in MySQL 8.0.2
[1]

To test:
1. Launch KTD with MySQL 8:
   $ ktd down
   $ DB_IMAGE=mysql:8 ktd up -d
2. Open the logs
   $ ktd --shell
  k$ tail -f /var/log/koha/kohadev/*.log
3. Create a serial, receive an issue and try to create a routing list
4. Click on `+ Add recipients` and look for Henry
5. Click `Add` and then `Close`
=> FAIL: Henry not added
=> FAIL: The logs show an error about wrong SQL syntax
6. Run:
  k$ prove t/db_dependent/Serials.t
=> FAIL: Tests explode with the same kind of error!
6. Apply this patch
7. Restart plack
8. Repeat 3 through 6
=> SUCCESS: Henry added!
=> SUCCESS: No explosion about the SQL syntax in the logs
=> SUCCESS: Tests pass!
9. Sign off :-D

[1] https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Signed-off-by: Emily Lamancusa <emily.lamancusa@montgomerycountymd.gov>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 9 Martin Renvoize 2024-07-26 13:48:29 UTC
Thanks for the fix here Tomas.. Passing QA
Comment 10 Martin Renvoize 2024-07-26 13:51:16 UTC
Thanks for all the hard work!

Pushed to main for the next 24.11.00 release as RM Assistant
Comment 11 Lucas Gass 2024-08-29 17:00:46 UTC
Backported to 24.05.x for upcoming 24.05.04
Comment 12 Fridolin Somers 2024-09-16 13:59:11 UTC
Depends on Bug 26567 not in 23.11.x